Index Match with Multiple Criteria and Results

[kingoffinance]

I need A11 and A12 to return the invoice numbers that have the employee name from B9 and that are blank in column D. I have been banging my head against the wall for hours now and I can't get it. Please help!

Thank you in advance,
Anthony

 

[Weazel]

something like...

Excel 2016 (Windows) 32 bit

	A	B	C	D
2	Invoice #	Item	Employee 1	Job Completed
3	1	wheels	steve
4	2	brakes	steve	yes
5	3	brakes	james
6	4	lug nuts	steve
7	5	wheels	chris
8
9	Employee	Steve
10	Open Invoice #
11	1
12	4
13
14
15

<tbody>
</tbody>

Sheet1​

Array Formulas Cell Formula A11 {=IFERROR(INDEX($A$3:$A$7,SMALL(IF($C$3:$C$7=$B$9,IF($D$3:$D$7="",ROW($A$3:$A$7)-ROW($A$3)+1)),ROWS($A$11:A11))),"")} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

 

[DRSteele]

If you have Excel 2016 365, you can use function TEXTJOIN to report the result in one cell as text. Using Weazel's template, try this:

	D
11	1, 4

<tbody>
</tbody>

Sheet33​

Array Formulas Cell Formula D11 {=TEXTJOIN(", ",1,IF((C3:C7=B9)*(D3:D7=""),A3:A7,""))} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try to enter the {} manually yourself.

<tbody>
</tbody>