How when using INDEX MATCH do I use OR when setting search array?

Question

HiI 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 😖.&nbsp;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). &nbsp;If I put them in the same cell it doesn’t work as it gets confused so I thought I could add an extra column but can’t figure out how to do “or” without it being confused if it sees someone else’s initials.My current one is&nbsp;=INDEX(‘Data Sheet’!A:J,MATCH(“JB1”,’Data Sheet’!J:J,0)2)&nbsp;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.&nbsp;hope this all makes sense. Would appreciate anyone’s help.

m_tarler · Accepted Answer

it would be very helpful if you could provide a sample workbook (no private info please). If you can't attach/upload here then provide a public link to a cloud share solution like OneDrive or Google Drive. A less but still helpful alternative is to provide pictures of the worksheets.

That said I think you are indicating that on the 'Data Sheet' you have task lines assigned to people based on initials in column J. You want to do a lookup of that task based on those initials in column J but certain tasks you would like to have 2 or more people assigned to.

So i would like to note that you did tag with Office 365 so assuming you have the latest you have access to newer functions like XLOOKUP and FILTER. So your above INDEX-MATCH could be done using XLOOKUP like:

=XLOOKUP("JB1",‘Data Sheet’!J:J,'Data Sheet’!B:B)

This assumed that the '2' at the end of the original formula was intended to return column 2. Regardless, check into this function as I think you will find it very useful.

I also recommend checking out FILTER which for the above could be:

=FILTER(‘Data Sheet’!B:B, “JB1”=’Data Sheet’!J:J, "not found")

With FILTER you can easily return multiple rows and columns. For example to return ALL of "JB" tasks it would be:

=FILTER(‘Data Sheet’!B:B, “JB”=LEFT(’Data Sheet’!J:J, 2) , "not found")

Now that doesn't solve your question about having multiple people but it can. For example you can use XLOOKUP with wildcard characters:

=XLOOKUP("*JB1*",‘Data Sheet’!J:J,'Data Sheet’!B:B,"none found", 2)

then in column J you can enter "AB1, CD1, EF1, JB1, XY1" and it will find the JB1

similarly you can find every task assigned to "JB" using FILTER using something like:

=FILTER(‘Data Sheet’!B:B, ISNUMBER(SEARCH(“JB”,’Data Sheet’!J:J) ), "not found")

hope that helps.

Forum Discussion

How when using INDEX MATCH do I use OR when setting search array?

2 Replies

Resources