Getting #N/A with INDEX and MATCH

[Huillam]

I’m using that spreadsheet as a checklist and for ease of use I wanted to check through multiple ranges whether or not a “Yes” or “No” was displayed.

I have four cells containing the following formulae:

S4: =INDEX($D$4:$D$64,MATCH(R4,$B$4:$B$64,0))

T4: =INDEX($h$4:$h$64,MATCH(R4,$f$4:$f$64,0))

U4: =INDEX($l$4:$l$64,MATCH(R4,$j$4:$j$64,0))

V4: =INDEX($p$4:$p$64,MATCH(R4,$n$4:$n$64,0))

As expected cell T4 says “No” while the others say “#N/A”

My issue is with cell W4.
With that formula I get a “No”:

=IFS(

NOT(ISNA(S4)),S4,

NOT(ISNA(T4)),T4,

NOT(ISNA(U4)),U4,

NOT(ISNA(V4)),V4

)

Plain and simple I’m displaying the first cell isn’t saying “#N/A”. It works as expected.

But with that formulae I get a “#N/A”:

=IFS(

NOT(ISNA(INDEX($D$4:$D$64,MATCH(R4,$B$4:$B$64,0)))),INDEX($D$4:$D$64,MATCH(R4,$B$4:$B$64,0)),

NOT(ISNA(INDEX($H$4:$H$64,MATCH(R4,$F$4:$F$64,0)))),INDEX($H$4:$H$64,MATCH(R4,$F$4:$F$64,0)),

NOT(ISNA(INDEX($L$4:$L$64,MATCH(R4,$J$4:$J$64,0)))),INDEX($L$4:$L$64,MATCH(R4,$J$4:$J$64,0)),

NOT(ISNA(INDEX($P$4:$P$64,MATCH(R4,$N$4:$N$64,0)))),INDEX($P$4:$P$64,MATCH(R4,$N$4:$N$64,0))

)

Is this a bug or am I missing something here? All I did was to replace the cell references with their content.

I know that final formula could be simplified to remove some the INDEX but that doesn’t solve the issue. And finally I tried to format things a bit to make the formulae easier to read.

[mikekaganski]

This might be a bug (maybe @erAck might tell?), but - I’d simplify this using IFNA instead:

IFNA(INDEX($D$4:$D$64;MATCH(R4;$B$4:$B$64;0)); IFNA(INDEX($H$4:$H$64;MATCH(R4;$F$4:$F$64;0)); IFNA(INDEX($L$4:$L$64;MATCH(R4;$J$4:$J$64;0)); IFNA(INDEX($P$4:$P$64;MATCH(R4;$N$4:$N$64;0)); FALSE()))))

[Huillam]

Oh I never suspected I could use multiple IFNA that way. It does work and is much cleaner that the solution I used (mixing IF and COUNTIFS over multiple range). Thank you very much.

[erAck]

Might be due to that IFS() does not short circuit it’s arguments (contrary to IF() and CHOOSE() that calculate only the necessary path according to the evaluated condition) so an injected #N/A may have survived in the calculation chain as a nasty side effect. There’s tdf#124710 to rewrite IFS(), which takes its time and work is ongoing.