return col header when test is true

[clock-smith]

I am using excel 2000.
I am a newbie to excel but an oldie in age so slow on the uptake!

I have tried looking for tips etc but do not know what to look for

An example may help.

if the col headers for cols a, b, c, d are 1950, 1960, 1970, 1980
and the entries in the range a2-d9 are all blanks except for an asterisk in a5 and another in d9

then the answer I would like to get is "1950" in e5 and "1980" in e9

Thanks for your time,

clock-smith

 

[Aladin Akyurek]

Book3
	A	B	C	D	E
1	1950	1960	1970	1980	Result
2
3
4
5	*				1950
6
7
8
9				*	1980
10
Sheet1

E2, copied down:

=IF(COUNTA(A2:D2),LOOKUP(REPT("z",255),A2:D2,$A$1:$D$1),"")

assuming that there are no formulas in A2:D9 that returns "".

 

[chiello]

The following solution should work for you

Cartel1
	A	B	C	D	E	F	G	H	I	J
1	1950	1960	1970	1980
2							Replace		With
3							SE		IF
4							VAL.NON.DISP		ISNA
5	*				1950		INDICE		INDEX
6							CONFRONTA		MATCH
7							;		,
8
9				*	1980
10
11
Foglio1

Post for feedback

 

[clock-smith]

Thanks Aladdin and Chiello for two rapid answers.

I tried Aladins way first and it works fine! Very grateful!

Chiello, I tried your answer as well but had no luck with it, perhaps because I am still using win98se whilst i noted you are on XP

specifically, I got a formula error message and the bit that excel highlighted was the first occurrence of "$A$1:$E$9;" I was using the same very short sample sheet and had cut and pasted the code, then replaced the italian as instructed.

I am happy with Aladins way, but thought I should let you know.

Thanks

Clock-smith

 

[chiello]

Just for information, did you replace semicolon ";" with comma "," ?
Try and the formula should work!!

 

[clock-smith]

Just for information, did you replace semicolon ";" with comma "," ?
Try and the formula should work!!

Thanks Chiello, your assistance is much appreciated

Barrie