Get Cell Address From Lookup (Alternative to Lookup)

[ryguy7272]

This is a follow up to a post that I submitted here a few days ago. T. Valko
gave me a great function and tried, repeatedly, to help me (thanks for the
assistance). No matter what I did, I just couldn’t get the function working
the way I needed it to work. Thus, I am back with a similar request. I am
hoping someone can give me a function to return the cell address for all
items in a list that match a certain criteria. I am thinking of something
such as this:
=ADDRESS(MATCH(A2,'Import Sheet'!A2:A65000,0),1)

This works, but the value in A2 must be an exact match with the value in the
list (which is 'Import Sheet'!A2:A65000). I know this comes from the
‘match_type’ in MATCH(lookup_value,lookup_array,match_type)

The problem is that the value in A2 will be a name, but the values in the
list in 'Import Sheet'!A2:A65000, are names, combined with spaces before and
after the names, commas before and after the names, hyphens before and after
the names, etc. Since the data will never be an EXACT MATCH, I can’t do the
match. I’m thinking the SEARCH function will be the best function to use
because it does not rely on case sensitive data. A function or a macro would
be great!!

Does anyone have any insight about this?

Cordially,
Ryan--

 

[Max]

=ADDRESS(MATCH(A2,'Import Sheet'!A2:A65000,0),1)

For the fuzzy match, try this option, array-entered with CTRL+SHIFT+ENTER:
=ADDRESS(MATCH(TRUE,ISNUMBER(SEARCH(A2,'Import Sheet'!A2:A65000)),0),1)

Replace SEARCH with FIND if you want the expression's search to be case
sensitive. FIND is case sensitive.

 

[T. Valko]

Sent you an email.

 

[Max]

Guess I'm not really sure whether that fuzzy match worked for you <g>?

 

[T. Valko]

It's much more complicated than that!

 

[RyGuy]

Sorry for the delay Max. The fuzzy logic turned out to be too fuzzy;
sometimes XL returned the correct results and sometimes it didn't.

I'm still working on a solution; I will post back as soon as one presents
itself.

Ryan--

 

[RyGuy]

A few days ago Biff gave me a function and it seemed to work great at first,
but then it didn’t seem to really work. I’ve pretty much narrowed it down to
this. If the value in column B is different (from the value below), then I
have to tell the below function to change the new reference to the cell
directly to the left (which is Column B), but if the value in Column B is the
same (as value above), then I have to tell the function to absolute reference
the cell above and continue down to the next cell (this seems to be the
genesis of the problem):

