Index Match Function skips value

[Turtlexcel]

Hi everyone,

Ive encountered an issue where the index match function finds the same number causing it to skip a value and gives me the wrong one.

Hopefully the following images makes it clearer than my wording.

Reference Table.JPG

This is the table reference, i have assigned a percentage score in the first column and the equivalent score in the second column.

The formula i used:

Please Login or Register  to view this content.

For some reason, the results for 0.14 and 0.15 comes to the same percentage score of 7.40% and this causes a skip in the sequence and i am not getting the 10% percentage score if the result is at 0.4.

I have tried almost everything, using different excel, using fixed values, adding the match type 0 - exact value for the above formula and still could not find a solution.

If it helps, i used the same formula but with a larger table and it came out correct, i only see this issue with the smaller sample sizes.

Appreciate any help i can find, thanks.

[FDibbins]

It would help if we could actually see your data in the file itself, rather than just a screen shot.

However, having said that, I see that you dont have the 3rd argument in the MATCH part of your formula. Without specifying how you want the results returned, excel defaults to 1, whereby MATCH finds the largest value that is less than or equal to lookup value. If you want an exact match, use 0

[Turtlexcel]

Hi FDibbins,

Thank you for the feedback. I have attached a sample file of my issue to make it easier.
Excelforum sample 1.xlsx

WRT the 3rd argument, I have tried to include exact match in the formula but it doesn't seem to work, giving me an error beyond 0.13 value. With that said, thank you for your quick response and help.

[FDibbins]

hmmm that 1 has me stumped!!

=EXACT(C8,F2) = TRUE so they are both identical
=MATCH(F2,$C$2:$C$32,0) returns #N/A meaning it cant find F2
=ISNUMBER(C8) = TRUE
=ISNUMBER(F2) = TRUE so they are both numbers

when I physically type 0.16 into C8, it works

I will push this to the other experts

[josephteh]

If you change the 3rd argument to 1, it gives you the correct answer. You also get correct answer with LOOKUP, G2=LOOKUP(F2,C2:C32,B2:B32).

[windknife]

Another option,

G2
=IF(F2<C2,0,INDEX($B$2:$B$32,MATCH(F2&"",$C$2:$C$32&"",0)))

[FlameRetired]

Also this works.

Please Login or Register  to view this content.

This appears to be a floating point issue. Are any of your data the results of prior calculations?

[Turtlexcel]

Hi everyone,

Thank for the advice so far. I have tried all the suggestions you all have kindly provided and it still wont fix the problem. Not sure why the exact match function does not seem to work for me as i had tried all options (1,0,-1) but above a certain value it just revers to #NA error. I had thought this was an issue with my excel but i tried it on several others excel but result was the same.

So far the only way it can refer to the % value correctly is when you link F2 to the cell reference (e.g. F2=C7), so decided to use data validation to use a list of the numbers for cell F2. This is the momentary quick fix but requires manual input, whereas the intention was to automate the functions.

WRT to FlameRetired's question, Yes the data in the original file are all calculated from formulas linking to others, however i had rounded up the numbers and pasted the exact values to see if it would work, but even then it didn't manage to solve the issue.

Again, i am grateful for all the helpful suggestions from everyone thus far. Thank you.

[Glenn Kennedy]

Jut how (EXACTLY) did you generate the two columns of data (columns B & C)?

[rorya]

The stored value for C8 is actually 0.16000000000000003 which appears to be what MATCH is looking at, and therefore failing to find an exact match.

[davsth]

although if the table is representative of reality it just needs a mathematical formula

=IF(AND(F2>=C2,F2<=C32),0.07+(C2-0.1)/10,"")

[MrShorty]

It looks like floating point "error" to me. Somewhere in the calculation schemes for the tabulated numbers and the lookup value, the lookup value will end up being smaller than the corresponding tabulated value by a bit or two. In such cases, an approximate match lookup will return the previous entry because the lookup value is smaller than what you think is the corresponding entry, so it will return the previous entry. An exact match lookup will say that the two are different, so it returns N/A.

At this point, davsth solution seems the best. There will still be floating point errors in his version, but, because his calculation is not relying on comparing two floating point numbers, any floating point errors are not seen.

A couple of links about floating point errors.
more than you want to know about floating point arithmetic: https://docs.oracle.com/cd/E19957-01..._goldberg.html
An Excel specific one: https://learn.microsoft.com/en-us/of...ccurate-result

[Turtlexcel]

Hi everyone, Thanks again for looking over the issue.

davsth, the formula works perfectly, just needed to change the C2 into F2 in the if true statement. So thank you very much for the workaround and will consider the question solved without needing the INDEX/MATCH function.

Glenn, not sure if i can explain it well, but the initial starting point of 7% and 0.1 is a threshold value and then is increased by a fixed value that was the result of the difference between the threshold value and the maximum value.

Roya, i suspect you might be right but i was unsure why the stored value would not give the exact value, so i tried again using FDibbins initial solution, which was to retype all the numbers in column C manually and the original function fixed itself, extracting the correct values from the table. Initially i had retyped only 1 value in column C which was 0.15, but it did not solve the whole problem and requires retyping of all values in column C. I guess this is where MrShorty and FlameRetired's suggestion of floating error point comes into play, which i have very limited knowledge on and would need to read up on it.

All in all, thank you all once again and really appreciate the community for helping me out with my issue, i will change this thread to "SOLVED". Thanks.

[FlameRetired]

You are welcome. Thank you for the feedback and marking your thread Solved.