Lookup a header in a table - Excel 2003

Closed
Report
-
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
-
So HLOOKUP doesn't seem to work as well as I hoped on this one,

I would like to return the text on the top of a table column by first finding some matching text within the table. Sort of like looking across the horizontal for a value and then returning the most vertical value into the formulated cell.

I can't link an example workbook here since my fileshare website is down at the moment.

'Sheet1'C3 contains the word "Five", 'Sheet2'D8 contains the word "Five" also. above this value, in 'Sheet2'D2 is the Letter "A". So how would I get a cell on sheet 1 to show me an "A" by matching the two cells C3 and D8, then looking vertically to row 2 and returning what is there?

1 reply

Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
803
I am not clear what you want.

try this macro and see whether you get what you want.

Sub test()
Dim x As String, cfind As Range, y As String
Dim cfind1 As Range
x = Worksheets("sheet1").Range("C3")
 Worksheets("sheet2").Activate

Set cfind = Columns("d:d").Cells.Find(what:=x, lookat:=xlWhole)
y = Cells(2, cfind.Column)

Set cfind1 = Worksheets("sheet1").Cells.Find(what:=y, lookat:=xlWhole)
MsgBox cfind1.Address

End Sub