Try this:
=IF(OFFSET(B2,0,MATCH($A$10,$C$1:$E$1))=0,"",OFFSE T(B2,0,MATCH($A$10,$C$1:$E$1)))

"tjvols" wrote:

A B C D E
1 Bob Sam Tom
2 =if(c3=0," ",row()) 10 0 4
3 0 5 4
4 4 5 8 0
5 5 4 0 0


I want to be able enter "Bob" in A10 and have the reference in the
formula in B2 look to A10 to get the column needed for the
calculation. If I change to A10 to "Sam" the formula would change to
=if(d3=0," ",row(). I also need to be able to copy this formula down
into B3, B4 & B5.

Any help would be greatly appreciated.
Thanks.