Please help ... comparing text

[Sally M.]

I have an Excel spreadsheet that has 2 columns so far ...

Col A = suburb name in full
Col B = an abbreviated version of the suburb (this appears on the same row
as the full name that appears in Col A)

I now need to enter hundreds of abbreviated suburb names - multiple and in
no particular order (which will be entered into a new column - Col C)

Question: is there a way I can "ask" ... if Col C matches any in Col B (not
case-sensitive preferably), then insert Col A (full suburb) into a 4th (new)
column (Col D)?

I apologise, I know this is a little confusing, but I would appreciate any
advice/help - I have searched everywhere and can't seem to find an answer.

 

[Max]

One way ..

Assuming data starts in row2 down

Put in D2:
=IF(ISNA(MATCH(C2,B:B,0)),"",INDEX(A:A,MATCH(C2,B:B,0)))
Copy down as far as required. If there's no match found for the lookup
values in col C, blanks: "" will be returned. MATCH is not case sensitive, as
desired.

 

[Don Guillett]

try this
Sub findmatch1()
Columns(4).ClearContents
For Each i In Range("c1:c" & Cells(Rows.Count, "c").End(xlUp).Row)
With Range("b1:b" & Cells(Rows.Count, "b").End(xlUp).Row)
Set c = .Find(i, Lookat:=xlWhole, MatchCase:=False)
If Not c Is Nothing Then
firstAddress = c.Address
Do
i.Offset(, 1) = c
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With
Next
End Sub