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.

 

[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