Lookup help

[The Idea Dude]

Hi All,

I have data as follows

Bib # Name Finish position
1 Bob Road 3
2 Bill White 4
3 Simone Crawley 1
4 Peter Parker 1


In another sheet I have a column (Say A4:A104) for bib #

I would like to pull The bib numbers across so that in A4 is Bib number 3, A5 is bib #4 (because they finished at the same time) A6 is bib #1 and A7 is bib # 2 etc. Needs to be done with formulas

Many thanks

 

[sheetspread]

Excel 2010
	A	B	C	D	E
1	Bib#	Name	Finishposition
2	1	Bob Road	3		Simone Crawley
3	2	Bill White	4		Peter Parker
4	3	Simone Crawley	1		Bob Road
5	4	Peter Parker	1		Bill White
Source (2)

Cell Formulas
Range	Formula
E2:E5	{=INDEX($B$2:$B$5,MATCH(SMALL($C$2:$C$5+ROW(C2:C5)/9999,ROW()-1),RANK(C2:C5,C2:C5,1)+ROW(A2:A5)/9999,0))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Aladin Akyurek]

@ The Idea Dude

Try to post the output you want from the data you posted. Your verbal description is a bit unnerving.

 

[The Idea Dude]

@ The Idea Dude

Try to post the output you want from the data you posted. Your verbal description is a bit unnerving.

Hi Aladin,

Sorry about that.

Thanks sheetspread for making it look nice

So essentially the output Aladin will be column E from sheetspreads table except it would show bib # instead of name and I need to be able to drag the formula downwards, so in the example above, from E2 to E5

Oh, and in the example above columns A, B and C are on 1 sheet and the formula I am looking for will go in another sheet, in case that matters

Hope that helps

 

[Aladin Akyurek]

Book1
	A	B	C
1	Bib#	Name	Finishposition
2	1	Bob Road	3
3	2	Bill White	4
4	3	Simone Crawley	1
5	4	Peter Parker	1
Sheet1

Book1
	A	B
1	bib #	smallest to largest
2	3	1
3	4	1
4	1	3
5	2	4
Sheet2

In A2 of Sheet2 control+shift+enter, not just enter, and copy down:

=IF($B2="","",INDEX(Sheet1!$A$2:$A$5,SMALL(IF(Sheet1!$C$2:$C$5=$B2,ROW($B$2:$B$5)-ROW($B$2)+1),COUNTIFS($B$2:B2,B2))))

In B2 of Sheet2 control+shift+enter, not just enter, and copy down:

=INDEX(SMALL(Sheet1!$C$2:$C$5,ROW(Sheet1!$C$2:$C$5)-ROW(Sheet1!$C$2)+1),ROWS(B$2:B2))

 

[The Idea Dude]

You sir are a legend!!

 

[Aladin Akyurek]

@ The Idea Dude

Glad to help.