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

 

[PaddyD]

i'd suspect that =istext() / isnumber() would return different results for the two target cells. To solve it, we';d need to know more about the source of the data / formulas that were populating the cells.

 

[olofmur]

Hi,

ISTEXT() and ISNUMBER() both return TRUE/FALSE for all of the entries, as would be expected. No problem here I am afraid!

Thank you for the reply.

BR Olof

 

[FDibbins]

where is the data coming from?
What formulas are generating the results?

What happens if you use =match("6.99",lookup-range,0)

 

[olofmur]

The data is coming from the company's database. Cell1 and Cell 2 are calculated price points from another sheet within the same workbook, as Ordered Value/Order Nr of Pcs. Note that Cell1 and Cell2 sit in the same column, meaning they have the same formula and are calculated based on the same data. The lookup range contains raw data from the database, no calculations done here.

=match("6.99", lookup-range,0) generates #N/A.

I solved the problem by manually entering the calculated price for every row that generated the error. Took me 2 hours but at least it works now.

Thanks for the reply!

BR Olof

 

[FDibbins]

If you over-typed the formula with a value, and it worked, that kinda tells me that either the value was not exactly what you thought it was (6.99 will not match 6.9900000000001) or, the answer was text, not numeric.

What was the formula that produced the criteria number, and what (if any) was the formula that produced the lookup range?

 

[olofmur]

I agree, but would not EXACT(Cell1,Cell2) and =Cell1=Cell2 give fals ein that case? Also, both Cell1 and Cell2 had the same formatting according to ISTEXT and ISNUMBER.

The formula used to compute the 6.99 is =F2*1000/D2, with F holding the ordered value in thousands, and D holding the ordered Pcs. Then, there is a lookup to find a product's price using =INDEX(OrderData!G:G,MATCH(B2,OrderData!$B:$B,0)). G column in OrderData contains the price (6.99) and B column in OrderData contains the products, which is matched on B2.

 

[joeu2004]

There is a lot of ambiguity in your postings. For example, first you say that ISTEXT returns TRUE and ISNUMBER returns FALSE "for all of the entries", then you say that there is a formula (not sure where) of the form =F2*1000/D2. Obviously, that result is numeric; so ISTEXT should be FALSE and ISNUMBER should be TRUE.

In any case, if you are still interested in an explanation, the best thing to do is: upload an example Excel file (redacted) that demonstrates the problem to a file-sharing website (e.g. box.net/files), and post the public/share URL in a response here. Please test the URL first, being careful to log out of the file-sharing website. With box.net/files, ignore any preview errors, and just download the file.

Caveat: Some participants in this forum object to that because they are unable or unwilling to download external files. Fine, they can sit on the sidelines while others solve your problem. No harm; no foul. But some problems can only be resolved this way. The devil is in the details, and obviously there is a communication problem.

FYI, EXACT(cell1,cell2) is not necessary or useful in this case. EXACT should be used when the strings might have a mix of upper and lower case, and you want an exact match. In Excel, "lowhigh"="LOWHIGH" is TRUE. But EXACT("lowhigh","LOWHIGH") is FALSE.

I suspect the problem is either a mix of text and numeric values, as suggested earlier, or due to binary arithmetic anomalies. For the latter, ISNUMBER must be TRUE. In that case, the formula =ROUND(F2*1000/D2,2) might solve your problem.

If not, please provide the example Excel file.

 

[Aladin Akyurek]

There is a lot of ambiguity in your postings. For example, first you say that ISTEXT returns TRUE and ISNUMBER returns FALSE "for all of the entries", then you say that there is a formula (not sure where) of the form =F2*1000/D2. Obviously, that result is numeric; so ISTEXT should be FALSE and ISNUMBER should be TRUE.

[...]

Achtung...

That ISNUMBER is TRUE of the result of

=F2*1000/D2

does not necessarily mean that F2 or D2 or both F2 and D2 are true number. If any of these or both are text number, the operators * and / will coerce them into true numbers with a true number as formula result.

 

[olofmur]

Hi,

Point taken, that was a typo. Obviously it should have been FALSE/TRUE and not TRUE/FALSE.