Lookup table is finding the wrong value

DataJo

New Member
Joined
Mar 10, 2014
Messages
2
Hello
We have created a lookup formula in Excel to look at 2 columns on another sheet, to find out what value the character (in cell K) has. This is using this formula: =IFERROR(LOOKUP(K$3,VALUES!$B$4:$B$51,VALUES!$A$4:$A$51),"")

However, the character in cell K is a sublevels, e.g. 5a, 5b, 5c. When it pulls through the value from the lookup table it is only showing the highest value- e.g. if cell K is 5c it is showing the value for 5a instead (this is the highest of the 5s).

Is there a way to make sure it selects the exact value and doesnt default to the highest for whatever reason it is doing this?

Many thanks!!

Jo
 

Some videos you may like

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

par60056

Well-known Member
Joined
Jul 26, 2012
Messages
1,581
Office Version
  1. 2011
  2. 2010
Platform
  1. Windows
  2. MacOS
Welcome to the forum.

If you use VLookup rather than Lookup and set the last parameter to 0 it will only return an exact match.
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
Are the values in VALUES!$B$4:$B$51 sorted in ascending order?
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
If you use VLookup rather than Lookup and set the last parameter to 0 it will only return an exact match.

Vlookup can't find a match in column B and return a corresponding value from column A (to the left)

You could use Index/Match though

=INDEX(VALUES!$A$4:$A$51,MATCH(K$3,VALUES!$B$4:$B$51,0))
 

DataJo

New Member
Joined
Mar 10, 2014
Messages
2
Thank you so much, the Index/Match formula works great! Yes they were in ascending order. That's really helpful :)
 

Watch MrExcel Video

Forum statistics

Threads
1,108,509
Messages
5,523,313
Members
409,511
Latest member
hitesh222002

This Week's Hot Topics

Top