Reverse Hlookup, Possible Offset/Match

[Humate]

Could anyone help me with a formula please? I am trying to find a value in a table, then return the heading of that column.

So for instance, I want to find the number 22 on row number 10, then return the first row of the column that number is found in (Name of the Column).

I have made attempts with offset an match unsuccessfully so far.

Thanks
Humate

 

[Domenic]

Assumptions:

B1:E1 contains the column headers

A2:A10 contains the row headers

B2:E10 contains the data

A15 contins the number of interest, such as 22


Formula:

=IF(COUNTIF(B2:E10,A15),INDEX(B1:E1,MIN(IF(B2:E10=A15,COLUMN(B2:E10)-COLUMN(B2)+1))),"")

...confirmed with CONTROL+SHIFT+ENTER. Note that if B8 and E4 both contain 22, the column header for Column B will be returned.

Hope this helps!

 

[NBVC]

Try:

=INDEX($A$1:$C$1,SUMPRODUCT((ISNUMBER(SEARCH(K1,$A$2:$C$6)))*(COLUMN($A$1:$C$1)-COLUMN($A$1)+1)))

Where A1:C1 contain the column headers

And A2:C6 is the full table

 

[Humate]

Thanks both that works great. I have found that it does bring the problem Dominic described though, if 22 is duplicated then it returns the first column header again. Is there anyway around this? In this case Column headers are names, so it is returning the same on several occasions.

Cheers
Humate

 

[NBVC]

Probably easier to modify, Domenic's formula as follows keeping the same assumptions he determined in his post.


=IF(COLUMNS($B$1:B1)>COUNTIF($B$2:$E$10,$A$15),"",INDEX($B$1:$E$1,SMALL(IF($B$2:$E$10=$A$15,COLUMN($B$1:$E$1)-COLUMN($B$1)+1),COLUMNS($B$1:B1))))

This is again confirmed with CTRL+SHIFT+ENTER not just ENTER

and then dragged over to the right the same number of columns you have in your original table.