Index Match - Return Multiple Values

问题:

I am new to index match as I just 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.

edit: I tried this match formula that works but only returns ones of the results. I am looking to return all of the values. =MATCH(B3,'Sheet2'!$C$1:$C$25435,0)

I then tried: =IF(ISERROR(INDEX($A$1:$N$25435,SMALL(IF('[Sheet2]Candidate ID'!!$A$1:$N$25435=$Q$3,ROW($A$1:$N$25435)),ROW(1:1)),2)),"",INDEX([Sheet2A$1:$N$25435,SMALL(IF($A$1:$N$25435=$D$1,ROW($A$1:$N$25345)),ROW(1:1)),2)) which yielded no result.

回答1:

Try this in Q3 and fill down until you run out of requisition numbers to return,

=iferror(index('sheet2'!d:d, aggregate(15, 7, row($1:$25435)/('sheet2'!c$1:c$25435=b$3), row(1:1))), text(,))