# SumProduct Multiple columns using name for column index Vlookup across multiple sheets

I am trying to sum multiple columns from a table on a separate sheet using vlookup and matching the column index number with a name instead of the column number. I can return a single result but can only seem to sum the product of the columns using the array {3,4,5} etc.

In one tab I have my data (simplified for this example) In another sheet I want to show the totals for Fred and Bob and Betty and Jane for each category. This returns a single result:

``````=IFERROR(VLOOKUP((\$A2&""),'P&L by job'!\$A\$2:\$E\$4,MATCH(\$A\$2,'P&L by job'!\$A\$1:\$E\$1,0),FALSE),0)
``````

This formula will result in the sumproduct:

``````=IFERROR(SUMPRODUCT(VLOOKUP((\$A2&""),'P&L by job'!\$A\$2:\$E\$4{2,3},FALSE)),0)
``````

The issue is when an additional column is added, or the columns are in a different order each time I import the data across, I need to change the column index number in my spreadsheet.

Is there a better approach?

Try peeling out Bob, Fred, Betty and Jane from the column headers.

``````=SUM(INDEX('P&L by job'!\$A:\$Z, MATCH(\$A2, 'P&L by job'!\$A:\$A, 0), MATCH(REPLACE(B\$1, FIND(" and ", B\$1), LEN(B\$1), TEXT(,)), 'P&L by job'!\$1:\$1, 0)),
INDEX('P&L by job'!\$A:\$Z, MATCH(\$A2, 'P&L by job'!\$A:\$A, 0), MATCH(REPLACE(B\$1, 1, FIND(" and ", B\$1)+4, TEXT(,)), 'P&L by job'!\$1:\$1, 0)))
`````` • It's working +1 ☺ – Rajesh S Apr 2 '20 at 10:28
• Thanks Jeeped - I will try this – Wendy Apr 5 '20 at 22:53

Lookup formula wrapped with `SUMPRODUCT` and `nested IF` solves the issue:

Caveat:

Below shown method is best suitable to small data range like 8 to 10 columns, otherwise method/formula suggested by `@Jeeped` is better & a smart move. How it works:

• Insert some helper data in Row 1 (`S1:X1`), you may extend them, if and when required.
• Write combination of names in Row 9 (`S9:U9`), remember you may change the order as your need.
• Formula in `S10`:

`````` =IFERROR(IF(S\$1="A",SUMPRODUCT(VLOOKUP(\$R10,\$R\$3:\$X\$5,{6,7},FALSE)),IF(S\$1="B",SUMPRODUCT(VLOOKUP(\$R10,\$R\$3:\$X\$5,{4,5},FALSE)),IF(S\$1="C",SUMPRODUCT(VLOOKUP(\$R10,\$R\$3:\$X\$5,{2,3},FALSE)),""))),"")
``````

N.B.

• Fill formula across the range.
• You may adjust cell references & order of columns as needed.