Returning a value for a non empty cell in a column

[exflyer1996]

Ok, I have a name in one of 9 columns (B-J). Those columns are have headings 1-9. In column A, I would like to return the heading for whichever column has the name in it. A name appears in only 1 column of each row, and it could be in any of the 9 columns. If the name moves to a different column in the future, I want column A to update accordingly.

EXAMPLE: If "SMITH" is in cell D2, I want A2 to return the value 3.

Any suggestions?

I guess the formula could read the heading number, or how many columns away from column A the name appears.

 

[Michael M]

Hi
Maybe something like

=LOOKUP("Smith",B2:J2,COLUMN(B2:J2))

If you wanted to make it a little more dynamic, try replacing the "Smith" with a cell reference "K1", and put the required name in that cell

=LOOKUP(K1,B2:J2,COLUMN(B2:J2))

copy down as required

 

[sheetspread]

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1		1	2	3	4	5	6	7	8	9
2	1	Red
3	3			Orange
4	5					Yellow
5	4				Green
6	7							Blue
7	2		Indigo
8	9									Violet
Sheet2

 

[Michael M]

Sorry, made a typo.
The formula needs a -1 on the end to give the correct column

=LOOKUP("Smith",B2:J2,COLUMN(B2:J2))-1

=LOOKUP(K1,B2:J2,COLUMN(B2:J2))-1

 

[exflyer1996]

thank you sheetspread. worked great.

 

[T. Valko]

Ok, I have a name in one of 9 columns (B-J). Those columns are have headings 1-9. In column A, I would like to return the heading for whichever column has the name in it. A name appears in only 1 column of each row, and it could be in any of the 9 columns. If the name moves to a different column in the future, I want column A to update accordingly.

EXAMPLE: If "SMITH" is in cell D2, I want A2 to return the value 3.

Any suggestions?

I guess the formula could read the heading number, or how many columns away from column A the name appears.

Here's another one...

Book1
	A	B	C	D	E	F	G	H	I	J
1	_	1	2	3	4	5	6	7	8	9
2	3	_	_	Smith	_	_	_	_	_	_
3	6	_	_	_	_	_	Day	_	_	_
4	8	_	_	_	_	_	_	_	Lee	_
Sheet1

This formula entered in A2 and copied down:

=MATCH("*",B2:J2,0)