• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

I can't get an exact match...

E

EldoFuoj

New Member
Hello all,

I am trying to use index/match, and I am having trouble getting an exact match. If I set the match to 1, I get a result, but it is incorrect. If I set the match to 0, I get an error. Any help with this would be greatly appreciated.

The formula I am trying to use is =INDEX(N3:N102,MATCH(F28,M3:M102,0)). I need the formula to look at the Average Points, then return the value in the right cell of the table on the right of the spreadsheet.

Thanks in advance,
-Chris
 

Attachments

  • Point_Calculator_B.xlsm
    14 KB · Views: 10
There is something odd about the numbers in column M. If you type in the first two again - 0.0 and 0.1 - and then drag copy down, your formula will work. They obviously aren't an exact match at the moment - possibly a tiny bit bigger than they look.
 
Clear the column M and type the values again

Or you could use math instead =100-F28*5
 
I assume pasted values are calculated.

Whenever dealing with decimals in Excel (or any other software/programming for that matter), you'd need to be careful. As decimals are actually stored as floats, and is "close" approximation but not exact.

One of common way to avoid this issue is to use integer number to do Index/match etc, and then use calculation at last stage to return decimal. Or to use ROUND().

Have a read of link to understand more about Floating Point Precision.
https://www.microsoft.com/en-us/mic...y-does-excel-give-me-seemingly-wrong-answers/
 
Thank you for the help. My initial thought was the format of the numbers was incorrect, but I have cleared the numbers and put them back in several times, but it didn't work. It never occurred to me to use math, but that worked just fine. I would be curious to find out what the problem was one day though.

Thanks again,
-Chris
 
Chihiro, you are correct. They were calculated then pasted in. I never knew that would cause a problem. I will check that for sure now. Thanks so much.
 
It won't have worked if you just kept copying and pasting them in from source. If you had entered them as I suggested, they would have worked. It's also worth adjusting your formula thus:

=INDEX($N$3:$N$102,MATCH(ROUND(F$28,0),$M$3:$M$102,0))
 
I actually tried typing them in manually, but I thought maybe the paste job reformatted the cells, so I deleted the columns and started over. It still didn't work. It has something to do with the odd numbers. All the multiples of five work fine, but anything that isn't a multiple of five gives the error.
 
You must log in or register to reply here.
Back
Top