Looking up, Matching, or indexing???

How do you want to do it?

A Sheet Formula or by VBA code?

For a Sheet formula try: VLookUp

For code try: OffSet

Yes, I have been trying VLookup, but am having some problems like I keep getting Uruguay UY matching up with USA.

I think there is a way to get it to match up exactly.

I am compiling a rather large list and I want this to be robust enough to handle differences like Caps and lower case and perhaps misspellings...

How have you tried VLOOKUP?

Are you setting the last argument to FALSE?

Hi,

It looks like you want to use the VLOOKUP function.

So in the first line of records in the Country Code cell that has a "?", you want to use the VLOOKUP function. For example, say the AIRPORT column is column A and CODE is column B, and the first record, Aarhus is row 2 etc.

Now, depending where the separate Country Code list is located (if it is in the same workbook on a different worksheet, or in a different workbook), say the COUNTRY LIST is in column A and the COUNTRY CODE LIST is in column B, you would write something like the following in the COUNTRY CODE cell where you want the result to be in place of the "?", which would be in D2:

=VLOOKUP(A2,Country Code List!A:B, 2, 0)

If you follow what I have said, everything in the previous example will be the same except for the 'Country Code List!A:B' part. It depends where the Country Code List is. If it is in the same workbook, that relates to my example above, which would say that the Country Code List exists in the same workbook on a separate worksheet where that worksheet has the name of Country Code List, and the actual list is contained in columns A and B.

You can use the wizard, which will walk you through the structure of the function. I suggest you google the VLOOKUP function, with what I have given you, you should be able to figure it out.

Marc Enzi said:

Yes, I have been trying VLookup, but am having some problems like I keep getting Uruguay UY matching up with USA.

I think there is a way to get it to match up exactly.

I am compiling a rather large list and I want this to be robust enough to handle differences like Caps and lower case and perhaps misspellings...

Click to expand...

If F2:G8 is sorted on F in ascending order, you can invoke:

D2:

=IF(LOOKUP(C2,$F$2:$F$8)=C2,LOOKUP(C2,$F$2:$G$8),"Not Listed")

Otherwise:

=VLOOKUP(C2,$F$2:$G$8,2,0)

The first formula is faster.

Thankyou

Index?? What is wrong with my formula?

I am a bit of a novice an dhaving difficulty getting the index function to work for me.

I am trying to use the following formula to pull 4 letter ICAO airport codes from a list with corresponding 3 letter IATA codes.

Here is what I am using but I keep getting #N/A
in sheet named mergeIATAdb

I am using a formula in column E, I want it to use the 3 letter code in column B containing the letter IATA airport code and search another sheet named paid50 for the corresponding 4 letter ICAO code.

=INDEX(paid50!$A$1:$A$50000,MATCH(B2,paid50!$B$1:$B$50000,0))

THis is what the mergeIATAdb sheet looks like
Column B Column E
IATA_Code...ICAO_Code
CRP.............#N/A

In the paid50 sheet I plainly see
Column A.....Column B.....Column C
IATA_Code...ICAO_Code...Airport_Name...
CRP............KCRP............Corpus Christi Intl Airport

I feel kind of stupid, I have two Excel books and I still can't get this to work...

Thanks for any help.

Re: Index?? What is wrong with my formula?

Marc Enzi said:

I am a bit of a novice an dhaving difficulty getting the index function to work for me.

I am trying to use the following formula to pull 4 letter ICAO airport codes from a list with corresponding 3 letter IATA codes.

Here is what I am using but I keep getting #N/A
in sheet named mergeIATAdb

I am using a formula in column E, I want it to use the 3 letter code in column B containing the letter IATA airport code and search another sheet named paid50 for the corresponding 4 letter ICAO code.

=INDEX(paid50!$A$1:$A$50000,MATCH(B2,paid50!$B$1:$B$50000,0))

THis is what the mergeIATAdb sheet looks like
Column B Column E
IATA_Code...ICAO_Code
CRP.............#N/A

In the paid50 sheet I plainly see
Column A.....Column B.....Column C
IATA_Code...ICAO_Code...Airport_Name...
CRP............KCRP............Corpus Christi Intl Airport

I feel kind of stupid, I have two Excel books and I still can't get this to work...

Thanks for any help.

Click to expand...

Is paid50 sorted in ascending order on IATA_code?

Yes, it is sorted in order on IATA