Lookup data in multiple tables

[Anthony]

I have data organized as:

product name1
Heading 1, heading 2, heading 3, heading 4
row name 1, data, data, data
row name 2, data, data, data
row name 3, data, data, data
row name 4, data, data, data
Total, data, data, data

product name 2
Heading 1, heading 2, heading 3, heading 4
row name 1, data, data, data
row name 2, data, data, data
Total, data, data, data

The issue is that i have many product names (over 25). The data is
always found in the named row "total" and column 4 AFTER finding the
name of the table. I need a formula that basically says, if you find
"product name X" then return the value the row named "total" and found
X columns to the right of that named row.

 

[venkat1926]

I wonder whether I have understood you completely. If there is only one total under each product name is it necessary to find the string product name and then go to the string total. why not straigtway find the strings total and shift the value.

I have assumed x=4

will the following macro help you?

Sub test()
 Dim r As Range, cfind As Range, cfind1 As Range, add As String
 On Error Resume Next
 Set r = ActiveSheet.UsedRange
 Set cfind = r.Cells.Find(what:="total", lookat:=xlWhole)
 add = cfind.Address
 Cells(cfind.Row, "D").Cut Cells(cfind.Row, "H")
 Do
 Set cfind = r.Cells.FindNext(cfind)
 If cfind Is Nothing Or cfind.Address = add Then Exit Do
 Cells(cfind.Row, "D").Cut Cells(cfind.Row, "H")
 Loop
 End Sub

 

[Patrick Molloy]

use the VLOOKUP function

 

[Patrick Molloy]

I misunderstood. sorry

use MATCH to find the row for the product

the use MATCH with OFFSET to get the value of the total row, and finally use
OFFSET with this value to get the result

=OFFSET(A1,MATCH("Total",OFFSET(A1,MATCH(H15,A:A,FALSE),0,1000),FALSE)+MATCH(H15,A:A,FALSE)-1,4)

in this H15 is a product name