Lookup Date equal to or smaller than specified Date

[NiugeS]

Hi all,

I'm looking for assistance working out a formula that will give me a value based on looking up the last date entry equal to or before a date the user will put in. Looking at the below, if the user


Example: User enters 02/01/2017 and result should be 4. If user enters 10/01/2017 result is 7. But if user enters 12/01/2017, it knows the last date closest to 12/01/2017 is 10/01/2017 and the answer is 7.
A B
01/01/2017 1
02/01/2017 2
02/01/2017 3
02/01/2017 4
05/01/2017 5
06/01/2017 6
10/01/2017 7

Hope that makes sense.

Thanks,
NigueS

[63falcondude]

With your dates in A1:A7, your numbers in B1:B7, and your lookup date in D1, try this:

=LOOKUP(D1,A1:A7,B1:B7)

[NiugeS]

Thank you 63falcondude,

Is there a formula that would work if the dates weren't in ascending order? I.e. all mixed up?

THanks

[Jonmo1]

It will be less efficient, but you can use

=LOOKUP(2,1/(A1:A7<=D1),B1:B7)

[63falcondude]

Try this:

=LOOKUP(2,1/(A$1:A$7=MAX(IF(A$1:A$7<=D1,A$1:A$7))),B$1:B$7) Ctrl Shift Enter

[NiugeS]

It will be less efficient, but you can use

=LOOKUP(2,1/(A1:A7<=D1),B1:B7)

Apologies as I’m still learning these formulas by why would this be less efficient?

[63falcondude]

Jonmo, would your formula work for 10/1/2017 and 12/1/2017?

I believe that they should both result in 7 but your formula is returning 6.

[Jonmo1]

This formula
=LOOKUP(D1,A1:A7,B1:B7)
Does a binary lookup, that is very efficient.
It realistically only has to actually look at a very small fraction of the cells in the range

Doing this
=LOOKUP(2,1/(A1:A7<=D1),B1:B7)

This makes it look at every single cell in the range.
Because it has to do a true/false expression (is the value in A <= D1), Then do a math operation on it as well.

It's much more work.

In this small sample range it won't be noticeable.
But if your actual dataset is much larger than the sample, then you may notice the difference.

[Jonmo1]

@63falcondude
I get 7 for both.

make sure you have <= not just <

[63falcondude]

With the dates not in order, I get a different result.

See attachment.

[Jonmo1]

I see...

My formula is looking at the last date entered in physical order of the sheet (furthest down column A)
Yours is looking at the last date entered chronologically

The original post was ambiguous on the definition of 'last date entered'

[NiugeS]

Try this:

=LOOKUP(2,1/(A$1:A$7=MAX(IF(A$1:A$7<=D1,A$1:A$7))),B$1:B$7) Ctrl Shift Enter

63falcondude.. thank you.. that works perfectly.. although I wish i new what it all meant

[63falcondude]

You're welcome. Happy to help.

I would be happy to explain how the formula works to you if you are interested.

If that solved your question, please mark this thread as SOLVED.

Almost forgot, welcome to the forum.

[NiugeS]

Thanks for all your hellp..

Unfortunately.... I've moved the formula to an existing spreadsheet and not getting the desired result. I have the formula in the first sheet and all the dates and amounts in a second sheet. The 3rd column
in the 2nd sheet is a running total and when there is no entry in the first two columns, it displays the last total in the 3rd column for several rows. When I enter a date that is before the earliest date,
it should come back with a #N/A but it comes back with the last total. This does not happen on the sample sheet i produced above.

Is there anywhere I can host the file as an example?

[63falcondude]

To attach an Excel workbook, go to Go Advanced > Manage Attachments > Choose file > Upload > Close this window.

Make sure that this workbook is representative of your actual workbook. Include the desired results from the formula/s (manually entered).

[NiugeS]

63falcondude.. apologies but i'm looking everywhere.. but can't seem to find a heading for Advanced... ????

[NiugeS]

63falcondude.. apologies but i'm looking everywhere.. but can't seem to find a heading for Advanced... ????

Found it!!! Trying to upload now.

[NiugeS]

I attached the file.

[63falcondude]

Try this:

=IF(F7<MIN(Bank!A3:A300),NA(),LOOKUP(2,1/(Bank!A3:A300=MAX(IF(Bank!A3:A300<=F7,Bank!A3:A300))),Bank!I3:I300))

Adjustments to the previous formula have been marked in red.

[NiugeS]

Thank you very much! That works perfectly! I understand what you added but still not sure on the actual lookup...

Thank you so much for your help!

[63falcondude]

You're welcome. Basically, the LOOKUP function [ LOOKUP(2,1/... ] returns the last value in column I that meets a certain criteria.

That criteria is: Bank!A3:A300=MAX(IF(Bank!A3:A300<=F7,Bank!A3:A300))

So it checks the last value in the range A3:A300 that is {=MAX(IF(Bank!A3:A300<=F7,Bank!A3:A300))} and returns the corresponding value (same row) in column I.

The MAX IF array formula finds the maximum value of A3:A300 that is less than or equal to the value in F7.

All dates have a numerical value associated with them (the number of days away from 1/0/1900) so the MAX IF formula finds the most recent date that is less than or equal to the date in F7.

[NiugeS]

Thanks 63falcondude...

Using the same spreadsheet and criteria, the above works perfectly at providing the value for the specified date. I now realise i will also need a formula to return a value when the specified date is first found (rather than last time as above). I've tried removing 2,1 etc.. but not getting the result. Could you point me in the right direction?

Thanks again..

[NiugeS]

Thanks 63falcondude...

Using the same spreadsheet and criteria, the above works perfectly at providing the value for the specified date. I now realise i will also need a formula to return a value when the specified date is first found (rather than last time as above). I've tried removing 2,1 etc.. but not getting the result. Could you point me in the right direction?

Thanks again..

To ensure i've explained myself correctly, if a date of 01/01/2017 is entered, the formula will first look for 01/01/2017 and return the first value for that date. If there is no entry for 01/01/2017, it will than look for the next date etc.