Reverse Hlookup, Possible Offset/Match

Humate

New Member
Joined
Feb 13, 2006
Messages
43
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
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,696
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

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
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

New Member
Joined
Feb 13, 2006
Messages
43
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

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
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.
 

Forum statistics

Threads
1,136,272
Messages
5,674,756
Members
419,525
Latest member
helensesc

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top