vlookup formula help

excel?

Board Regular
Joined
Sep 14, 2004
Messages
129
Office Version
  1. 365
Platform
  1. Windows
Is there a way to convert this formula to sum a range instead of the single column?

=VLOOKUP("Production Total",Input!$A$6:$AK$195,$M$3+1,FALSE)

$M$3 is a number generated based on the selection from a pull down box. If you select a date (10/4/06) from the Pull Down List, cell M$3$ returns "4", so the VLOOKUP Formula then looks in Column 5 and returns the "Production Total" for that day.

What I'm trying to do is have a pull down box by Week instead of by Day. So If I select Week 1, the formula returns the Sum of the "Production Total" from Column 2 thru 8 (10/1/06 - 10/7/06).
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
If the week number is in A2 then you could try

=SUM(VLOOKUP("Production Total",input!$A$6:$AK$195,($A$2-1)*7+{2,3,4,5,6,7,8},0))

confirmed with CTRL+SHIFT+ENTER

...although perhaps a simpler way would be to use

=SUM(OFFSET(INDEX(A6:A195,MATCH("production total",A6:A195,0)),,(A2-1)*7+1,,7))

which only requires ENTER
 

excel?

Board Regular
Joined
Sep 14, 2004
Messages
129
Office Version
  1. 365
Platform
  1. Windows
Thanks for the respoonse - can you tell me what is Cell A2 supposed to be? What is in this cell?

The Pull Down offers the user to choose "Week 1", "Week 2", "Week 3", and "Week 4". If "Week 1" is chosen then in Cell M3 is the number "1".

I do not see how to get your formula to Sum columns 2 thru 8 based on the user selecting "Week 1"
 

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
Sorry, I assumed your week numbers would be determined by a different cell to M3. Just substitute M3 for A2, i.e.

=SUM(OFFSET(INDEX($A$6:$A$195,MATCH("production total",$A$6:$A$195,0)),,($M$3-1)*7+1,,7))
 

Forum statistics

Threads
1,137,337
Messages
5,680,900
Members
419,937
Latest member
Talic

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top