vlookup with a range result
 

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
DN4:DW4.
If it were the 5th of the month I would put 5 in D4 and want the sum of
DN4:DR4.

Any suggestions?

vlookup with a range result
 

Try this:

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

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

--
Biff
Microsoft Excel MVP


"tk" wrote in message
...
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
DN4:DW4.
If it were the 5th of the month I would put 5 in D4 and want the sum of
DN4:DR4.

Any suggestions?

vlookup with a range result
 

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


"tk" wrote:

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
DN4:DW4.
If it were the 5th of the month I would put 5 in D4 and want the sum of
DN4:DR4.

Any suggestions?

vlookup with a range result
 

just guessing here
try
=sumproduct(--(d4=<DN2:ER2),--(DN4:ER4))
this may suit on whatever arrangement of Day# 1-31 DN2:ER2
that may be sorted at any way you like<

--
regards,



"tk" wrote:

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
DN4:DW4.
If it were the 5th of the month I would put 5 in D4 and want the sum of
DN4:DR4.

Any suggestions?

vlookup with a range result
 

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

THANK YOU

"T. Valko" wrote:

Try this:

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

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

--
Biff
Microsoft Excel MVP


"tk" wrote in message
...
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
DN4:DW4.
If it were the 5th of the month I would put 5 in D4 and want the sum of
DN4:DR4.

Any suggestions?

vlookup with a range result
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"tk" wrote in message
...
This worked perfectly!!!! I will have to look at index to understand.

THANK YOU

"T. Valko" wrote:

Try this:

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

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

--
Biff
Microsoft Excel MVP


"tk" wrote in message
...
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
DN4:DW4.
If it were the 5th of the month I would put 5 in D4 and want the sum of
DN4:DR4.

Any suggestions?