Lookup table is finding the wrong value

[DataJo]

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

 

[par60056]

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]

Are the values in VALUES!$B$4:$B$51 sorted in ascending order?

 

[Jonmo1]

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]

Thank you so much, the Index/Match formula works great! Yes they were in ascending order. That's really helpful

 

[Jonmo1]

Glad to help, thanks for the feedback.