Index Match Non-Blank Cells Multiple Matches

[daveasu]

I have a list of employees with their names and corresponding departments, and their annual bonus amount.

I need to find all matches by department, and return employee name and the bonus. There may be multiple matches per department.

If the department number to lookup is in D1, the following formula will only return the first match?

<c2:c8),0))
<c2:c8),0))

(apologies for the image file of the formula, for some reason the 2nd half of the formula was being truncated)<c2:c8),0))<c2:c8),0))[ code]<="" html=""></c2:c8),0))<c2:c8),0))[></c2:c8),0))
</c2:c8),0))

 

[AhoyNC]

Try:
The formula in D2 is an array formula and must be entered with CTRL-SHIFT-ENTER.
Drag formulas down column as needed.

Excel Workbook
	A	B	C	D	E
1	Dept	Name	Bonus	101
2	100	Emp1	1000	Emp3	500
3	101	Emp2		Emp4	250
4	101	Emp3	500
5	101	Emp4	250
6	102	Emp5
7	103	Emp6	750
8	103	Emp7
Sheet

 

[daveasu]

AhoyNC THANK YOU!! I was just now able to test this on our large dataset -- it works perfectly!!

 

[AhoyNC]

You're welcome. Thanks for the feedback.