Excel #NA error returning TRUE

1

I have an Excel formula that I use to check against duplicates that is not working. The data is not in order, which is why the VLOOKUP was needed to match values.

=EXACT(B2,VLOOKUP(B2,$F$1:$F$76,1,0))

I have used this formula in the past and it has worked because #NA errors would mean there is no match.

When checking using the "Evaluate Formula" button the VLOOKUP portion returns an error, but the EXACT portion returns TRUE.

Why is this happening? If there is no solution, is there a better formula to use?

socksxbirkenstocks

Posted 2013-06-10T17:54:03.510

Reputation: 95

Strangely, this problem was only fixed after reopening my worksheet and re-entering the formula. The Calculate Sheet option and Data Refresh buttons could not alleviate the problem. – socksxbirkenstocks – 2013-06-10T21:03:52.860

Answers

2

EXACT gives you a case-sensitive comparison, do you need that? if so try

=SUMPRODUCT(EXACT(B2,$F$1:$F$76)+0)>0

or if you don't care about case try

=COUNTIF($F$1:$F$76,B2)>0

barry houdini

Posted 2013-06-10T17:54:03.510

Reputation: 10 434

I was hoping for a formula to check text cells in the future, but your formula works well for the problem I was having. – socksxbirkenstocks – 2013-06-10T21:01:33.257

What do you mean "text cells" - the formulas I suggested should work OK for text or numbers – barry houdini – 2013-06-10T21:17:16.340

Asked: 2013-06-10T17:54:03.510

Viewed: 166 times

Active: 2013-06-10T18:06:57.577