Forum Discussion
Vlookup approximate match giving wrong result
Hello, I use vlookup to categorise the average price into price range buckets. However, I'm getting inconsistent results:
| Weight | Total price | Average price |
| 40.2 | 68340 | Weight/Total = 1700 (c8) |
Lookup table:
| Threshold | Price range |
| 1650 | 44: $1650 - <$1700 |
| 1700 | 45: $1700 - <$1750 |
Formula used:
=VLOOKUP(C8,'Lookup table',2,TRUE)
-> return 44: $1650 - <$1700
However, if I type '1700' into a cell and vlookup against that cell, it returns '45: $1700 - <$1750' which is the correct answer.
I have removed all decimals on the 'Weight' and 'Total price' by using the 'decrease decimal' icon in the ribbon, but it still returns '44: $1650 - <$1700'.
Is this a bug and is there a way around this to get excel to return '45: $1700 - <$1750'?
vanilla8910 Decreasing the number of decimals displayed isn't going to help as the numer that is used by Excel in the calculation stays the same. To overcome your problem you need to round the calculation to 0 decimals like this:
in C8: =ROUND(A8/B8,0)
or change the VLOOKUP to this:
=VLOOKUP(ROUND(C8,0),'Lookup table',2,TRUE)
2 Replies
vanilla8910 Decreasing the number of decimals displayed isn't going to help as the numer that is used by Excel in the calculation stays the same. To overcome your problem you need to round the calculation to 0 decimals like this:
in C8: =ROUND(A8/B8,0)
or change the VLOOKUP to this:
=VLOOKUP(ROUND(C8,0),'Lookup table',2,TRUE)
Riny_van_Eekelen thank you so much! that makes perfect sense now I realise the formula bar is still showing 40.2 but the cell is showing 40.
