0

I'm experiencing a very odd behaviour with Excel lookups and match: if I try a lookup or match for 7.03, it will find a cell, but if I lookup 7.02+0.01, it fails. For example:

A B
1 7.01 a
2 7.02 b
3 7.03 c
4 7.04 d
5 7.05 e
  • If I use =VLOOKUP(7.03, A1:B5, 2, 0) it correctly returns c
  • If I do =VLOOKUP(7.01+0.01,A1:B5, 2, 0), it correctly returns b - it's looking up 7.02
  • If I do =VLOOKUP(7.02+0.01,A1:B5,2,0) it FAILS and returns #N/A - it also fails for equivalent XLOOKUP and MATCH formulae

It also fails on other calculated values - not just this one. If I do an 'inexact' match type, it finds the wrong value (7.02).

I can work around this, but is this a bug or can I control for it - how!?

I'm on Excel for Mac Version 16.84 (24041420)

Improve this question
0

1 Answer 1

Reset to default
2

Calculations involving numbers with decimal fractions can always produce small differences in value, because such numbers generally have infinite binary expansions that are subject to rounding.
Therefore, if possible, this type of search should be done using integers, or rounding can be used

=VLOOKUP(ROUND(7.02+0.01,2),A1:B5,2,0)
Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .