# Lookup table is finding the wrong value

#### DataJo

##### New Member
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

### 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
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
Are the values in VALUES!\$B\$4:\$B\$51 sorted in ascending order?

#### Jonmo1

##### MrExcel MVP
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
Thank you so much, the Index/Match formula works great! Yes they were in ascending order. That's really helpful

#### Jonmo1

##### MrExcel MVP
Glad to help, thanks for the feedback.

Replies
2
Views
46
Replies
10
Views
113
Replies
3
Views
37
Replies
8
Views
67
Replies
5
Views
68