vendredi 29 septembre 2017

Excel If formula with OR, avoiding #N/A

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?

Aucun commentaire:

Enregistrer un commentaire