Find cell in array that contains partial text and use column # of cell to lookup value

[chuckyfang]

Hi, I have data with headers over the columns. There are cells in the data which contain an asterisk (*) and I would like to find all of these cells and return the headers of the columns which contains those cells.

Please reference sample.

[Ace_XL]

In a separate row use this array formula in Column A

=IF(SUM(LEN(A3:A7)-LEN(SUBSTITUTE(A3:A7,"*","")))>0,A2,"")

Confirm with Ctrl+Shift+Enter and not just Enter

Copy across

[chuckyfang]

That's beautiful. thanks, Ace.