Best formula to use for summing multiple column returns 1 row down from search value

0
votes

I need to copy over data from one worksheet to a master worksheet. I do this with a new created worksheet every week. The new worksheets I create every week use the same cell layout so I would need to use a formula that I can copy and paste on the master worksheet.

The values I need to return and sum are 1 row below the lookup_value that I would normally use in Vlookup; they are also 8 columns across.

lookup_value is in cell A138, Values to return and sum are in cells H139:P139. These are the same cells every worksheet.

I've tried to use variances of Sum(Vlookup($H$139:$P$139,{8,9,10,11,12,13,14,15,16} & Index(Match but continue to get #REF! or #Value!

what formula structure can I use to lookup_value and return 1 row below and sum columns H - P? enter image description here

Image 1 of "Master Worksheet" formula is located in cell I1996 under "Qty in Transit" This row is in reference to Part # A03781402 shown on the right.

Image 2 of "Worksheet 1" You can see where Part # A03781402 is referenced in cell A138. Now I just need to grab the Qty from Cells H139:P139, sum & return to "Master Worksheet" Cell I1996

enter image description here

3
excelexcel-formulavlookupsumifs
The formula is deriving from "Master Worksheet". I am trying to lookup values from cells H139 - P139 based on Lookup_Value in cell A138 in "Worksheet 1"; sum the values and return the total value into another cell in "Master Worksheet" – Matt Picca
There isn't value in cell A138 its more of a reference to the cells below it. Maybe im just stuck in the vlookup logic. – Matt Picca
I will attached images to the main post, maybe that will help – Matt Picca
Where is the lookup value and in which range(column?) is it searched for? I know the rest. – VBasic2008
In "Master Worksheet" Lookup_value is in Column R (R1996). In Worksheet 1, Lookup_value is in column A (A138) – Matt Picca

3 Answers

0
votes

You need to use something like a SUMIF:

=SUMIF($H$138:$P$138,$A$138,$H$139:$P$139)

img1

0
votes

If the lookup_value is in Z1 and the lookup_array ColumnA, then please try:

=SUM(INDEX(H:P,1+MATCH(Z1,A:A,0),))
0
votes

Formula for 'Master Worksheet'!I1996 to sum 'Worksheet 1'!H:P one row below the row that matches 'Master Worksheet'!R1996 in 'Worksheet 1'!A:A.

=sum(index('Worksheet 1'!H:P, match(R1996, 'Worksheet 1'!A:A, 0)+1, 0))

The 0 as the column_num in INDEX is important; it means all columns within the indexed range.