Display Column First Row Header when Matched

Hello Experts,
 
I need an Excel 2010 formula that will display the first header row when a match is found within an array based on a value:
 
Value        Result from first row
Oakland = City
Bob = Name

ID   Name  City
1      Joe      Houston
2      Bob     New York
3      Jill        Oakland
ShadowITAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Glenn RayExcel VBA DeveloperCommented:
I'm going to re-state your question; please confirm that this is correct.

A table exists with data.  A user enters an arbitrary value.  A formula is desired that will identify the type of information as listed in a header row of the table.

For example, if a table contains three columns of data, with the header row containing:
A1:  "ID"
B1: "Name"
C1: "City"

with values below being:
A2: 1
B2: "Joe"
C2: "Houston"
A3: 2
B3: "Bob"
C3: "New York"
A4: 3
B4: "Jill"
C4: "Oakland"

and the user enters the value "Oakland" in a cell, the formula will return the value "City"
Likewise, if the user enters the value "Bob", the formula will return the value "Name"

1) What if a value occurs in more than one column?  For example, "Austin" could be a person's name or the name of a city.
2) What do you want to see if there is no match?


-Glenn
0
ShadowITAuthor Commented:
Yes, that's right

1) What if a value occurs in more than one column?  For example, "Austin" could be a person's name or the name of a city.

Just the first it encounters it fine

 2) What do you want to see if there is no match?

N/A..zero...no match found...doesn't really matter to me
0
ShadowITAuthor Commented:
this is close but only works if I specify a specific row and I need this to work for an array:

=INDEX($A$1:$E$1,1, MATCH(1,$A2:$E2,0))
0
Glenn RayExcel VBA DeveloperCommented:
Shadow,

I'm close to a solution, but am trying to avoid either nested IF statements (an approach seen here in EE) or a helper row.  Both cases work (I have a workbook with both types), but am trying to see if I can use an array function instead.

-Glenn
0
Glenn RayExcel VBA DeveloperCommented:
Shadow,

Sorry for the long delay in getting a solution to you, but I did figure it out.  Assuming the value to search for is in G2, the formula to return the header row lable (field name) would be:
=IFERROR(INDEX($A$1:$E$1,1,SUMPRODUCT(($A$1:$E$20=$G$2)*(COLUMN($A$1:$E$20)))/COUNTIF($A$1:$E$20,$G$2)),"Not Found")

This is only searching down to row 20; you'd want to change that value accordingly.  Example workbook attached.

-Glenn
EE-IdentifyColumn.xlsx
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.