# Lookup value /="" and closest to a given date

This is a discussion on Lookup value /="" and closest to a given date within the Excel Questions forums, part of the Question Forums category; Hi, I have a table. Dates in Column A, values in Column B. I need to lookup the last data ...

1. ## Lookup value /="" and closest to a given date

Hi,

I have a table. Dates in Column A, values in Column B. I need to lookup the last data from column B for a date given but only if the value is not "" (I populate column B with a formula, and if that formula doesn't find anything, it returns a ""). If there is no data in Column B for the date given, I need the last data which is not "", closest to but less than the date given.

I'm putting this into a cell with a multilayered conditional formula, so I'm just curious what would be the shortest solution to keep the already complex formula as simple as possible.

best,
deL

2. ## Re: Lookup value /="" and closest to a given date

Hi deL

For ex., with you date list in B2:B20 and the input date in D2, in E2:

=MAX(IF(\$B\$2:\$B\$21<=D2,\$B\$2:\$B\$21))

This in an array formula, you have to confirm it with CTRL+SHIFT+ENTER.

If no data exists less than the input data the result is 0, so format the result cell with a message, for ex.:

yyyy-mm-dd;;"N/A"

ABCDEF
1
2 2012-09-07 2012-11-012012-10-17
3 2012-10-08
4 2012-09-21
5
6 2012-10-09
7 2012-09-19
8 2012-09-22
9 2012-12-14
10 2012-09-13
11 2012-09-27
12
13
14 2012-11-17
15 2012-12-06
16 2012-10-17
17 2012-12-09
18 2012-11-17
19
20 2012-12-09
21
[Book1]Sheet1

3. ## Re: Lookup value /="" and closest to a given date

Either I don't get what you wrote, or I should've been more clear. So just to clarify:

If I give 07/01/2012, then I need the value 229.13 returned. If, however I gave 06/26/2012, I need 229.72 to be returned. The Value column is populated with and INDEX/MATCH lookup formula with an IFERROR to give "" if the INDEX/MATCH formula gives an error (I'm not sure if it's important to know, I just thought it wouldn't hurt).

 Date Value 06/25/2012 229.97 06/26/2012 229.72 06/27/2012 228.97 06/28/2012 231.62 06/29/2012 229.13 06/30/2012 07/01/2012 07/02/2012 225.91

4. ## Re: Lookup value /="" and closest to a given date

Originally Posted by deLockloire
Either I don't get what you wrote, or I should've been more clear.
A table always helps ... but I should have read your post with more attention.

See if this is more like it.

In E2:

=IFERROR(LOOKUP(2,1/(\$A\$2:\$A\$20<=D2)/(B2:B20<>""),\$B\$2:\$B\$20),"N/A")

ABCDEF
1
22012-09-071 2012-09-1911
32012-09-082
42012-09-093
52012-09-104
62012-09-115
72012-09-12
82012-09-13
92012-09-14
102012-09-159
112012-09-1610
122012-09-1711
132012-09-18
142012-09-19
152012-09-2014
162012-09-2115
172012-09-2216
182012-09-2317
192012-09-2418
202012-09-2519
21
[Book1]Sheet1

5. ## Re: Lookup value /="" and closest to a given date

Hi,

This one works and does exactly what I need. Thanks.

best,
deL

6. ## Re: Lookup value /="" and closest to a given date

I'm glad it helped. Thanks for the feedback.

7. ## Re: Lookup value /="" and closest to a given date

Originally Posted by deLockloire
Either I don't get what you wrote, or I should've been more clear. So just to clarify:

If I give 07/01/2012, then I need the value 229.13 returned. If, however I gave 06/26/2012, I need 229.72 to be returned. The Value column is populated with and INDEX/MATCH lookup formula with an IFERROR to give "" if the INDEX/MATCH formula gives an error (I'm not sure if it's important to know, I just thought it wouldn't hurt).

[...]
What would be the outcome you want to see if we had:
 Date Value 6/25/2012 229.97 6/26/2012 229.72 6/27/2012 228.97 6/28/2012 231.62 6/29/2012 229.13 6/29/2012 240.75 6/30/2012 7/1/2012 7/2/2012 225.91

for 7/1/2012: 229.13 or 240.75? The latter will be calculated by the formula PGC suggested.

8. ## Re: Lookup value /="" and closest to a given date

Hi,

Fortunately, there is only one value for each day in this table, so the formula suggested works well.
But just for curiosity's sake: why do you ask?

best,
deL

What would be the outcome you want to see if we had:
 Date Value 6/25/2012 229.97 6/26/2012 229.72 6/27/2012 228.97 6/28/2012 231.62 6/29/2012 229.13 6/29/2012 240.75 6/30/2012 7/1/2012 7/2/2012 225.91

for 7/1/2012: 229.13 or 240.75? The latter will be calculated by the formula PGC suggested.

9. ## Re: Lookup value /="" and closest to a given date

Originally Posted by deLockloire
Hi,

Fortunately, there is only one value for each day in this table, so the formula suggested works well.
But just for curiosity's sake: why do you ask?

best,
deL
If multiple values for the same date would occur and you'd need the first of such values, a different formula would be required. Curious about that formula too?

10. ## Re: Lookup value /="" and closest to a given date

If multiple values for the same date would occur and you'd need the first of such values, a different formula would be required. Curious about that formula too?
You bet.

Page 1 of 2 12 Last

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•