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

kangelosanto

New Member
Joined
Aug 12, 2014
Messages
14
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:

IATAICAOAirport NameLocation ServerdTime
DSTCountry Code
AAANTQAAnaa AirportAnaa, Tuamotus, French PolynesiaUTC
AABYARYArraburyArrabury, Queensland, AustraliaUTC

<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!
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Something like this.

Code:
=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.
 
Upvote 0
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?
 
Upvote 0
Code:
=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))
 
Upvote 0
And here is a VBA mix solution that looks a lot cleaner.

VBA function to get everything after the last comma
Code:
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
Code:
=VLOOKUP(trimR(D3,","),SheetB!$A$1:$B$3,2,0)
 
Upvote 0
Code:
=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.
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,186
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top