Find column letter containing specific data
 

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

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.


On Thu, 17 Mar 2005 19:41:11 +0100, "markx"
wrote:

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

This will cover columns A - Z:

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

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

HTH
Jason
Atlanta, GA

-----Original Message-----
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


.

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)),COL UMN
(B1:G5))))

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

Biff

-----Original Message-----
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


.

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


"Biff" wrote in message
...
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)),COL UMN
(B1:G5))))

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

Biff

-----Original Message-----
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


.