microsoft excelworksheet-function
Problem
This formula will return the row number where the value "Bob" appears. Say the value happens to be in $A$10, the formula would return: 10
=MATCH("Bob",A:A,0)
I would like a formula to do a similar thing, except give me the column.
For example, if I search for "Bob" in A:F, I would like to know what column it is in. I thought of something like this:
=INDEX(1:1,MATCH("Bob",A:F,0))
or
=CELL("col",B14)
Where the address, B14, is found with a lookup, index/match, etc.
Let's say you have the following data in 'Sheet 1':
Col A ; Col B ; Col C
1 ; 131 ; 3
2 ; 125 ; 5
3 ; 168 ; 2
4 ; 154 ; 8
5 ; 1365 ; 0
If I understand the requirement of your question correctly, use the following formula in the sheet you want to have your corresponding value appear:
=IF(ISERROR(MATCH(Sheet1!A1,Sheet1!$C$1:$C$5,0)),"VALUE DOES NOT MATCH",Sheet1!B1)
You have a number of different cases. Let's consider one case:
Somewhere in columns A through E there is one and only cell containing 13, return the contents of the cell in column F in the same row.
We will use a "helper" column. In G1 enter:
=COUNTIF(A1:E1,13)
and copy down. This allows us to identify the row:
Now we can use MATCH()/INDEX():
Pick a cell and enter:
=INDEX(F:F,MATCH(1,G:G,0))
If the "rules" change and there could be more than one 13 in a row or several rows containing 13, we would modify the helper column.
EDIT#1:
Based on your update, the first step would be to pull the hard-coded 13 out of the formulas in the "helper" column and put it in its own cell, (say H1). Then you can run different cases simply by changing a single cell.
If you have a large number of cases in a table, you could create a macro to setup each case (update H1) and record the results.
Best Answer
MATCH only works with a single column or row, so you can use a row like 1:1 rather than A:A
=MATCH("Bob",1:1,0)That will give you a number, so if "Bob" is first found in Z1 you'd get 26.....or do you need something different?
Do you have a larger problem of which this is a part?
Edited in response to comments:
This "array formula" will search for A2 anywhere in Summary!A2:Z1000 and return the column header from the first column where that value is found
=INDEX(Summary!A1:Z1,SMALL(IF(Summary!A2:Z1000=A2,COLUMN(Summary!A2:Z1000)-COLUMN(Summary!A2)+1),1))formula needs to be confirmed with
CTRL+SHIFT+ENTERso that curly braces like { and } appear around the formula in the formula bar