|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
May 16th, 2005, 11:56 AM
|
|||
|
|||
vlookup with lookup value of different length
sortkey value
80101 10 80102 20 80103 30 80104 40 80105 50 80106 60 80107 70 80108 80 80109 90 801010 100 801011 110 801012 120 801013 130 801014 140 801015 150 801016 160 801017 170 801018 180 801019 190 801020 200 80151 11 =VLOOKUP($D2,$A$2:$B$41,1) When I use the above formula to lookup for 80151, I get the wrong value "90" (the corresponding sortkey is 80109) instead of my expected value "11" . Is there a limitation of the VLOOKUP function that the lookup value has to be sorted in order for correct result? 
|
|
#2
May 16th, 2005, 12:01 PM
|
|||
|
|||
|
Hi
Try using the last argument in VLOOKUP. I also don't understand why you ask the formula to return the first column as the result, rather than the second! Try this: =VLOOKUP($D2,$A$2:$B$41,2, FALSE) Hope this helps. -- Andy. "accl" wrote in message ... sortkey value 80101 10 80102 20 80103 30 80104 40 80105 50 80106 60 80107 70 80108 80 80109 90 801010 100 801011 110 801012 120 801013 130 801014 140 801015 150 801016 160 801017 170 801018 180 801019 190 801020 200 80151 11 =VLOOKUP($D2,$A$2:$B$41,1) When I use the above formula to lookup for 80151, I get the wrong value "90" (the corresponding sortkey is 80109) instead of my expected value "11" . Is there a limitation of the VLOOKUP function that the lookup value has to be sorted in order for correct result?
|
|
#3
May 16th, 2005, 05:51 PM
|
|||
|
|||
|
it works! thank you very much for your kind advice...andy b
|
|
#4
May 17th, 2005, 09:23 AM
|
|||
|
|||
|
Pleased to help and thanks for the feedback!
-- Andy. "accl" wrote in message ... it works! thank you very much for your kind advice...andy b
|
| Thread Tools | |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Thread Starter | Forum | Replies | Last Post |
| Vlookup based on two lookup values | Trip | Worksheet Functions | 2 | April 8th, 2005 06:25 PM |
| Vlookup with upper and lower case lookup values | Angus | Worksheet Functions | 4 | August 18th, 2004 08:48 AM |
| Lookup fields - what's wrong with them? | NATHAN SANDERS | New Users | 2 | May 4th, 2004 10:51 AM |
| VLOOKUP - return cell is before lookup column | Simon | Worksheet Functions | 3 | April 6th, 2004 12:37 PM |
Linear Mode
