Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I find a column entry closest to a particular value | Excel Worksheet Functions | |||
merge data from multiple columns to single column | Excel Worksheet Functions | |||
Matching data in one column to another | Excel Worksheet Functions | |||
filling a forumla down a column from data across a row | Excel Worksheet Functions | |||
getting data from 2 excel sheets automatically | Excel Worksheet Functions |