Excel - using INDEX / MATCH to retrieve data from a table based upon multiple matches

Question

I have a rota listing names (down the side) and dates (across the top) and there is also an additional column for region/area for each name e.g.:

--Name--------Region-------13/12/19--------14/12/19--------15/12/19--------16/12/19-----------17/12/19
John Smith    North           IN             IN              OFF              IN                OFF
Jane Doe      North           OFF            IN              IN               IN                OFF
Bob Newhart   South           IN             IN              OFF              OFF               OFF

I also have a list of jobs completed by each person e.g.:

--Name--------Region-----Job#---CompletedDate-----JobType
John Smith                22        14/12/19         xx
John Smith                23        14/12/19         yy
John Smith                24        16/12/19         zz
Bob Newhart               25        14/12/19         aa

I know how to look up the Region from the name =INDEX(table[Region],MATCH(A2,table[Name],0),0) and I've even worked out how to look up whether they're in or off based on a 2-way INDEX/MATCH (e.g. =INDEX(Rota!B:B,MATCH(A2,Rota!A:A,0),MATCH(D2,Rota!1:1,0))

My issue is when a person changes region e.g. John Smith moves from North to West:

--Name--------Region-------13/12/19--------14/12/19--------15/12/19--------16/12/19-----------17/12/19
John Smith    North           IN             IN              OFF                                
John Smith    West                                                            IN                OFF
Jane Doe      North           OFF            IN              IN               IN                OFF
Bob Newhart   South           IN             IN              OFF              OFF               OFF

I need to look up what their region was on the day the job was done so I can summarise the jobs done per region separately from the number of jobs done per person.

I'm guessing that it is something like the 2nd INDEX/MATCH above but with some form of "if the cell is blank then move down to the next match of their name in the same column". Does that even make sense?

Any help would be appreciated.

Thanks, Alan

T. Altena T. Altena · Accepted Answer · 2019-12-19T15:38:59

What about creating a helper column that combines person + region? e.g. you'll have a (hidden) column containing values like John Smith~north (you'll want a separator that properly handles Jane South).

Additionally, I think the first index function you've created can be substituted by a VLOOKUP:

=VLOOKUP(A2,rota!$A:$E, 2, FALSE) 
// look for value A2 
   in the first column of A:E on the Rota sheet, then 
   copy over the value from the 2nd
   Don't approximate matches

The second could be re-created with a simple COLUMN() reference to itself if the dates are entirely congruent between the sheets (i.e. if the dates are the same for each column).

EDIT

Took a second (closer) look - You're kind of pushing Excel to the limits of what it is designed for. You could try and hack around this, e.g. by wrapping the second INDEX function in an IF that checks if the outcome of the index is not empty and if it is, moves the match down one. But this will not tackle an example where John moves between three regions.

Conceptually, you're working on three tables: 1. Name/date with in or out values 2. Name/date with region values 3. Job/date with name values

Because your ROTA table is merging 1 and 2, you are getting multiple rows for one individual, and that is giving you trouble in the third table. Maybe in your design you can split these, which will make it easier to grab them with the functions you use. This starts to look a lot like a database though...

But maybe you add the region in the fields of the ROTA table?

--Name----------13/12/19--------14/12/19--------15/12/19--------16/12/19-----------17/12/19
John Smith      IN~N            IN~N            OFF~N           IN~S               OFF~S

This means that for the region you can use the function that you'll already used, if you use one appended character in all cases:

=LEFT(INDEX(table[Region],MATCH(A2,table[Name],0),0), LEN(INDEX(table[Region], MATCH(A2,table[Name],0),0)) -2)
=RIGHT(INDEX(table[Region], MATCH(A2,table[Name],0),0), 1)

Or if you want to use the delimiter (~ in the example)

=LEFT(INDEX(table[Region],MATCH(A2,table[Name],0),0), FIND("~", INDEX(table[Region],MATCH(A2,table[Name],0),0) -1)
=RIGHT(INDEX(table[Region],MATCH(A2,table[Name],0),0), FIND("~", INDEX(table[Region],MATCH(A2,table[Name],0),0) -1)

Excel - using INDEX / MATCH to retrieve data from a table based upon multiple matches

1 Answers

EDIT