Forum Discussion

David's avatar
David
Copper Contributor
Oct 27, 2017

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...
Excel Desktop
Formulas & Functions
Steve8140's avatar
Steve8140
Copper Contributor
Jun 11, 2025

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.

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Jun 13, 2025

    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.

Resources