Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
accl
 
Posts: n/a
Default 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   Report Post  
 
Posts: n/a
Default

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   Report Post  
accl
 
Posts: n/a
Default

it works! thank you very much for your kind advice...andy b
  #4   Report Post  
 
Posts: n/a
Default

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



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
lookup combine vlookup with hlookup Doug Excel Worksheet Functions 2 April 23rd 23 11:42 AM
how do i get mutiple values using vlookup in excel, lookup value . Abhijeet Excel Discussion (Misc queries) 4 May 19th 05 04:30 AM
Vlookup based on two lookup values Trip Excel Worksheet Functions 2 April 8th 05 06:25 PM
Vlookup & Lookup function error Beginner Excel Worksheet Functions 9 January 11th 05 01:37 AM
Problem with LOOKUP & VLOOKUP ramsdesk Excel Worksheet Functions 1 November 17th 04 08:52 AM


All times are GMT +1. The time now is 03:36 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"