Forum Discussion
Solved
Finding the column name for a value in a table
I have a 3x3 table with a header and two data rows as below. A1=First, B1=Second, C1=Third A2=1, B2=2, C2=3 A3=4, B3=5, C3=6 In cell D1 I'd like to enter a search criteria. For example 5. Th...
Hi Peter,
You need to the Index & Match combination to solve this!
But you need to nest another combination because you want to search the value in cell E1 in two rows!
The formula looks like this:
=IFERROR(INDEX(A1:C3,,MATCH(D1,A2:C2,0)),INDEX(A1:C3,,MATCH(D1,A3:C3,0)))
Hope that helps
That helps, thanks :)
What if I want to expand the table with more columns and rows? Is it possible to make the formula more dynamic?
- The problem we have here is that we are assuming that there will be no repeat values. As soon as there are then the column numbers will be added which gives the wrong result. Enter "38" into A7 for example and you will return Third (1+2).
