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


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:




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

MATCH only works with a single column or row, so you can use a row like 1:1 rather than A:A


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


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:


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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.