Index/Match to Conditional Spaces

[ipperz]

Hello,

I am trying to create an Index/Match (open to other suggestions) that would return information from an array that was based around a few conditional matches.

In the example below: John Smith has pay/deductions assigned to Department 200 and 201 (rows 2:7 are for Dept. 200, rows 8:15 are for Dept. 201).

If I were to want John's "Tax 2" amount for Dept. 201, could an Index/Match or other formula find this? I can't figure out a way to create the Index array to be based around a conditional section (If Dept. 201 and name is John Smith, then array D8:E15).

Excel 2016 (Windows) 32 bit
	A	B	C	D	E
1	Department & Name	Pay Description	Amount	Deduction Description	Amount
2	200	PAY 1	25.00	TAX 1	2.33
3	John Smith			TAX 2	0.38
4				DEDUCTION 1	0.81
5				DEDUCTION 2	0.00
6				DEDUCTION 3	0.12
7				Deduction Total:	3.64
8	201	PAY 1	50.00	TAX 1	4.16
9	John Smith	PAY 2	5.00	TAX 2	0.69
10		Pay Total:	55.00	TAX 3	3.01
11				DEDUCTION 1	2.48
12				DEDUCTION 2	0.00
13				DEDUCTION 3	1.45
14				DEDUCTION 4	1.05
15				Deduction Total:	12.84
Sheet1

Thank you for your insights!

 

[Fluff]

Duplicate https://www.mrexcel.com/forum/excel-questions/1065408-index-match-conditional.html#post5116581

Please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread.
Per forum rules, posts of a duplicate nature will be locked or deleted (rule 12 here: Forum Rules).