Sports Example --- return 1st, 2nd, etc. non-zero values in a sequence of rows

Hey all,

My first time to the forum, and I had considered myself a bit of an Excel expert, but apparently I am not after seeing all of the outstanding contributions here. Can anyone help me with the following formula I need to write?

Basically I have a sequence of rows each containing columns ten left-to-right columns of values. In each row, five of the columns contain non-zero numbers (but they could easily be check marks or names of people). What I need to do is return either the column number or the actual value of those five non-zero numbers in an adjacent set of columns.

Imagine the reference data is a string indicating which of 10 possible players is currently on the floor in a game of basketball (ie five players on the court at all times, remaining five are on the bench). And so there are different five-person permutations, and I will pluck out the unique permutations to look at which lineup combinations scored the best, defended the best, etc.

I have played with SMALL/ROW and INDEX/MATCH to no avail in this particular case, as most people that I have seen try to solve this problem had their data oriented vertically as opposed to horizontally (I do not unfortunately have the option to flip my data).

MUCHOS GRACIAS to anyone that can help, have been banging my head against the wall on this one for several hours.

-AH

Try

=IFERROR(INDEX($E5:$N5,SMALL(IF($E5:$N5>0,COLUMN($E$4:$N$4)-COLUMN($E$4)+1,""),COLUMNS($A:A))),"")

Enter with Ctrl+Shift+Enter

Copy across and down