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?

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?

it works! thank you very much for your kind advice...andy b

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