# lookup a value and sum a corresponding dynamic range

#### doug firr

##### Board Regular
Trying to create a nested formula and am struggling. What I need to do is find a value in a range and then sum the corresponding row. The row grows each month.
This is the data table that needs to be looked up
 july august september october november tim 123 456 789 101 212 david 987 654 321 123 456 john 123 456 789 987 765 peter 432 123 456 789 101

<tbody>
</tbody>

I need a formula that looks up a given name and then sums the total for that row. The row grows each month and I'd like to have this automatically update when the new date column is added.

Any pointers?

### Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Assuming names in A2:A5 and data in B2:Z5 try this formula to sum data for "john" (assuming that will be only 1 row)

=SUM(INDEX(B2:Z5,MATCH("john",A2:B5,0),0))

That will sum data up to column Z, you could extend that further if required. Lookup value "john" could be a cell reference if required

Hi,
let Your Data be in Columns A:F, exactly in A1:F5. If in H2 is a given name, in I2 the following formula is enough:
Code:
Best regards.

Replies
1
Views
147
Replies
3
Views
812
Replies
3
Views
252
Replies
3
Views
392
Replies
8
Views
337

1,203,434
Messages
6,055,346
Members
444,781
Latest member
rishivar

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back