CODEWITHSUNDEEP
excelexcel-formula
user2752935
user2752935

Reputation: 81

If match error when a value does not match

I currently have this: =IF(MATCH("Example",D:D,0),"Not Available","Available"). The problem is that if the value isn't in the column, it gives the #N/A error. How do I make that go away? I have read several similar problems that might have had the solution but couldn't make sense of it.

Upvotes: 8

Views: 23120

Answers (1)

L42
L42

Reputation: 19727

Although I've commented it out, here's the formal answer to the question.

The reason why your function is throwing up an #N/A error value is because
the logical part of your IF statement cannont handle values other than Bolean (true or false).
When MATCH returns an ERROR, the logical part of IF statement was not satisfied
thus returning #N/A error.
To correct this, we add ISERROR on your IF statement like this:

=IF(ISERROR(MATCH("Example",D:D,0)),"Not Available","Available")

ISERROR evaluates if the function or statement returns an ERROR.
It returns TRUE if it is an ERROR and FALSE otherwise.
Adding that to your code let's your IF statement evaluates the result of MATCH function when it returns an ERROR.

Upvotes: 6

Related Questions