Search code examples
excelexcel-formulavlookupsumifs

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

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

Solution

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