Index Help looking up and matching

Marc Enzi

Board Regular
Joined
Dec 13, 2004
Messages
92
I am trying to use Index and Match to lookup a 3 letter code in a list I am compiling and have it match a corresponding 3 letter code and a corresponding 4 letter code in a master list.

I am looking up airport codes, and the lookup doesn't seem to do the job exactly matching the codes...

I am having no luck with this formula

=INDEX(paid50!A:A,MATCH(B3,paid50!B:B,0))

I have a sheet named mergeIATAdb with the columns

Column B......Column C
IATA_name...ICAO_name...(I want to use Index and Match to fill in ICAO)
AAB..............
AAE..............
ABE..............

Sheet named paid50 (This sheet is my master list with all the airports)
Column B......Column C......Column D
IATA_name...ICAO_name...Airport_name
AAB..............YARY............Queensland
AAE..............DABB............Mellah
ABE..............KABE............Allentown
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
sometimes VLookup returns the wrong answer, if there is no exact match, I would prefer an error message...

For instance, I have an airport with the 3 letter code AAC, and it reurns YARY which for another airport but lookup uses the closest meatch according some lookup rules in excel.

I am looking for help with the index function. Can anyone help with the Index function???
 
Upvote 0
Marc Enzi said:
sometimes VLookup returns the wrong answer, if there is no exact match, I would prefer an error message...

That VLOOKUP formula returns an exact match or #N/A if there isn't one.
 
Upvote 0
Thanks for the VLookup Information

I am really in search of the Index and Match formula to accomplish what I need.
 
Upvote 0
As Andrew stated, that Vlookup returns only exact matches, since False was used in the 4th argument. If True was used, then it would return closest matches.

If you really desire the more complex Index/Match way, then use this. Adjust ranges to suit you table size.


=Index(paid50!$B$1:$B$100,match(A1,paid50!$A$1:$A$100,0),1)
 
Upvote 0
Re: Thanks for the VLookup Information

Marc Enzi said:
I am really in search of the Index and Match formula to accomplish what I need.

Why do you want to use 2 functions when 1 will do the job?
 
Upvote 0
I want to use Index and match because sometimes I have to look left. (And I don't think VLookup will do the job)

As in this example

I am building a listing of 3 letter country codes from a master list sheet named OCONUSNMRB.

The master list contains 27208 locations, but has only 271 contries.

I have a list of the countries but only the names and 2 letter ISO codes, not the 3 letter country codes as listed in the long master list....

The master list is structured as follows
Column B....................Column C
State/Country..Code.....State/Country Name
AFG............................AFGHANISTAN
ANR............................ANTARCTICA
ANT............................ANTIGUA AND BARBUDA
.
.
ZIM.............................ZIMBABWE


The list I am trying to compile is setup on a sheet named Country Codes as follows:

Column D .......................Column E.........Column F
Country_Names................2 ltr_Code.......3 Ltr_Code
AFGANISTAN.....................AF.................________
ANTARCTICA....................AQ.................________
ANTIGUA AND BARBUDA.....AG.................________

I tried using Vlookup in column F as follows:

=VLOOKUP(D2,OCONUSNMRB!C2:C27208,1,FALSE)
and I get "AFGANISTAN"
but I can't seem to get it to return "AFG" (I think looking left is a limitation of VLOOKUP and that is why I want to use Index and Match.

THanks for the patience...
 
Upvote 0
I agree you need INDEX/MATCH to look left:

=INDEX(OCONUSNMRB!B2:B27208,MATCH(D2,OCONUSNMRB!C2:C27208,FALSE))

Edited for typo.
 
Upvote 0
Marc Enzi said:
I want to use Index and match because sometimes I have to look left. (And I don't think VLookup will do the job)

As in this example

I am building a listing of 3 letter country codes from a master list sheet named OCONUSNMRB.

The master list contains 27208 locations, but has only 271 contries.

I have a list of the countries but only the names and 2 letter ISO codes, not the 3 letter country codes as listed in the long master list....

The master list is structured as follows
Column B....................Column C
State/Country..Code.....State/Country Name
AFG............................AFGHANISTAN
ANR............................ANTARCTICA
ANT............................ANTIGUA AND BARBUDA
.
.
ZIM.............................ZIMBABWE


The list I am trying to compile is setup on a sheet named Country Codes as follows:

Column D .......................Column E.........Column F
Country_Names................2 ltr_Code.......3 Ltr_Code
AFGANISTAN.....................AF.................________
ANTARCTICA....................AQ.................________
ANTIGUA AND BARBUDA.....AG.................________

I tried using Vlookup in column F as follows:

=VLOOKUP(D2,OCONUSNMRB!C2:C27208,1,FALSE)
and I get "AFGANISTAN"
but I can't seem to get it to return "AFG" (I think looking left is a limitation of VLOOKUP and that is why I want to use Index and Match.

THanks for the patience...

It's hard to keep track of your questions... Didn't you post something similar before:

http://www.mrexcel.com/board2/viewtopic.php?t=176482

If the data on OCONUSNMRB is sorted on State/Country:

In F2 on Country Codes enter:

=IF(LOOKUP(D2,OCONUSNMRB!$C$2:$C$27208)=D2,LOOKUP(D2,OCONUSNMRB!$C$2:$C$27208,OCONUSNMRB!$B$2:$B$27208),"Not Found")

If the data on OCONUSNMRB is unsorted on State/Country:

=INDEX(OCONUSNMRB!$B$2:$B$27208,MATCH(D2,OCONUSNMRB!$C$2:$C$27208,0))
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,915
Members
448,532
Latest member
9Kimo3

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