Precision Error With Match Function In Excel

user_10962789 0
Viewed 179

I have a data frame, say from A1:C100, where each cell is a value (not derived from any formula) that happens to be stored as a percentage. I copy and paste values of a column in the data frame (say Column A), sort it from largest to smallest, and then paste it in say Column E. I then use =MATCH(E1, $A$1:$A$100, 0), which works as expected and returns the correct row.

However, if I then add a constant to every value in E, say column F is =E+1, and use =MATCH(F1-1, $A$1:$A$100, 0) about 90% of the values will still be correct, but some return #N/A.

How can I work around this without changing the original data frame? I have already tried rounding data to various precision points (for example =MATCH(ROUND(F1-1,4), $A$1:$A$100, 0)), or using non-exact matching (for example =MATCH(F1-1, $A$1:$A$100, 1) or even something like =MATCH(F1-.999, $A$1:$A$100, -1)) but no luck.

Any other suggestions/anyone else ever encounter something like this? What is the underlying issue?

excel-formula excel
1 Answers

The problem with your precision correction is that it's only being applied to the 'lookup' component of the Match function. To get more accurate 'match' rate, advise you either round the values being 'searched' at source, or simply deploy the following:

= Match(round(range_1,6), round(range_2,6), 0)

where you would substitute range_1, range_2 with the relevent/respective ranges per your example/q.

Note - the advantage of doing this 'at source' (i.e. inserting an additional column, say, range_3, where range_3 = round(range_2,6), then substitute range_2 with range_3 in the eqn. above) concerns computational speed (this would be far quicker, especially if range_2 is substantially long, e.g. >30k rows). The reason for this should be obvious: calculations within the match function are repeated for each and every match / cell that is performed, whereas the 'at source' version would only apply the calculation 'once' (i.e. across all rows/cells in question).

Hope this makes sense, and best of luck with your endeavours.

PS - change round(range,6) to round(range,4) if necessary. I usually like 6 dps when using something like this in conditional formatting for validation reasons...

Ta, J

user_136076720
Related