Find column letter containing specific data

[markx]

Hello everybody,

I'm looking for a formula (not VBA!) that could give me back the letter of
the column containing some particular data (f. ex. "abc").
If the data is present in more than one column, it could give me back the
letter of the first column that matches the criteria (or of any of them, if
it's easier).

Any help on this would be greatly appreciated!
Thanks in advance,

Mark

 

[Myrna Larson]

Do you want to search one particular row, or all rows?

HLOOKUP and MATCH are the usual worksheet functions, but they require that you
search one row and return either data from that or another row, or the column
number.

If you want to search multiple rows, as you can do with Edit/Find, then you'll
need a VBA macro. And with some earlier versions of Excel that doesn't work
from a worksheet formula. In that case, the macro would have to execute
multiple MATCHs, on on each row.

 

[Jason Morin]

This will cover columns A - Z:

=CHAR(MIN(IF(COUNTIF(INDIRECT(CHAR(ROW(INDIRECT("65:90")))
&":"&CHAR(ROW(INDIRECT("65:90")))),"*abc*"),ROW(INDIRECT
("65:90")))))

Array-entered, meaning press ctrl + shift + enter.

HTH
Jason
Atlanta, GA

 

[Biff]

Hi!

This will return the column NUMBER for the first instance
of "abc".

Assume A1 = abc
The range to search is B1:G5

Entered with the key combo of CTRL,SHIFT,ENTER:

=IF(A1="","",MIN(IF((B1:G5=A1)*(COLUMN(B1:G5)),COLUMN
(B1:G5))))

If "abc" is not present in the search range the formula
will return 0.

Biff

 

[Markx]

Thanks Myrna, Jason and Biff,

.... and sorry for not responding immediately. I was temporarily cut off from
internet connection. As far as MATCH/HLOOKUP functions are concerned, I
don't know them good enough to put them at work. I think I'll try first the
solution proposed by Biff and Jason. In case fo any (unexpected) problems,
I'll get back to you!

Thanks once again for your quick reaction!
Mark