How to use Index Match when Match value is repeating ?

[Yale2018]

Hi,

I am trying to use Index Match based on the "New" record, however the formula isn't working as it is reverting same value over and again. Can some one help me understand what I can do here to get each value in each cell.. like in this case I would need number 4 to number 9 as my output.

FYI - New and Old records are not static and may change every week with data refresh.

[TABLE="width: 278"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD]Column A[/TD]
[TD]Column B[/TD]
[TD]Column C[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]AttendeeName [/TD]
[TD]Program ID[/TD]
[TD]TypeofRecord [/TD]
[/TR]
[TR]
[TD="align: right"]1.[/TD]
[TD]Chris C[/TD]
[TD]469[/TD]
[TD]OLD[/TD]
[/TR]
[TR]
[TD="align: right"]2.[/TD]
[TD]Chris C[/TD]
[TD]5698[/TD]
[TD]OLD[/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD]3.Chris C[/TD]
[TD]585[/TD]
[TD]OLD[/TD]
[/TR]
[TR]
[TD="align: right"]4.[/TD]
[TD]Kris M[/TD]
[TD]589[/TD]
[TD]New[/TD]
[/TR]
[TR]
[TD="align: right"]5.[/TD]
[TD]Kris M[/TD]
[TD]345[/TD]
[TD]New[/TD]
[/TR]
[TR]
[TD="align: right"]6.[/TD]
[TD]Kris M[/TD]
[TD]4825[/TD]
[TD]New[/TD]
[/TR]
[TR]
[TD="align: right"]7.[/TD]
[TD]Sky V[/TD]
[TD]10394[/TD]
[TD]New[/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD]8.Sky V[/TD]
[TD]135264[/TD]
[TD]New[/TD]
[/TR]
[TR]
[TD="align: right"]9.[/TD]
[TD]Sky V[/TD]
[TD]465[/TD]
[TD]New[/TD]
[/TR]
</tbody>[/TABLE]


Thanks
Navneet

 

[Macropod]

See, for example:
http://windowssecrets.com/forums/sh...es-from-a-list?p=734296&viewfull=1#post734296
http://www.techsupportforum.com/forums/f57/excel-formula-issues-375768.html#post2567119

 

[Aladin Akyurek]

Book1
	A	B	C	D	E	F	G
1	AttendeeName	Program ID	TypeofRecord		6
2	Chris C	469	OLD		Idx	AttendeeName	Program ID
3	Chris C	5698	OLD		4	Kris M	589
4	3.Chris C	585	OLD		5	Kris M	345
5	Kris M	589	New		6	Kris M	4825
6	Kris M	345	New		7	Sky V	10394
7	Kris M	4825	New		8	8.Sky V	135264
8	Sky V	10394	New		9	Sky V	465
9	8.Sky V	135264	New
10	Sky V	465	New
Sheet1

In E1 just enter:

=COUNTIFS(C:C,"new")

In E3 control+shift+enter, not just enter, and copy down:

=IF(ROWS($E$3:E3)>$E$1,"",SMALL(IF($C$2:$C$10="new",ROW($A$2:$C$10)-ROW(INDEX($A$2:$C$10,1,1))+1),ROWS($E$3:E3)))

In F3 just enter, copy across, and down:

=IF($E3="","",INDEX($A$2:$C$10,$E3,MATCH(F$2,$A$1:$C$1,0)))

 

[Yale2018]

Thank you .. This worked ..

 

[Aladin Akyurek]

Thank you .. This worked ..

You are welcome.