CODEWITHSUNDEEP
excelerror-handlingexcel-formula
Anthony
Anthony

Reputation: 23

IF Statement with OR Criteria and ISERROR

I have a formula that is not working.

=IF(OR(AC358="",ISERROR(AC358), AC358=0),VLOOKUP(M358,FCal,3,TRUE),VLOOKUP(AC358,FCal,3,TRUE)

Column AC has a vlookup in it that will either return a real value, 0, or an error #N/A.

When AC (the vlookup) returns a #N/A error the IF statement fails in its current format.

However, the IF Statement works when I either

  1. hard code a real value or a zero into AC, or

  2. if I replace the OR Criteria with just a single criteria ISERROR(AC358)

So I know the ISERROR is working by itself but it is not working inside the OR statement.

Upvotes: 2

Views: 3894

Answers (1)

user4039065
user4039065

Reputation:

Checking for an error with ISERROR or ISNA must be done separately from other calculations; you cannot nest an error check with more conventional criteria in an OR. An OR that includes a check on a cell that includes a #N/A will return #N/A, not TRUE or FALSE. =IF(ACD358=0, TRUE, FALSE) when ACD358 contains a #N/A error does not return FALSE; it returns #N/A.

=IF(ISNA(AC358), VLOOKUP(M358, FCal, 3), IF(OR(AC358="", AC358=0), VLOOKUP(M358, FCal, 3), VLOOKUP(AC358, FCal, 3)))

When the error check returns TRUE, processing passes immediately to the first M358 VLOOKUP function; the other IF and OR are not calculated.

Given that a blank, zero or #N/A are likely to return another #N/A from FCal, perhaps this shorter version with IFERROR would be better.

=IFERROR(VLOOKUP(AC358, FCal, 3), VLOOKUP(M358, FCal, 3))

TRUE is the default for VLOOKUP's range_lookup parameter. Beyond including it for clarity, it is not necessary.

Upvotes: 2

Related Questions