Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
markx
 
Posts: n/a
Default 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


  #2   Report Post  
Myrna Larson
 
Posts: n/a
Default

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


  #3   Report Post  
Jason Morin
 
Posts: n/a
Default

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


.

  #4   Report Post  
Biff
 
Posts: n/a
Default

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


.

  #5   Report Post  
Markx
 
Posts: n/a
Default

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


.



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I find a column entry closest to a particular value feman007 Excel Worksheet Functions 2 March 9th 05 04:48 PM
merge data from multiple columns to single column triggerthehorse Excel Worksheet Functions 2 January 17th 05 08:19 PM
Matching data in one column to another excel idiot Excel Worksheet Functions 1 January 14th 05 03:15 PM
filling a forumla down a column from data across a row Doug Excel Worksheet Functions 2 December 9th 04 09:55 PM
getting data from 2 excel sheets automatically pinar Excel Worksheet Functions 0 November 9th 04 12:47 PM


All times are GMT +1. The time now is 09:29 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"