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