Help with Index/Match formula.

[jmazorra]

Hello:

I am using the following formula on my report:

=INDEX('Open Leave Report'!$P:$P,MATCH(D2,'Open Leave Report'!$C:$C,0))

Works well, unless I have the reference sheet has multiple rows with the same look up value ie:

Column C                                                   Column P
[TABLE="width: 361"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]
DVX46J6K[/TD]
[TD][/TD]
[TD="align: right"]6/1/2018[/TD]
[/TR]
[TR]
[TD]DVX46J6K[/TD]
[TD]Y[/TD]
[TD="align: right"]6/29/2018[/TD]
[/TR]
[TR]
[TD]DVX46J6K[/TD]
[TD]Y[/TD]
[TD="align: right"]8/24/2018[/TD]
[/TR]
</tbody>[/TABLE]

So when I look up the data on the return sheet it looks like this

[TABLE="width: 361"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]DVX46J6K[/TD]
[TD][/TD]
[TD="align: right"]6/1/2018[/TD]
[/TR]
[TR]
[TD]DVX46J6K[/TD]
[TD][/TD]
[TD="align: right"]6/1/2018[/TD]
[/TR]
[TR]
[TD]DVX46J6K[/TD]
[TD][/TD]
[TD="align: right"]6/1/2018[/TD]
[/TR]
</tbody>[/TABLE]

How can I modify my Index/Match formula to return the values exactly as my reference table?

 

[wideboydixon]

You'll need an array formula and SMALL ...

Book1
	D	E	F	G
2	DVX46J6K			6/1/2018
3	DVX46J6K			6/29/2018
4	DVX46J6K			8/24/2018
Sheet1

Cell Formulas
Range	Formula
G2	{=INDEX('Open Leave Report'!$P:$P,SMALL(IF('Open Leave Report'!$C:$C=D2,ROW('Open Leave Report'!$C:$C)),COUNTIF(D$2:D2,D2)))}
Press CTRL+SHIFT+ENTER to enter array formulas.

WBD