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.


Greg wrote:

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?

"Dave Peterson" wrote:

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.

Greg wrote:

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" wrote:

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" <roger@technology4unospamdotcodotuk wrote in message
...
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)

--
Regards
Roger Govier

"Greg" wrote in message
...
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?



--
I am always thankful for the help everyone gives me.

Virtual Drinks for all!


--

Dave Peterson


--

Dave Peterson