Vlookup question

[Desperate]

I have a worksheet with an array of scheduled delivery dates, amounts, etc.
On another worksheet, I have a summary where there is a vlookup for the
delivery date. The problem is that if it is not on the same day, it will
return the last date rather than the next date. Can excel look for the next
date?

 

[Duke Carey]

use Match() with a -1 for the 3rd argument

From the help file for MATCH()

MATCH(lookup_value, lookup_array, [match_type])

[match_type]
1 or omitted
MATCH finds the largest value that is less than or equal to lookup_value.
The values in the lookup_array argument must be placed in ascending order,
for example: ...-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE.

0
MATCH finds the first value that is exactly equal to lookup_value. The
values in the lookup_array argument can be in any order.

-1
MATCH finds the smallest value that is greater than or equal to
lookup_value. The values in the lookup_array argument must be placed in
descending order, for example: TRUE, FALSE, Z-A, ...2, 1, 0, -1, -2, ..., and
so on.

 

[Desperate]

Match just returns the cell number in the array, and not the cell value
itself? I can't get it to work. For example, if the dates are (Jan-21, the
Oct-20) in the array, and todays date is Aug-14 (lookup value) how can I get
the cell to return Oct-20 for next delivery date. If I use the vlookup it
will return Jan-21 everytime.

use Match() with a -1 for the 3rd argument

From the help file for MATCH()

MATCH(lookup_value, lookup_array, [match_type])

[match_type]
1 or omitted
MATCH finds the largest value that is less than or equal to lookup_value.
The values in the lookup_array argument must be placed in ascending order,
for example: ...-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE.

0
MATCH finds the first value that is exactly equal to lookup_value. The
values in the lookup_array argument can be in any order.

-1
MATCH finds the smallest value that is greater than or equal to
lookup_value. The values in the lookup_array argument must be placed in
descending order, for example: TRUE, FALSE, Z-A, ...2, 1, 0, -1, -2, ..., and
so on.

I have a worksheet with an array of scheduled delivery dates, amounts, etc.
On another worksheet, I have a summary where there is a vlookup for the
delivery date. The problem is that if it is not on the same day, it will
return the last date rather than the next date. Can excel look for the next
date?

 

[Duke Carey]

OK, I short-changed you on the answer. You need to use the Match() inside an
INDEX function. Index will return the value that is x number of rows down in
a range. The MATCH() function supplies the x value. So...

=INDEX($B$1:$B$18,MATCH(D1,$A$1:$A$18,-1))

will find the value in column B that corresponds to the date in A1:A18 that
EQUALS or is Later than the date in D1. Assumes that the dates in A1:A18 are
sorted in descending order.

Match just returns the cell number in the array, and not the cell value
itself? I can't get it to work. For example, if the dates are (Jan-21, the
Oct-20) in the array, and todays date is Aug-14 (lookup value) how can I get
the cell to return Oct-20 for next delivery date. If I use the vlookup it
will return Jan-21 everytime.

use Match() with a -1 for the 3rd argument

From the help file for MATCH()

MATCH(lookup_value, lookup_array, [match_type])

[match_type]
1 or omitted
MATCH finds the largest value that is less than or equal to lookup_value.
The values in the lookup_array argument must be placed in ascending order,
for example: ...-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE.

0
MATCH finds the first value that is exactly equal to lookup_value. The
values in the lookup_array argument can be in any order.

-1
MATCH finds the smallest value that is greater than or equal to
lookup_value. The values in the lookup_array argument must be placed in
descending order, for example: TRUE, FALSE, Z-A, ...2, 1, 0, -1, -2, ..., and
so on.

I have a worksheet with an array of scheduled delivery dates, amounts, etc.
On another worksheet, I have a summary where there is a vlookup for the
delivery date. The problem is that if it is not on the same day, it will
return the last date rather than the next date. Can excel look for the next
date?

 

[Desperate]

This works but isn't there a way to keep the dates in ascending order. It
just seems odd to be adding dates to the top of the list rather than the
bottom.

OK, I short-changed you on the answer. You need to use the Match() inside an
INDEX function. Index will return the value that is x number of rows down in
a range. The MATCH() function supplies the x value. So...

=INDEX($B$1:$B$18,MATCH(D1,$A$1:$A$18,-1))

will find the value in column B that corresponds to the date in A1:A18 that
EQUALS or is Later than the date in D1. Assumes that the dates in A1:A18 are
sorted in descending order.

Match just returns the cell number in the array, and not the cell value
itself? I can't get it to work. For example, if the dates are (Jan-21, the
Oct-20) in the array, and todays date is Aug-14 (lookup value) how can I get
the cell to return Oct-20 for next delivery date. If I use the vlookup it
will return Jan-21 everytime.

use Match() with a -1 for the 3rd argument

From the help file for MATCH()

MATCH(lookup_value, lookup_array, [match_type])

[match_type]
1 or omitted
MATCH finds the largest value that is less than or equal to lookup_value.
The values in the lookup_array argument must be placed in ascending order,
for example: ...-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE.

0
MATCH finds the first value that is exactly equal to lookup_value. The
values in the lookup_array argument can be in any order.

-1
MATCH finds the smallest value that is greater than or equal to
lookup_value. The values in the lookup_array argument must be placed in
descending order, for example: TRUE, FALSE, Z-A, ...2, 1, 0, -1, -2, ..., and
so on.


:

I have a worksheet with an array of scheduled delivery dates, amounts, etc.
On another worksheet, I have a summary where there is a vlookup for the
delivery date. The problem is that if it is not on the same day, it will
return the last date rather than the next date. Can excel look for the next
date?