Forum Discussion
Can you use AND / OR in an INDEX MATCH
Hi David,
If it returns 0 that's something in your figures. Formula works like
if ABC or T matches it return some number
if ABC and T matches it returns #N/A
if no one of ABC and T matches it returns #N/A
Thanks Sergei
Your second statement is not what I am trying to do.
I want it to give a name in Column from the Rebate Report if:
if A=A2 OR t=A2 AND B = B2 AND C=C2 return a cell ref for name
if A=A2 AND T=A2 AND B=B2 AND C=C2 return a cell ref for name. This should return a ref and not NA. This seemed different from what you said it would do in the formula.
If A not match A2 AND T also not match A2 OR B not match B2 OR C not match C2 then return NA.
Let me know if this makese sense. Thanks.
Hi David,
Just to clarify first one, where is OR?
A=A2 OR (t=A2 AND B = B2 AND C=C2)
(A=A2 OR t=A2) AND (B = B2 AND C=C2),
...
David wrote:if A=A2 OR t=A2 AND B = B2 AND C=C2 return a cell ref for name
Hi Sergei,
The second one is where the OR should be. Thanks
(A=A2 OR t=A2) AND (B = B2 AND C=C2),
Hi David,
More exactly
(A=A2 OR A=T2) AND (B = B2 AND C=C2)
, that was my misprint. In this case array formula could be
=IFERROR(INDEX('Rebate report'!A:A,MATCH(1,((('Rebate report'!A:A=A2)+('Rebate report'!A:A=T2))>0)*('Rebate report'!B:B=B2)*('R'!C:C=C2),0),1),0)First multiplier in MATCH imitates OR condition, next are with AND