EXCEL: Create Array Formula out of INDEX/MATCH with multiple results

0
votes

my aim is to convert a massive excel sheet with different projects, employees and hours worked per month into an overview per employee. It should be possible to display the projects the employee is involved in and how many hours he worked per project per month. The original sheet looks something like this:

see simplified excel sheet here

I managed to find the projects Person A worked in by filtering through the INDEX/MATCH function. I applied the formula to the whole row where the employees are listed and receive multiple results of projects. My question is how to transform the formula into something more effective to copy all of the matched results (projects) into a column (see 1).

This is what I have so far, if matches the employee name in a certain area; the output is the first match of the project he is involved in:

=INDEX(B2:J3;1;MATCH("Person A";Sheet1!B3:E3;0))

How can I copy this to the bottom cells to copy all of the matched results? Does it help to create an array formula with this?

1
arraysexcelexcel-formula
Does your version of Excel support either FILTER() or AGGREGATE() ?? – Gary's Student
How is your origin data filled? When it's filled like Date, Person, Project in a Table-setup it will make your work much more easier – EvR

1 Answers

0
votes

You can use he following formula in cell B9:

=IFERROR(INDEX($2:$2,SMALL(IF($3:$3=$B$8,COLUMN($3:$3)-COLUMN(INDEX($3:$3,1,1))+1),ROWS(A$1:A1))),"")

It indexes row 2 and looks for the column number of the first match in row 3 that equals the value in B8 (=Person A). When dragging down it will look for the second match ROWS(A$1:A1) will become ROWS(A$1:A2) = 2.

For Person B you can use this formula in cell B14:

=IFERROR(INDEX($2:$2,SMALL(IF($3:$3=$B$13,COLUMN($3:$3)-COLUMN(INDEX($3:$3,1,1))+1),ROWS(A$1:A1))),"")

I hope this is what you where looking for.

PS if you paste the following formula in cell C9 you will get the sum result for Person A on Project XY in month 10 2019: =IF(OR($B9="",C$8=""),"",SUMPRODUCT(($B$2:$K$2=$B9)*($B$3:$K$3=$B$8)*($A$4:$A$6=C$8),B4:K6))

Note: That is provided that the value in cell C8 equals the value in cell A4.