Vlookup a value but returning the closest date match (before the date)

[bakarken]

Hi guys

Absolutely amazed at past help from here, and wonder if one of you can help me with a problem I'm having.

In Sheet1, I have:
Column A: Dates
Column B: Amounts

In cell C1, I'd like to type a specific date, and in Cell D1, I'd like to type a specific amount.
In cell E1, I'd like a formula that will return the closest date AT OR BEFORE the date in C1 (from the dates in column A), but the amount (in column B) must match exactly to D1.

Ideally, the first result would be anything same day, but if that does not exist, the date will be before D1 date.

I've attached an example.

Possible it would say 'No result' or something if it cannot find the amount at all in the list

Any help would be really appreciated!

 

[Momentman]

If you use Excel 365, then something like the below can work

Sample.xlsx
	A	B	C	D	E
1	01/01/2023	50	06/01/2023	50	05-Jan-23
2	02/01/2023	100
3	05/01/2023	50
4	10/01/2023	11
5	15/01/2023	50
6	31/01/2023	50
Sheet2

Cell Formulas
Range	Formula
E1	E1	=LET(a,FILTER(A1:A6,B1:B6=D1,""), XLOOKUP(C1,a,a,"Not found",-1))

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[bakarken]

Excel 2019

 

[Peter_SSs]

Excel 2019

Thanks - but please put that in your account details as requested so that it is always available to helpers and we don't have to ask you again next time.

Try a formula like this.

23 10 19.xlsm
	A	B	C	D	E
1	1/01/2023	50	6/01/2023	50	5/01/2023
2	2/01/2023	100
3	5/01/2023	50
4	10/01/2023	11
5	15/01/2023	50
6	31/01/2023	50
Find Date

Cell Formulas
Range	Formula
E1	E1	=IFERROR(AGGREGATE(14,6,A1:A6/((B1:B6=D1)*(A1:A6<=C1)),1),"Not found")

 

[bakarken]

Thanks - but please put that in your account details as requested so that it is always available to helpers and we don't have to ask you again next time.

Try a formula like this.

23 10 19.xlsm
	A	B	C	D	E
1	1/01/2023	50	6/01/2023	50	5/01/2023
2	2/01/2023	100
3	5/01/2023	50
4	10/01/2023	11
5	15/01/2023	50
6	31/01/2023	50
Find Date

Cell Formulas
Range	Formula
E1	E1	=IFERROR(AGGREGATE(14,6,A1:A6/((B1:B6=D1)*(A1:A6<=C1)),1),"Not found")

Thank you so much! This seems to work You have helped me a lot, really appreciated!!
Account preferences updated also, thank you

 

[Peter_SSs]

Thank you so much! This seems to work You have helped me a lot, really appreciated!!
Account preferences updated also, thank you

You're welcome. Thanks for the follow-up.

.. and thanks for updating your version details.