# Excel Formula: What is the equivalent of =MATCH(), but for multiple columns?

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.

• Do you want to do a two-dimension lookup, where you need to find the match in a table, rather than a specific row or column? – nutsch Oct 1 '12 at 19:48
• @nutsch Yes, that is exactly right. Some way to find the address of a cell via lookup in a table. – Gruzzles Oct 2 '12 at 12:04

## 2 Answers

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+ENTER` so that curly braces like { and } appear around the formula in the formula bar

• Well, I have a master data list in columns, and I need to create a summary table of a subset of data including which column each item came from. I ended up doing a match for each column inside a nested if. I only have 6 columns, but If I had more, It would be nightmarishly long. Here's what I did: =IF(ISNA(MATCH(\$A2,Summary!A:A,0)),IF(ISNA(MATCH(\$A2,Summary!B:B,0)),IF(ISNA(MATCH(\$A2,Summary!C:C,0)),IF(ISNA(MATCH(\$A2,Summary!D:D,0)),IF(ISNA(MATCH(\$A2,Summary!E:E,0)),IF(ISNA(MATCH(\$A2,Summary!F:F,0)),"#N/A",Summary!\$F\$1),Summary!\$E\$1),Summary!\$D\$1),Summary!\$C\$1),Summary!\$B\$1),Summary!\$A\$1) – Gruzzles Oct 2 '12 at 11:50
• Sorry, in response to your answer: That is what I need, except my value, "Bob" could be in any row, anywhere in columns A to Z, and I need to return the name/number of that column, so I can do a =RANGE(ColumnVar & "1"), or =CELLS(1,ColumnVar) in order to print the column header. – Gruzzles Oct 2 '12 at 11:57
• You can use a shorter "array formula" - I edited my answer – barry houdini Oct 2 '12 at 12:10
• Brilliant!! Thank you SO much. It worked like a charm. The only thing I added was \$ around the lookup arrays so that they wouldn't increment when I auto-expanded the formula. – Gruzzles Oct 2 '12 at 18:33

A hidden column within the lookup range will do it, enabling you to use MATCH to bring back a sub-array - in my case a 1 row x 5 column subsection of the named range `_Maturity`:

``````=OFFSET(INDEX(_Maturity,MATCH(\$B7&\$C7,INDEX(_Maturity,0,3),0),0),0,3,1,5)
``````

The `_Maturity` array is 8 columns wide with a dynamic row length (using OFFSET and COUNTA).

Column 1 holds the value for B, column 2 for C - these are concatenated in hidden column C (hidden) - hence, the column offset of 3 in the second Index function.

Hope this helps.