Vlookup = Vheadache: Lookup returning previous row cell data

[leeman]

Hi guys,

I have the most unusual problem i just cant find a solution for. I have used Vlookup sucessfully for a while but today I have met my match!!.

I have a list of airport codes and I need to look up these 2 digit codes and find the relevant airport and country.

For some reason when I use the lookup function it is finding the correct lookup value but returning the column cell in the previous row.

Any ideas?

 

[sanrv1f]

Seem to be there are some leading/trailing spaces in the cells of column D, I got the above said result when I added a space to "9W" in column D,

Try 'Find and replace' to clean the data on column D and use the VLOOKUP with the fourth argument (FALSE)

 

[Aladin Akyurek]

When i use the following function in cell B1:

=VLOOKUP(A1,'IATA Airline Designator'!A2:D1251,3)

The return value is " Aviones de Oriente, C.A. (AVIOR) "

It should be " Jet Airways (India) Private Limited "

if i use the formula =VLOOKUP(A1,'IATA Airline Designator'!A2:D1251,3,FALSE)

the return value is: "N/A'

both these results are incorrect

Try to remove errant spaces around the entries (see Sankar's post).

The formula that you originally run requires that 'IATA Airline Designator'!A2:D1251 is sorted in ascending order on A2:A1251. If the table is guaranteed to be sorted at all times, you can run a faster formula...

Either:

=IF(LOOKUP(A1,'IATA Airline Designator'!$A$2:$A$1251=A1,
    LOOKUP(A1,'IATA Airline Designator'!$A$2:$A$1251,
      'IATA Airline Designator'!$C$2:$C$1251,"Not Found")

Or:

=IF(VLOOKUP(A1,'IATA Airline Designator'!$A2:$D$1251,1,1)=A1,
   VLOOKUP(A1,'IATA Airline Designator'!$A$2:$D$1251,3,1),
   "Not Found")

Otherwise:

=VLOOKUP(A1,'IATA Airline Designator'!A2:D1251,3,0)

 

[DaveTheBall]

Hi Leeman

Using "FALSE" as everyone suggested, makes this work perfectly. e.g. AA definitely returns American Airlines. It's possible that ...

1. Your formula rfefers to the row above for its argument
2. You have still left the FALSE out of your formula
3. The fact that you have duplicate keys (e.g. 9V, 9X) is not helping, and we have no idea how your sheet is sorted

 

[leeman]

Thanks everyone Happy camper here!!!

The issues was there was 90 incidents of trailing or leading spaces in the lookup column. Having found and replaced them I am recieving expected results.

TOP EFFORT

 

[leeman]

Good work. This simplest solution is often overlooked

 

[bethg7]

First thing I see is that your list of 1250 airlines and countries has 415 duplicates! The VLookup feature cannot distinguish one duplicate from another. You need to have each value unique.