Go to previous date if no mach found

[Jd1uth]

Hallo i use this code =ALS.NB(VERT.ZOEKEN(A1;Planning!$A$2:$S$100074;3;ONWAAR);0) to search for a date
if date found do something on sheet 3 this works fine when date match
on the sheet (Planning) to search I have a week calendar
i use date +7 for a workweek
28-9-2024 (Monday)
blank cell
blank cell
blank cell
blank cell
3-10-2024 (Monday)
is it possible if date not match search or cell is blank to go back to previous date

 

[MARK858]

Have you tried changing your lookup to True rather than False?

Btw you should really post your formula in English on this forum

 

[Jd1uth]

yes but that make,s no different I am also missing the weekend dates be course of the 5 workdays so in the weekend there is a 0 be course there is no date found
that way i think he shoot look for the previous date

 

[MARK858]

yes but that make,s no different

That is a very strange result you are getting if you don't have an exact match using TRUE and FALSE in VLOOKUP (see below) and blank cells.

in the weekend there is a 0

In your first post you say there are blank cells between the dates, a 0 is not a blank cell, which is it?
Perhaps you need to post your actual data using XL2BB.

BTW, neither of the dates you posted is a Monday

Book1
	A	B	C	D	E	F	G
8	28/09/2024
9				07/10/2024			#N/A
10							05/10/2024
11
12
13
14	05/10/2024
15
16
17
18
19
20	12/10/2024
Sheet1

Cell Formulas
Range	Formula
D9	D9	=TODAY()+9
G9	G9	=VLOOKUP(D9,A8:A20,1,FALSE)
G10	G10	=VLOOKUP(D9,A9:A21,1,TRUE)
A14,A20	A14	=A8+7

For anyone reading this thread the formula in post 1 in English is

=IFNA(VLOOKUP(A1,Planning!$A$2:$S$100074,3,FALSE),0)