excel formula to return multiple results

[jmazorra]

Hello everyone:

I am currently using Index,Match to match records from one sheet to the other. Works great, except when I have multiple records with the same identifier. Let me explain:

The sheet named Open Leave Report contains the raw data (lookup values), while the sheet named Open Leave Capture contains the scrubbed data. So my formula looks like this

=INDEX('Open Leave Report'!O:O,MATCH('Open Leave Capture'!C9,'Open Leave Report'!B:B,0))

Works great, except when I have the following:

[TABLE="width: 284"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Employee ID[/TD]
[TD]Continuous or Intermittent[/TD]
[/TR]
[TR]
[TD]1646484        [/TD]
[TD]Intermittent[/TD]
[/TR]
[TR]
[TD]1646484        [/TD]
[TD]Continuous[/TD]
[/TR]
</tbody>[/TABLE]

So when I type the Employee ID in the Open Leave Capture it always brings back the first row, but it ignores the second record. It looks like this:

[TABLE="width: 182"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Employee ID[/TD]
[TD]Type[/TD]
[/TR]
[TR]
[TD]1646484[/TD]
[TD]Intermittent[/TD]
[/TR]
[TR]
[TD]1646484[/TD]
[TD]Intermittent[/TD]
[/TR]
</tbody>[/TABLE]

Is there any way I can format my Index/Match formula to bring back both records?

I hope I explained this well

 

[Legacy 143009]

Try;

=IFERROR(INDEX(Sheet1!$B:$B,SMALL(IF((Sheet1!$A:$A=$A2),ROW($A:$A)),ROW()-1)),"")

It's an array formula so Ctrl+Shift+Enter after paste