I am looking to shorten/simplify a formula that looks up values in a table using INDEX/MATCH as part of an IF formula that includes logical OR and AND functions. For example, I could use the following general structure:
=IF(OR(INDEX(MATCH)=A,INDEX(MATCH)=B,INDEX(MATCH)=C),0,IF(AND(INDEX(MATCH)=D,INDEX(MATCH)=E),1,""))
The INDEX(MATCH) elements have been abbreviated here simply to convey the general idea, but the formula can quickly run into many lines when written normally and when the number of conditions is more than 2 or 3 and there are additional nested IF statements.
I have tried to shorten the formula using the following (ie grouping the conditional values in an OR function) but it returns a #VALUE error:
=IF(INDEX(MATCH)=OR(A,B,C),0,1)
Is there another way to write a shortened conditional formula of this kind? Or does the INDEX(MATCH) need to be included for each separate condition as written in the first code snippet?
Bookmarks