Finding "nth" match

[mcorydon]

I have been working on this formula for a while and cannot find a solution without having a super long and complex nested if statement. For the below table, I want to be able to choose a name (Bernard for example) and have a formula tell me the column name of the first cell with a value greater than 0 (Orange). I then want to have a formula next to that one that pulls the second value, then third, then forth (if applicable). Can anyone think of what formula will accomplish this (ideally not a ctrl-shift-enter)

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]red[/TD]
[TD]orange[/TD]
[TD]yellow[/TD]
[TD]green[/TD]
[/TR]
[TR]
[TD]andy[/TD]
[TD]10[/TD]
[TD]20[/TD]
[TD]50[/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]bernard[/TD]
[TD][/TD]
[TD]30[/TD]
[TD]40[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Carl[/TD]
[TD]50[/TD]
[TD][/TD]
[TD][/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]Daphne[/TD]
[TD][/TD]
[TD][/TD]
[TD]100[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

The answer should look like this

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]first[/TD]
[TD]second[/TD]
[TD]third[/TD]
[TD]fourth[/TD]
[/TR]
[TR]
[TD]andy[/TD]
[TD]red[/TD]
[TD]orange[/TD]
[TD]yellow[/TD]
[TD]green[/TD]
[/TR]
[TR]
[TD]bernard[/TD]
[TD]orange[/TD]
[TD]yellow[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]carl[/TD]
[TD]red[/TD]
[TD]green[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]daphne[/TD]
[TD]yellow[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Thank You (I'm almost certain Index is needed()

 

[wideboydixon]

Book1
	A	B	C	D	E	F	G	H	I	J	K
1		red	orange	yellow	green			first	second	third	fourth
2	andy	10	20	50	50		daphne	yellow
3	bernard		30	40			carl	red	green
4	carl	50			10		bernard	orange	yellow
5	daphne			100			andy	red	orange	yellow	green
Sheet1

Cell Formulas
Range	Formula
H2	{=IFERROR(INDEX($B$1:$E$1,SMALL(IF(INDEX($B$2:$E$5,MATCH($G2,$A$2:$A$5,0),)>0,COLUMN(INDEX($B$2:$E$5,MATCH($G2,$A$2:$A$5,0),))-COLUMN($B$2)+1),COLUMNS($H2:H2))),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

Copy H2 formula across and down as necessary.

WBD

 

[Peter_SSs]

If you'd prefer, here is a similar one but does not require the Ctrl+Shift+Enter confirmation.

Excel Workbook
	A	B	C	D	E
1		red	orange	yellow	green
2	andy	10	20	50	50
3	bernard		30	40
4	carl	50			10
5	daphne			100
6
7
8		first	second	third	fourth
9	daphne	yellow
10	carl	red	green
11	bernard	orange	yellow
12	andy	red	orange	yellow	green
mcorydon

 

[mcorydon]

Wow thats is brilliant! I need to learn how to use the Aggregate function ASAP

 

[Peter_SSs]

I need to learn how to use the Aggregate function ASAP

It can be very handy.

WBD's suggestion without AGGREGATE also works though.