vlookup with a range result

[tk]

Here is my layout:

Cell d4 is the day of month manually entered
In columns DN2 through ER2 I have 1 - 31 for 31 days in the month
I have revenue numbers in DN4 through ER4
I want to match what I put in D4 for day of month with the day in row 2 (DN
to ER) and return a month to date value.

So if today is the 10th I would put 10 in D4. I want to return a value for
DN4W4.
If it were the 5th of the month I would put 5 in D4 and want the sum of
DN4R4.

Any suggestions?

 

[T. Valko]

Try this:

=SUM(DN4:INDEX(DN4:ER4,D4))

Note: if D4 is empty the entire range will be calculated.

 

[Bob Umlas, Excel MVP]

=SUM(OFFSET(DN4,0,0,1,D4))

 

[driller]

just guessing here
try
=sumproduct(--(d4=<DN2:ER2),--(DN4:ER4))
this may suit on whatever arrangement of Day# 1-31 DN2:ER2

 

[tk]

This worked perfectly!!!! I will have to look at index to understand.

THANK YOU

 

[T. Valko]

You're welcome. Thanks for the feedback!