Ask Your Question
0

Getting #N/A with INDEX and MATCH

asked 2019-08-10 12:47:45 +0200

Huillam gravatar image

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.

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
0

answered 2019-08-10 13:00:02 +0200

updated 2019-08-10 13:00:41 +0200

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()))))
edit flag offensive delete link more

Comments

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.

Huillam gravatar imageHuillam ( 2019-08-10 16:13:23 +0200 )edit

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.

erAck gravatar imageerAck ( 2019-08-12 14:25:30 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2019-08-10 12:47:45 +0200

Seen: 22 times

Last updated: Aug 10