Excel If formula with OR, avoiding #N/A

user_4887991 0
Viewed 763

Currently facing a problem, long story short: I'm trying to combine 2 formulas into one, by making use of the OR-function, but if one of the 2 conditions does not exist, it gives me an #N/A back. There's 3 conditions that can happen: "MTI", "MTI Z" and "MTO". What I would like is that the formula searches for any combination in column L with either "MTI" or "MTI Z" (might also be both) and if that combination exists, give back a 1. If not (so only MTO exists) then return a 0 (in this case it will be an #N/A, but I can fix that with either ISNA or IFERROR).

Formula 1 is:

=IF(CONCATENATE(A2,B2,"MTI")=INDEX(L:L,MATCH(CONCATENATE(A2,B2,"MTI"),L:L,0),0),1,0)

Formula 2 is

=IF(CONCATENATE(A2,B2,"MTI Z")=INDEX(L:L,MATCH(CONCATENATE(A2,B2,"MTI Z"),L:L,0),0),1,0)

Both formulas work, giving back a "1" when there is respectively a "MTI" or "MTI Z"

However, when I try to combine them, if 1 of the 2 does not exist in the list, it gives me an #N/A, even though I'm using OR (which would state if at least 1 of the 2 exists, go ahead).

=IF(OR(CONCATENATE(A2,B2,"MTI Z")=INDEX(L:L,MATCH(CONCATENATE(A2,B2,"MTI Z"),L:L,0),0)
,CONCATENATE(A2,B2,"MTI")=INDEX(L:L,MATCH(CONCATENATE(A2,B2,"MTI"),L:L,0),0)),1,0)

How can I adjust my formula so that it does work?

if-statement excel-formula excel
4 Answers

This is a bit more straight forward:

=SIGN(COUNTIF(L:L,CONCATENATE(A2,B2,"MTI*")))
user_28593470

Wrap each of these formulas in an IFERROR([formula],0)

user_63874790

Try adding two IFERROR 'wrappers' so you return FALSE when no match, not #N/A.

=IF(OR(IFERROR(CONCATENATE(A2,B2,"MTI Z")=INDEX(L:L, MATCH(CONCATENATE(A2,B2, "MTI Z"), L:L, 0)), FALSE),
       IFERROR(CONCATENATE(A2,B2,"MTI")=INDEX(L:L, MATCH(CONCATENATE(A2,B2,"MTI"), L:L, 0)), FALSE)),
 1, 0)
Admin1

You can re-write your formula as

=IF(OR(ISNUMBER(MATCH(CONCATENATE(A2,B2,"MTI"),L:L,0)),
       ISNUMBER(MATCH(CONCATENATE(A2,B2,"MTI Z"),L:L,0))),1,0)
user_60752590
Related