Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 331
Default Problem with VLOOKUP Results.

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!
  #2   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 2,480
Default Problem with VLOOKUP Results.

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!


  #3   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 2,480
Default Problem with VLOOKUP Results.

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!


  #4   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 35,218
Default Problem with VLOOKUP Results.

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 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
  #5   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 331
Default Problem with VLOOKUP Results.

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!




  #6   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 35,218
Default Problem with VLOOKUP Results.

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
  #7   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 331
Default Problem with VLOOKUP Results.

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

  #8   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 2,480
Default Problem with VLOOKUP Results.

Quite right Dave, I hadn't noticed it was a single column!!!
The eyes are getting worse<bg

--
Regards
Roger Govier

"Dave Peterson" wrote in message
...
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 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


  #9   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 2,480
Default Problem with VLOOKUP Results.

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
AdataText to ColumnsDelimitedNextclick Otherenter a - in the white box
next to itFinish

Then, use the amended formula that Dave Peterson gave you.
--
Regards
Roger Govier

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

  #10   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 35,218
Default Problem with VLOOKUP Results.

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


  #11   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 331
Default Problem with VLOOKUP Results.

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



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

Virtual Drinks for all!


"Dave Peterson" wrote:

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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Sum results of VLOOKUP Dewayne Excel Worksheet Functions 7 November 28th 07 05:53 PM
vlookup results in 0 sveazie Excel Discussion (Misc queries) 6 July 17th 07 09:20 AM
Vlookup with two results Luke Excel Discussion (Misc queries) 2 March 22nd 07 06:41 PM
to sum up all value results from VLOOKUP Linn Excel Worksheet Functions 1 March 7th 07 03:45 AM
how do you add vlookup results? Anna Excel Worksheet Functions 3 March 26th 05 04:05 AM


All times are GMT +1. The time now is 10:31 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"