Excel Match not working with Currency

[Toxic Parsnip]

This one's got me beaten.

I am trying to troubleshoot a spreadsheet that adds a series of numbers (in £), and then uses this total (also in £) to look up a corresponding discount that matches the total. I am using INDEX and MATCH to find the discount, to ensure accuracy.

The formula works fine until the total equals £648.90. Despite there being a £648.90 in the index list, #N/A is displayed. I have triple-checked the formulas, the sum and tried it by typing the number directly into the reference - this works, leading me to believe that the issue is with the calculations that contribute to the total.

Any suggestions??

 

[nodroj81]

You can try using the round function and embed within your match/index functions.

For example, in cell A1 you have 648.90, in reality cell could be reading as 648.90046358, so Excel is missing the match when you use the match formula to find the other 648.90.

If you used =ROUND(A1,2), then Excel would read this is =ROUND(648.90046358,2) = 648.90

So your match formula would read,

=MATCH(ROUND(A1,2),[array],0)

 

[Toxic Parsnip]

What a hero!

I had an inkling that it would be something like this, but never thought of the ROUND function.

Many Thanks