ShadowIT
asked on
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
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
ASKER
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
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
ASKER
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))
=INDEX($A$1:$E$1,1, MATCH(1,$A2:$E2,0))
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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