Sign up ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.


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.

share|improve this question
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

1 Answer 1

up vote 5 down vote accepted

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

share|improve this answer
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(MAT‌​CH($A2,Summary!C:C,0)),IF(ISNA(MATCH($A2,Summary!D:D,0)),IF(ISNA(MATCH($A2,Summar‌​y!E:E,0)),IF(ISNA(MATCH($A2,Summary!F:F,0)),"#N/A",Summary!$F$1),Summary!$E$1),Su‌​mmary!$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

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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