Percentage Vlookup Returning Error / Incorrect Values

Hey everyone - been trying to work through this issue and has been driving me crazy!

In the attached excel, I'm trying to Vlookup a percentage value against a table, where using approximate range lookup returns an incorrect value and exact range lookup returns error.

The issue is all these percentages are round numbers and the lookup works for a select few values (e.g., 8% works, but not 6%). However, if I go to B10 or I10 and manually enter "6%" it works -- it's extremely strange.

Let me know what's going on here! Been at it all night and it's driving me insane.

Thanks!

i can't remember the last time I used VLOOKUP. Use INDEX and MATCH and with MATCH, use 1 in the MATCH Type so it returns the value less than or equal to the percentage you're looking up.
Example formula:
=INDEX(C$5:C$30,MATCH(E7,B$5:B$30,1))

Index match in this case behaves the exact same way. Try it on E5 and it returns an incorrect value ("E" instead of "F")

Ah, so this is a data problem, not a formula problem. it is not truly "6%" in the cell. There is some extraneous and small value causing the formula to return E as 5% is the next closest value that is not greater than the 6% lookup value. Clean up the whole percent column to not include formulas, or round to two decimals in your formula and drag it down and your original formula and mine will both work.

Thanks - if you look at the sheet though, I have a version to the right with hardcoded numbers and it behaves the same way. It's only when I go to 6% and hit F2 > Enter that it works...

Originally Posted by piepod

Thanks - if you look at the sheet though, I have a version to the right with hardcoded numbers and it behaves the same way. It's only when I go to 6% and hit F2 > Enter that it works...

I’m assuming the hard coded numbers were copied and pasted from your formula output as values and formatting only. Again, if you round your formula and drag it down, then you copy and paste your newly rounded values, it will work.