Problem with VLOOKUP Results.

  • Thread starter Thread starter Greg
  • Start date Start date
G

Greg

My issue is:

Col A has the airport codes (ABE) and my table array has the airport codes
decoded.
The formula I used:
=VLOOKUP($A$2:$A$2544,'AIRPORT CODES'!$A$2:$A$2989,1)
should have returned the decoded airport city of ABE - Allentown, PA, USA
Allentown Bethlehem EastonAirport.
Instead it returned ABC - Albacete, Spain Albacete Airport. Can anyone tell
me what I'm doing wrong?
 
Hi Greg

You need to set the 4th argument of the Vlookup to be False or 0 instead of
True, in order to find an exact match if the data is not in sorted order.
also, you should be looking up a single cell, not a range of cells

=VLOOKUP($A2,'AIRPORT CODES'!$A$2:$A$2989,1)
 
Apologies, to include the 4th parameter that should have course have read
=VLOOKUP($A2,'AIRPORT CODES'!$A$2:$A$2989,1,0)

but I'm not sure about the 1. That is only finding the same name in the
first column of Airport Codes.
Surely you would want some offset from this, perhaps 2?

=VLOOKUP($A2,'AIRPORT CODES'!$A$2:$A$2989,2,0)


--
Regards
Roger Govier

Roger Govier said:
Hi Greg

You need to set the 4th argument of the Vlookup to be False or 0 instead
of True, in order to find an exact match if the data is not in sorted
order.
also, you should be looking up a single cell, not a range of cells

=VLOOKUP($A2,'AIRPORT CODES'!$A$2:$A$2989,1)
Click to expand...
 
And if the OP wanted a second column, that range would have to be at least 2
columns wide:

=VLOOKUP($A2,'AIRPORT CODES'!$A$2:$b$2989,2,0)

If the OP were only looking at a single column, maybe =match() would be a better
function to use.

Roger said:
Apologies, to include the 4th parameter that should have course have read
=VLOOKUP($A2,'AIRPORT CODES'!$A$2:$A$2989,1,0)

but I'm not sure about the 1. That is only finding the same name in the
first column of Airport Codes.
Surely you would want some offset from this, perhaps 2?

=VLOOKUP($A2,'AIRPORT CODES'!$A$2:$A$2989,2,0)
Click to expand...
 
Somewhere, something is wrong, inputting the formula
=VLOOKUP($A2,'AIRPORT CODES'!$A$2:$A$2989,1,0)
gave me #N/A result and I can't seem to figure out where the issue lies.
Any help would be great.



Roger Govier said:
Apologies, to include the 4th parameter that should have course have read
=VLOOKUP($A2,'AIRPORT CODES'!$A$2:$A$2989,1,0)

but I'm not sure about the 1. That is only finding the same name in the
first column of Airport Codes.
Surely you would want some offset from this, perhaps 2?

=VLOOKUP($A2,'AIRPORT CODES'!$A$2:$A$2989,2,0)
Click to expand...
 
That means that there was not an exact match between what's in A2 and all the
entries in A2:A2989 of sheet 'airport codes'.

Maybe you have an extra space in the table or in A2.
 
I think I've lost my way now. I've double checked everything, but I'm still
getting #N/A as the result.

If I may, this is an example of what is in column A.

A1 list the title of the column, Origin Airport Code, and the codes are
alphabetically listed A2 is ABE, A3 is ABI., etc.

Origin Airport Code
ABE
ABI
ABQ
ACT
ALB

This is how my table array is setup.

Airport Codes
AAC - Al Arish, Egypt Al Arish Airport
AAX - Araxa, Brazil Araxa Airport
ABC - Albacete, Spain Albacete Airport
ABE - Allentown Bethlehem-Easton International, PA, USA
ABI - Abilene, TX, USA Municipal

My table array has ABE - AllentownBethlehem-Easton International, PA. Do
you think the way my table array is defined is wrong?
 
Quite right Dave, I hadn't noticed it was a single column!!!
The eyes are getting worse<bg>
 
Hi

Your data on Airport Codes needs to be in 2 separate columns.

A B
ABE Allentown Bethlehem-Easton International, PA, USA
etc.

The easiest way to split it if it is all in column A is to select column
A>data>Text to Columns>Delimited>Next>click Other>enter a - in the white box
next to it>Finish

Then, use the amended formula that Dave Peterson gave you.
 
First, I would do what Roger wrote. I'd split that table into two columns. The
first column to hold the code and the second column to hold the name.

But if you want...(and I wouldn't use this!), you could use:

=VLOOKUP($A2&" - *",'Airport codes'!$A$2:$A$2989,1,0)

=vlookup() is one of the worksheet functions that can use wildcards.

So the forumula says to concatenate whatever is in A2 (say ABE) with a space,
hyphen, space, and a wild card. Then match that in column A of the Airport
Codes worksheet.

Essentially the same as:

=VLOOKUP("ABE - *",'Airport codes'!$A$2:$A$2989,1,0)

when you're matching on the ABE code.
 
Guys, Thanks for your help. I've done everything possible and I'm still
having problems. I'm going step away from this problem a bit and come back
to later.

Greg
 
Back
Top