MATCH returns #N/A but EXACT returns true

[olofmur]

Hi,

I have the following scenario:

Cell1=6.99
Cell2=6.99
Lookup range contains value 6.99

Cell1 and Cell2 both have same format and contain the value of another lookup formula.

The MATCH function returns #N/A for Cell1, but not for Cell2. When I check equality with =Cell1=Cell2 and =EXACT(Cell1,Cell2) they both return true. Using EXACT(Cell1, lookup range's value 6.99) and EXACT(Cell2,lookup range's value 6.99) also return true for both cases.

However, if I do "Remove duplicates" both Cell1 and Cell2 persist as unique values. Also, when i step through the MATCH function with F9, there is a match for both Cell1 and Cell2.

I have tried to format as text and numbers but neither works for both Cell1 and Cell2. This problem propagates (seemingly randomly) for other numeric values as well.

Any ideas?

Best,

Olof

 

[olofmur]

@joeu2004 i tried truncating with Round and now it seems to work! Thank you for that suggestion. I am still confused why they generate true though when tested as above... Anyway, problem solved and I am very greatful for all your input!

Best,

Olof

 

[joeu2004]

@joeu2004 i tried truncating with Round and now it seems to work! Thank you for that suggestion. I am still confused why they generate true though when tested as above

You're welcome. I was afraid it might solve your problem, and you would not upload an example Excel file. I really want to clarify the ambiguities in your postings and answer all of your previous and lingering questions. But I must have the example Excel file in order to that. An image or descriptions are not sufficient. Again, the devil is in the details.

So please upload an example Excel file (redacted) that demonstrates the original problem, per the instructions in my previous response.

Alternatively, please send the file to me at joeu2004 "at" hotmail.com.

-----

For future note, please do not use abstract terms ("cell1", "cell2") in your original question. Instead, provide a concrete example with correct cell names, constant values (ideally formatted to display 15 significant digits), and formulas copied from the Formula Bar. Please denote any formulas that were array-entered. Unfortunately, Excel removes the surrounding curly braces when we copy from the Formula Bar.

There are tools to make that easier. See the description of one such tool in thread https://www.mrexcel.com/forum/about-board/1017050-mrexcel-html-maker-update.html.

 

[olofmur]

I appreciate that. Unfortunately we have very strict regulations of what we can share with parties outside the company, and there is no way for me to get it passed security for this purpose (fully redacted or not). Hence the abstractions I did were necessary and I would not have been able to ask the questions without them. I will keep your feedback in mind for next time and be as clear and precise that I can.

Thanks again,

Olof