MATCH function error?

**trommelom99** · 02-22-2020, 08:25 PM

Hi,

I have been using the INDEX/MATCH functionality several times, but would not say I have Black Belt.
Now I ran into an issue I really don't understand. I have scaled down my problem trying to isolate the issue from my original workbook. The issue is that my lookup value in the MATCH function is not working when I summarize some percentage numbers (72%+9%) and do a match against 81%, while if I enter 81% manually in the cell is the Match working. It is only some value combinations that is not working. 72%+10% is working.

I have tried both Excel 2016 and 365, and the problem is the same if I use VLOOKUP and XLOOKUP

Attached is an example workbook. If you change the values in the Yellow field you will be able to see the problem (hopefully :-)

Don't know if this is an Excel error, or an error from my side (I wold assume so, but I really don't understand what is going on)

Thanks

**GregM56** · 02-22-2020, 08:54 PM

Hi there. I don't know much, but I suspect it's the format of the cell.
I've returned your sample spreadsheet and it works fine.
Best regards!

**trommelom99** · 02-22-2020, 09:12 PM

Thanks GregM56,

What you did is typing the number in cell C17 manually, and that is the same I do in cell C20, and that is working. My problem is when is sum C15+C16 in cell C17. Try changing the values in cell C16 to 10% in my original file and you see that this is working.

I really don't understand what is going on, and I'm fully aware of that I make an stupid error, but this one isn't straight forward

Thanks for looking into my problem

**GregM56** · 02-22-2020, 09:25 PM

Look at this . . . I'm pretty sure it's a cell format problem. I placed the data next to the cell and it works fine. I used formulas this time, not just typing in the numbers.

**FlameRetired** · 02-22-2020, 10:36 PM

Hello trommelom99. Welcome to the forum.

Formatting does not affect value. This is a floating point issue. They are ubiquitous in computer math.

They are not always easy to detect either. Since Excel is limited on how many characters it can display you might not detect it, but even the tiniest decimal difference will not permit a match. You will need to round to an acceptable precision.
Try this in F17

Formula:

Please Login or Register  to view this content.

**trommelom99** · 02-22-2020, 11:47 PM

Thanks so much FlameRetired.
Your solution resolved my problem, and its logical when you think about from the floating point view, but I defiantly didn't think about that.

**GregM56** · 02-23-2020, 12:17 AM

...and I learned something too, thanks!

**AliGW** · 02-23-2020, 02:43 AM

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

MATCH function error?

LinkBack

Thread Tools

Rate This Thread

Display

MATCH function error?

Re: MATCH function error?

Re: MATCH function error?

Re: MATCH function error?

Re: MATCH function error?

Re: MATCH function error?

Re: MATCH function error?

Re: MATCH function error?

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Error while using match function

[SOLVED] Vlookup to Pivot Table, using Match function, returns error if can't find match value

Match function with #N/A error

index function error using match function to get data from a cell in an array

Error with Match function

Match Function Error

[SOLVED] Match function, N/A error, how do I get around it?

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions