I am using an IFS formula to check for different conditions and produce a value:

=IFS(LEN(AP5)=0,"",AP5="N/A", "N/A", VLOOKUP(B5,'Calculation Date'!B:T,18, FALSE), "N/A", AVERAGE(AP5:AT5), AVERAGE(AP5:AT5))

The trouble I am having is with the third test around the VLOOKUP. When it's true, the formula works correctly. However, when the value returns false, I get an "#N/A" value in the cell.

If VLOOKUP is returning false, shouldn't it move onto my fourth test?

CodePudding user response：

First, check that the vlookup actually returns FALSE, and not #N/A (it should return FALSE only if this is the value in the lookup table) if the lookup value is missing from the lookup table, it would return #N/A, and the whole IFS function becomes #N/A.

Second, note that you are checking for NAs wrong. You should write:

=IFS(LEN(AP5)=0,"",isna(AP5), na(), vlookup...

CodePudding user response：

I managed to get this working correctly. I had to add a " 0" to my lookup value in VLOOKUP. In other words

VLOOKUP(B5 0,'Calculation Date'!B:T,18, FALSE)