=IF(B2=B3,IF(ROWS(B$2:B2)<=B$1,"A"&SMALL(IF(ISNUMBER(SEARCH(A3,'Import
Sheet'!A$1:A$65000)),ROW('Import
Sheet'!A$1:A$65000)),ROWS(OFFSET(B$2:B2,0,-1))),""),IF(ROWS(OFFSET(B2,0,-1))<=B$1,"A"&SMALL(IF(ISNUMBER(SEARCH(A3,'Import
Sheet'!A$1:A$65000)),ROW('Import Sheet'!A$1:A$65000)),ROWS(OFFSET(B2,0,-1)))))
Note: this is a CSE function

For instance, the values in B2:B9 are all the same, so B$2:B2 works fine,
down to B9. The value in B9 is different from the value in B10, so I have to
get the function to absolute reference B10, and move down until the value in
Column B changes again, which happens at B11. Then B11:B14 are the same and
then it changes once again at B15:16 (these two are the same). This has me
baffled. I hate to be annoying and keep posting this same, although slightly
modified, question, but I’m totally out of ideas. Any thoughts? Anyone?

Thanks so much!
Ryan--

 

[Peo Sjoblom]

Just a bit of caution, you realize that your solution is very complicated.
Maybe you should rethink your layout. What happens if someone else inherits
this spreadsheet or if MS decides to close down these newsgroups. There is
nobody working at MS support that can audit that formula.


--


Regards,


Peo Sjoblom

 

[Max]

Whether or not the suggestion worked, one should always feedback promptly as
a closure to the responder.

 

[Max]

Is that reason to take it offline?

What is the point of posting, if not to discuss online,
and " .. to keep all discussions within the newsgroup
for the benefit of all"?

 

[T. Valko]

Is that reason to take it offline?

Can't solve a problem if one doesn't understand the problem. Sometimes
written explanations just don't convey what's actually happening. This is
one of those times.

--
Biff
Microsoft Excel MVP

Is that reason to take it offline?

What is the point of posting, if not to discuss online,
and " .. to keep all discussions within the newsgroup
for the benefit of all"?

 

[RyGuy]

Peo, you are right; very complex. I'm trying to simplify things a little. I
think I'm close to a solution. I will post back as soon as I can come up
with something workable.

Thanks to all who tried to help!!

Ryan--

 

[RyGuy]

Well, thanks to a huge push from Tom Ogilvy, I finally got this darned thing
resolved.
For the benefit of others who encounter a similar challenge follow these
(simple) instructions:

Data (in my case names) is listed in Column A. Column B contains the
following function:
=COUNT(SEARCH($A2,'Import Sheet'!$A$1:$A$65000))
C+S+E


Column C contains the following function (or versions of the function, based
on the macro below):
=IF(ROWS(C$2:C2)<=B$1,"A"&SMALL(IF(ISNUMBER(SEARCH(A2,'Import
Sheet'!A$1:A$65000)),ROW('Import Sheet'!A$1:A$65000)),ROWS(C$2:C2)),"")
C+S+E


Finally, Column D contains this function:
=INDIRECT("'Import Sheet'!"&C2)
Not C+S+E


Cell B1 contains this function:
=COUNT(B2:B65536)


Cell T1 contains this function:
=LOOKUP(10^10,B2:B500)-1



The entire macro looks like this:
Sub InsertVarRows()
Dim myRow As Long
Dim rng As Range

Dim sngStart As Double
sngStart = Now


Range("B2").Select
Selection.FormulaArray = _
"=IF(RC[-1]<>"""",COUNT(SEARCH(RC1,'Import Sheet'!R1C1:R65000C1)),"""")"
Dim LastRow As Long 'Fills down, based on data in column to left
With ActiveSheet
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
.Range("B2").AutoFill Destination:=.Range("B2:B" & LastRow)
End With

Range("B2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Range("B2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Range("A1").Select
lastcell = Cells(Rows.Count, "A").End(xlUp).Row
myRow = 2
Do Until myRow = lastcell
For i = 2 To Cells(myRow, 2) 'start counter at 2
If Cells(myRow, 1) <> "" Then
Cells(myRow + 1, 1).Select
'Selection.Insert shift:=xlDown
Selection.EntireRow.Insert shift:=xlDown
End If
Next
lastcell = Cells(Rows.Count, "A").End(xlUp).Row
myRow = myRow + 1
Loop



Range("B2").Select
Selection.FormulaArray = _
"=IF(RC[-1]<>"""",COUNT(SEARCH(RC1,'Import Sheet'!R1C1:R65000C1)),"""")"
With ActiveSheet
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
.Range("B2").AutoFill Destination:=.Range("B2:B" & LastRow)
End With

Dim LastRow1 As Long
LastRow1 = Range("T1")
Range("B500").Select
Selection.End(xlUp).Select
ActiveCell.Offset(1, 0).Select
Range(ActiveCell, ActiveCell.Offset(LastRow1, 0)).Select
Selection.FormulaR1C1 = "=R[-1]C"


Last = Cells(Rows.Count, ActiveCell.Column).End(xlUp).Row
Range("A2:A" & Last).SpecialCells(xlCellTypeBlanks).FormulaR1C1 =
"=R[-1]C"


Dim iStart As Long
Dim sFormula As String
Dim iLastRow As Long
Dim j As Long

With ActiveSheet

iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
sFormula = "=IF(ROWS(R<row>C:RC)<=R1C[-1],""A""&" & _
"SMALL(IF(ISNUMBER(SEARCH(RC[-2]," & _
"'Import Sheet'!R1C[-2]:R65000C[-2]))," & _
"ROW('Import Sheet'!R1C[-2]:R65000C[-2]))," & _
"ROWS(R<row>C:RC)),"""")"

iStart = 2
For j = 2 To iLastRow
If Cells(j, 2) = Cells(j - 1, 2) Then
.Cells(j, "C").FormulaArray = Replace(sFormula, "<row>", j - 1)
.Cells(j, "C").FillDown
Else
.Cells(j, "C").FormulaArray = Replace(sFormula, "<row>", j)
End If
Next
End With
‘The part that I had trouble with is:
‘From: Dim iStart As Long
‘To: End With
‘This thing was a pain-in-the-butt!!!


Range("D2").Select
ActiveCell.FormulaR1C1 = "=INDIRECT(""'Import Sheet'!""&RC[-1])"
Dim LastRow3 As Long 'Fills down, based on data in column to left
With ActiveSheet
LastRow3 = .Cells(.Rows.Count, "C").End(xlUp).Row
.Range("D2").AutoFill Destination:=.Range("D2" & LastRow3)
End With


MsgBox "Process Complete!! " & Counter & _
" File Updated!" & vbNewLine & _
" took " & Format(Now - sngStart, "hh:mm:ss")

End Sub


Thanks to all who helped out!!!
Thanks 1,000,000*!
Ryan--