SMALL kth "2nd instance" ?

[seanh1016]

Is it possible for SMALL to return the second instance of, say, the 2nd smallest value in a list? For example, if I have 2 birthdays on the same say, I'd like to somehow return one with small(range,2) and the other with small(range,2).

I know this is similar to vlookupnth. But I'm trying to avoid using vlookupnth.

 

[jbeaucaire]

My understanding of how SMALL() works is that if small(range,2) and small(range,3) are identical, they both will return in order of rank as the "k" value increases.

Excel Workbook
	A	B	C	D
1	Dates	*	*	in Order
2	1/15/2009	*	*	1/15/2009
3	1/16/2009	*	*	1/16/2009
4	1/17/2009	*	*	1/16/2009
5	1/18/2009	*	*	1/17/2009
6	1/16/2009	*	*	1/18/2009
7	1/20/2009	*	*	1/18/2009
8	1/18/2009	*	*	1/20/2009
9	1/22/2009	*	*	1/22/2009
10	1/23/2009	*	*	1/23/2009
11	1/24/2009	*	*	1/24/2009
Sheet4

That one formula copied down DOES appear to bring in the multiple instances of each duplicated date.

So, since SMALL already works the way you want, is this the actual problem or are you having trouble grabbing the associated NAMES with these duplicated dates?

 

[barry houdini]

Assuming it is the names required then try like this:

Book2
	A	B	C	D	E	F	G
1	Name	Birthday			Birthday	Name
2	aaronadams	26-Oct-87		1	23-Dec-72	charliebritton
3	alicearchibald	25-Jul-83		2	08-Sep-77	benatkinson
4	benatkinson	08-Sep-77		3	08-Sep-77	callumbarnes
5	callumbarnes	08-Sep-77		4	08-Sep-77	dougburton
6	carolbridges	18-Nov-94		5	11-Mar-78	fionacave
7	charliebritton	23-Dec-72
8	davebrown	09-May-80
9	dougburton	08-Sep-77
10	eddiecarter	26-Nov-83
11	fionacave	11-Mar-78
12
Sheet1

Formula in E2 copied down

=SMALL(B$2:B$11,D2)

formula in F2 confirmed with CTRL+SHIFT+ENTER and copied down

=INDEX(A$2:A$11,SMALL(IF(B$2:B$11=E2,ROW(B$2:B$11)-ROW(B$2)+1),COUNTIF(E$2:E2,E2)))

 

[seanh1016]

great. what about the closest and 2nd closest birthdays to today -- returning the names.

 

[barry houdini]

If you have a list of names and their DOBs then you could do something like this:

birthdays test.xls
	A	B	C	D	E	F	G	H	I	J
1	Name	DOB	daysuntil			Next5birthdaysfrom8March2009
2	KofiAnnan	08Apr1938	31
3	TonyBlair	06May1953	59			Date	Daystogo	Name	Age
4	GordonBrown	20Feb1951	349			10Mar2009	2	EdwardWindsor	45
5	GeorgeWBush	05Jul1946	119			12Mar2009	4	JackKerouac	87
6	barryhoudini	26Oct1964	232			12Mar2009	4	LizaMinnelli	63
7	JackKerouac	12Mar1922	4			15Mar2009	7	SylvesterStallone	65
8	MartinLutherKing	15Jan1929	313			08Apr2009	31	KofiAnnan	71
9	DalaiLama	06Jul1935	120
10	AngelMerkel	17Jul1954	131
11	LizaMinnelli	12Mar1946	4
12	BarackObama	04Aug1961	149
13	NicolasSarkozy	28Jan1955	326
14	SylvesterStallone	15Mar1944	7
15	EdwardWindsor	10Mar1964	2
16
birthdays2

I used a "helper" column in column C. Formula in C2 is as follows copied down:

=DATE(YEAR(B2)+DATEDIF(B2,F$1-1,"Y")+1,MONTH(B2),DAY(B2))-F$1

F1 contains today's date generated by the formula =TODAY() and then F1 is custom formatted as

"Next 5 birthdays from "d mmmm yyyy

F4 and G4 have these formula copied down

=F$1+G4
=SMALL(C$2:C$20,ROWS(F$4:F4))

H4 contains this formula

=INDEX(A$2:A$20,SMALL(IF(C$2:C$20=G4,ROW(C$2:C$20)-ROW(C$2)+1),COUNTIF(F$4:F4,F4)))

confirmed with CTRL+SHIFT+ENTER and copied down

I4 contains this formula copied down

=DATEDIF(VLOOKUP(H4,A$2:B$20,2,0),F$1-1,"y")+1

Note: formulas might not all work correctly until all of them have been added

Does that help at all?

 

[seanh1016]

hell yeah. thank you very much