# return col header when test is true

#### clock-smith

##### New Member
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

clock-smith

### Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Book3
ABCDE
11950196019701980Result
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 "".

The following solution should work for you
Cartel1
ABCDEFGHIJ
11950196019701980
2 ReplaceWith
3 SEIF
4 VAL.NON.DISPISNA
5*1950INDICEINDEX
6 CONFRONTAMATCH
7 ;,
8
9*1980
10
11
Foglio1

Post for feedback

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

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

chiello said:
Just for information, did you replace semicolon ";" with comma "," ?
Try and the formula should work!!
Thanks Chiello, your assistance is much appreciated

Barrie

Replies
1
Views
734
Replies
3
Views
369
Replies
3
Views
861
Replies
0
Views
390
Replies
5
Views
396

1,203,530
Messages
6,055,935
Members
444,837
Latest member
TheBams

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back