Forum Discussion
Can you use AND / OR in an INDEX MATCH
Hi I have am array formula that looks like this:
=INDEX('Rebate report'!A:A,MATCH(1,('Rebate report'!A:A=A2)*('Rebate report'!B:B=B2)*('Rebate report'!C:C=C2),0),1) which works.
I want to add in an OR function for the name in column A. I will add this name in Column T. In other words the match is correct if column A or T match A2 and COL B=B2 and COL C=C2 also match the criteria
I tried using the + to add T criteria but gave me a 0
=INDEX('Rebate report'!A:A,MATCH(1,('Rebate report'!A:A=A2)*('Rebate report'!B:B=B2)*('Rebate report'!C:C=C2)+('Rebate report'!A:A=T2),0),1)
Thanks for the help!
63 Replies
Hi finding this old thread and seems Sergei is the man to fix everyone’s solutions, hoping he is still around to see this and help with mine. I have a dashboard setup across three tabs on excel to assign people their work tasks, tab one is the easy read dashboard, second is the data sheet with all the tasks and where I can assign each person to each task, and tab three is where I keep all my formulaes safe 😖.
I'm using Index match to show different things each time but the one I struggle with is when I want to put the two or more people on one task, at the moment I just copy the task onto the next row down as a work around but it’s clunky and messes with figures for task numbers etc. I use people’s initials and the task priority number to determine which order they should be done (sound like micro-managing but honestly it’s not). If I put them in the same cell it doesn’t work as it gets confused so I though I could add an extra column but can figure out how to do “or” without it being confused if it sees someone else’s initials.
My current one is
=INDEX(‘Data Sheet’!A:J,MATCH(“JB1”,’Data Sheet’!J:J,0)2)
JB1 is the variable for the initials. I would either like to make it so the Cell i have JB1 in can have multiple initials in and the search would be able to look them up and pull the same results, or I can just add in Column K and L and it will search those also and pull up the same result and not be confused if I have different initials in them.
hope this all makes sense. Would appreciate anyone’s help.
INDEX/MATCH, as any lookup formula, returns first found result only. To have multiple results you need to filter the source first and combine founded results into one cell next. As variant that could be
=LET( range, TRIMRANGE('Data Sheet'!A:J), initials, TRIMRANGE('Data Sheet'!J:J), tasks, CHOOSECOLS(range,2), ARRAYTOTEXT( FILTER( tasks, initials = $L5 ) ) )or
=LET( range, TRIMRANGE('Data Sheet'!A:J), initials, TRIMRANGE('Data Sheet'!J:J), tasks, CHOOSECOLS(range,2), ARRAYTOTEXT( FILTER( initials, tasks = $L7 ) ) )depends on which exactly logic you use.
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
