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
 

Some videos you may like

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

Marc Enzi

Board Regular
Joined
Dec 13, 2004
Messages
92
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???
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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.
 

Marc Enzi

Board Regular
Joined
Dec 13, 2004
Messages
92

ADVERTISEMENT

Thanks for the VLookup Information

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

NBVC

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
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)
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092

ADVERTISEMENT

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?
 

Marc Enzi

Board Regular
Joined
Dec 13, 2004
Messages
92
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...
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
I agree you need INDEX/MATCH to look left:

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

Edited for typo.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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))
 

Watch MrExcel Video

Forum statistics

Threads
1,123,380
Messages
5,601,285
Members
414,440
Latest member
Kim0204

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
Top