Index Match

[Bwalsh]

I am new to index match as I just recently downloaded Excel 2016 and I am beyond lost.
For context, I will be adding my formula to cell Q3 and dragging it down. Sheet 2 runs from A1-N25435
I am trying to look up an ID Number (sheet 1, column B) and return of the requisition number(s) (sheet 2, column D) associated with that candidate ID (Sheet 2, Column C). I run into issues as there could be up to 20 requisitions associated with a specific ID and I can't seem to get any data to return.
Ideally, this information is returned horizontally.
If anyone could provide assistance I would be most appreciative. Every formula I have attempted to enter has returned an error.

 

[JPARKHURST]

I am trying to look up an ID Number (sheet 1, column B) and return of the requisition number(s) (sheet 2, column D) associated with that candidate ID (Sheet 2, Column C). I run into issues as there could be up to 20 requisitions associated with a specific ID and I can't seem to get any data to return.
Ideally, this information is returned horizontally.

My Friday afternoon thoughts:

Is your output on Sheet 3 or oen of the previous sheets?
Are you able to modify Sheet 2?
If so, can you sort sheet 2 & to then look at data?


If answers to above are no, then you will very likely need to do it in VBA, which means modifying your workbook to a macro-enabled one (different extension, and permissions to use macros on your network).

If all above are no, then I would say the way you would go at it was to build an array of row numbers, concatenate them. i'm not sure how you would split that up horizontally, but if the first wtwo can be done, then third can be.

Jon

 

[AhoyNC]

Maybe something like this?
This is an array formula and must be entered with CTRL-SHIFT-ENTER.
Drag formula across and down as needed.

Excel Workbook
	A	B	C	P	Q	R	S	T
1		ID#
2		ID1
3		ID2			668	642	832
4		ID3
5		ID4
6		ID5
7		ID6
8		ID7
Sheet1

Excel Workbook
	B	C	D
1		ID#	Req. #
2		ID1	668
3		ID2	545
4		ID3	775
5		ID1	642
6		ID5	525
7		ID1	832
8		ID2	555
Sheet2