Formula/Macro to Find Specific Text and Return a Corresponding Value

[kangelosanto]

So here's a quick overview of what I need:

On one sheet (let's call this "SHEET A") I have a list of every airport in the world and the following data:

IATA	ICAO	Airport Name	Location Serverd	Time	DST	Country Code
AAA	NTQA	Anaa Airport	Anaa, Tuamotus, French Polynesia	UTC
AAB	YARY	Arrabury	Arrabury, Queensland, Australia	UTC

<tbody>
</tbody>

I have another table on another sheet (let's call this "SHEET B") with every country in the world, and then in the cell next to it a unique number (i.e. Afghanistan | 1, Albania | 2, etc.)

I figured in theory I could run some sort of variation of an INDEX(MATCH that would search using the country on Sheet B (such as *Australia* or *France*), find it in column 4 of Sheet A, and then fill in the missing country code field from Sheet B in Column 7 of Sheet A.

Any help is appreciated!

 

[lrobbo314]

Something like this.

=VLOOKUP(TRIM(RIGHT(D3,LEN(D3)-FIND(",",D3,FIND(",",D3)+1))),SheetB!$A$1:$B$2,2,0)

Change your ranges to match your data.

 

[kangelosanto]

This works for some, but there's also a few that only have one comma in the cell, such as "Mzuzu, Malawi"

Is there a formula that would satisfy both conditions?

 

[lrobbo314]

=IF(SUMPRODUCT(--(MID(D3,ROW(INDIRECT("1:"&LEN(D3))),1)=","))=1,VLOOKUP(TRIM(RIGHT(D3,LEN(D3)-FIND(", ",D3))),SheetB!$A$1:$B$3,2,0),VLOOKUP(TRIM(RIGHT(D3,LEN(D3)-FIND(",",D3,FIND(",",D3)+1))),SheetB!$A$1:$B$2,2,0))

 

[lrobbo314]

And here is a VBA mix solution that looks a lot cleaner.

VBA function to get everything after the last comma

Function trimR(r As Range, s As String)
Dim i As Integer
i = Len(r)

For i = i To 1 Step -1
    If Mid(r, i, 1) = s Then
        trimR = Trim(Right(r, Len(r) - i - 1))
        Exit For
    End If
Next i

End Function

Formula

=VLOOKUP(trimR(D3,","),SheetB!$A$1:$B$3,2,0)

 

[kangelosanto]

=IF(SUMPRODUCT(--(MID(D3,ROW(INDIRECT("1:"&LEN(D3))),1)=","))=1,VLOOKUP(TRIM(RIGHT(D3,LEN(D3)-FIND(", ",D3))),SheetB!$A$1:$B$3,2,0),VLOOKUP(TRIM(RIGHT(D3,LEN(D3)-FIND(",",D3,FIND(",",D3)+1))),SheetB!$A$1:$B$2,2,0))

Perfect. Thanks a bunch.