Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sum results of VLOOKUP | Excel Worksheet Functions | |||
vlookup results in 0 | Excel Discussion (Misc queries) | |||
Vlookup with two results | Excel Discussion (Misc queries) | |||
to sum up all value results from VLOOKUP | Excel Worksheet Functions | |||
how do you add vlookup results? | Excel Worksheet Functions |