Finding 1st, 2nd, 3rd... Nth value by excel formula (vlookup/if/macth..)

[jgopalk]

Hi there,

I just wanted to fix the following problem that last few days struck my work.

Data:
[TABLE="class: cms_table, width: 881"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD="align: right"]10/5/2016[/TD]
[TD]RAM[/TD]
[TD][/TD]
[TD]10/5/2016[/TD]
[TD]RAM[/TD]
[TD]RAHIM[/TD]
[TD]SUJA[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD="align: right"]10/5/2016[/TD]
[TD]RAHIM[/TD]
[TD][/TD]
[TD]10/6/2016[/TD]
[TD]RAM[/TD]
[TD]RAHIM[/TD]
[TD]SUJA[/TD]
[TD]SANGEETHA[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD="align: right"]10/5/2016[/TD]
[TD]SUJA[/TD]
[TD][/TD]
[TD]10/10/2016[/TD]
[TD]RAM[/TD]
[TD]RAHIM[/TD]
[TD]SUJA[/TD]
[TD]SANGEETHA[/TD]
[TD]JOHN[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD="align: right"]10/6/2016[/TD]
[TD]RAM[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD="align: right"]10/6/2016[/TD]
[TD]RAHIM[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD="align: right"]10/6/2016[/TD]
[TD]SUJA[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD="align: right"]10/6/2016[/TD]
[TD]SANGEETHA[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD="align: right"]10/10/2016[/TD]
[TD]RAM[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD="align: right"]10/10/2016[/TD]
[TD]RAHIM[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD="align: right"]10/10/2016[/TD]
[TD]SUJA[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD="align: right"]10/10/2016[/TD]
[TD]SANGEETHA[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD="align: right"]10/10/2016[/TD]
[TD]JOHN[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Black font is available data and red colour font is required data by formula.

This is for huge data so we can not change formula each time and each look up cells. kindly try to fix it by formulas and not by<acronym title="visual basic for applications" style="border-width: 0px 0px 1px; border-bottom-style: dotted; border-bottom-color: rgb(0, 0, 0); cursor: help;">VBA</acronym> code..

Kindly check and fix it.

Thanks,
Gopal

 

[jgopalk]

The names i have given in order. But it is not the exact case. Names might be interchanged.

 

[CyrusTheVirus]

If you don't mind the use of a helper column (I would recommend using it), then perhaps the below would work for you.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L
1	Helper	Date	Name	*	Dates	Names	Names	Names	Names	Names	Names	Names
2	42648-1	10/5/2016	RAM	*	10/5/2016	RAM	RAHIM	SUJA	0	0	0	0
3	42648-2	10/5/2016	RAHIM	*	10/6/2016	RAM	RAHIM	SUJA	SANGEETHA	0	0	0
4	42648-3	10/5/2016	SUJA	*	10/10/2016	RAM	RAHIM	SUJA	SANGEETHA	JOHN	0	0
5	42649-1	10/6/2016	RAM	*	*	*	*	*	*	*	*	*
6	42649-2	10/6/2016	RAHIM	*	*	*	*	*	*	*	*	*
7	42649-3	10/6/2016	SUJA	*	*	*	*	*	*	*	*	*
8	42649-4	10/6/2016	SANGEETHA	*	*	*	*	*	*	*	*	*
9	42653-1	10/10/2016	RAM	*	*	*	*	*	*	*	*	*
10	42653-2	10/10/2016	RAHIM	*	*	*	*	*	*	*	*	*
11	42653-3	10/10/2016	SUJA	*	*	*	*	*	*	*	*	*
12	42653-4	10/10/2016	SANGEETHA	*	*	*	*	*	*	*	*	*
13	42653-5	10/10/2016	JOHN	*	*	*	*	*	*	*	*	*
Sheet3

 

[jgopalk]

Thank you very much Mr. Virus

It is awesome. This is what I was expecting. Great work.

Thanks,
Gopal

 

[alansidman]

Look her for a UDF solution: http://www.mrexcel.com/forum/excel-questions/969080-vlookup-two-more-matches.html

 

[Peter_SSs]

Without a helper column ..

D2 is copied down.
E2 is copied across and down.

Excel 2010 32 bit
	A	B	C	D	E	F	G	H	I	J	K
1	Date	Name		Dates	Names
2	5/10/2016	RAM		5/10/2016	RAM	RAHIM	SUJA	0	0	0	0
3	5/10/2016	RAHIM		6/10/2016	RAM	RAHIM	SUJA	SANGEETHA	0	0	0
4	5/10/2016	SUJA		10/10/2016	RAM	RAHIM	SUJA	SANGEETHA	JOHN	0	0
5	6/10/2016	RAM
6	6/10/2016	RAHIM
7	6/10/2016	SUJA
8	6/10/2016	SANGEETHA
9	10/10/2016	RAM
10	10/10/2016	RAHIM
11	10/10/2016	SUJA
12	10/10/2016	SANGEETHA
13	10/10/2016	JOHN
14
List by date

Cell Formulas
Range	Formula
D2	=IF(D1="","",IF(D1=MAX(A$2:A$13),"",SMALL(A$2:A$13,COUNTIF(A$2:A$13,"<="&D1)+1)))
E2	=IF($D2="","",IF(COLUMNS($E2:E2)>COUNTIF($A$2:$A$13,$D2),0,INDEX($B$2:$B$13,MATCH($D2,$A$2:$A$13,0)+COLUMNS($E2:E2)-1)))