Vlookup with nested IF function

[JohnZ1156]

I have a data table with dates in column A and values in column B.
In cell C1 I enter the date for the Vlookup.
I would like to use Vlookup to search for a date in column A and return the corresponding value in column B.

Now,
Some dates may not have a corresponding value in column B. (an empty cell)
If the date found by Vlookup has no corresponding value in column B, I'd like it to return the Previous days value.
If THAT date has no corresponding value, I'd like it to return the NEXT Previous days value, and so on.

I tried a nested IF function with Vlookup something like this:
I was trying to get it to go to the prior date when no value was found.

=IF(VLOOKUP(C1,Table1,2)=0,VLOOKUP(C1-1,Table1,2),IF(VLOOKUP(C1-1,Table1,2)=0,VLOOKUP(C1-2,Table1,2),IF(VLOOKUP(C1-2,Table1,2)=0,VLOOKUP(C1-3,Table1,2),IF(VLOOKUP(C1-3,Table1,2)=0,VLOOKUP(C1-4,Table1,2),IF(VLOOKUP(C1-4,Table1,2)=0,VLOOKUP(C1-5,Table1,2),IF(VLOOKUP(C1-5,Table1,2)=0,VLOOKUP(C1-6,Table1,2),IF(VLOOKUP(C1-6,Table1,2)=0,VLOOKUP(C1-7,Table1,2),VLOOKUP(C1,Table1,2))))))))

Thanks for your help in advance.

 

[Fluff]

How about like

+Fluff 1.xlsm
	H	I	J	K	L
1	Date	Amount
2	20/12/2021	1		28/12/2021	4
3	21/12/2021	2
4	22/12/2021	3
5	23/12/2021	4
6	24/12/2021
7	25/12/2021
8	26/12/2021
9	27/12/2021
10	28/12/2021
11	29/12/2021	10
12	30/12/2021	11
13	31/12/2021	12
14	01/01/2022	13
15	02/01/2022	14
16	03/01/2022	15
17	04/01/2022	16
18	05/01/2022	17
19
Main

Cell Formulas
Range	Formula
L2	L2	=LOOKUP(2,1/(I2:INDEX(Table1[Amount],MATCH(K2,Table1[Date],0))<>""),I2:INDEX(Table1[Amount],MATCH(K2,Table1[Date],0)))

 

[JohnZ1156]

Hi Fluff,

That works great !! Thank you.
Being an old Lotus 1-2-3 User, I'm not familiar with the "Lookup" Function.

I do have another question which I thought I'd be able to figure out from your formula.

Is is possible to "go the other way"?
In other words, this formula goes backwards and returns the values for the previous day(s).

Can we also have a formula that would go forward and return the value for the day(s) AFTER the date with no value?

I'm not fluent enough in excel to "reverse" your great formula.
Thanks again, John

 

[Fluff]

How about

=INDEX(Table1[Amount],AGGREGATE(15,6,(ROW(Table1[Amount])-ROW(Table1[[#Headers],[Amount]]))/(Table1[Date]>=K2)/(Table1[Amount]<>""),1))

 

[JohnZ1156]

How about

=INDEX(Table1[Amount],AGGREGATE(15,6,(ROW(Table1[Amount])-ROW(Table1[[#Headers],[Amount]]))/(Table1[Date]>=K2)/(Table1[Amount]<>""),1))

I have no idea how this works, but it does !!! Thank you.
I would NEVER have been able to come up with this formula by looking at your first formula.
Thank you very much, again !!!

 

[Fluff]

You're welcome & thanks for the feedback.