# Reverse Hlookup, Possible Offset/Match

#### Humate

##### New Member
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

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Assumptions:

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!

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

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

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.

Replies
2
Views
120
Replies
17
Views
612
Replies
4
Views
249
Replies
7
Views
386
Replies
5
Views
237

1,221,384
Messages
6,159,544
Members
451,571
Latest member
Qwissy

### 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.

### Which adblocker are you using?

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

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