Excel XLookup check if date is between two values in a table

Question

This is the same question as in "XLOOKUP() with multiple criteria and date between two dates" I found on this forum, but that question wasn't solved, so here is my attempt.

I have a (named) table with a column named "Datum", now I want to check the value of this field in a table that looks like the table in the image:

For this I use the XLOOKUP() formula. I have the following syntax:

=@XLOOKUP(1;[@Datum]>=V$3:V$8*[@Datum]<W$3:W$8;U$2:$U$8)

V3:V6 is the from column in the image W3:W8 is the till column in the image

When the condition is met, it should return the value from the sprint column.

I cannot get it to work though. Any suggestions?

Answer 1

Yes you can use XLOOKUP() function as well:

---

• Formula used in cell Z2

=XLOOKUP(1,(Y2>=$V$2:$V$7)*(Y2<=$W$2:$W$7),$U$2:$U$7)

---

In Dutch Version it remains same, only the separators are different:

=XLOOKUP(1;(Y2>=$V$2:$V$7)*(Y2<=$W$2:$W$7);$U$2:$U$7)

---

With FILTER( )

---

=FILTER($U$2:$U$7,(Y2>=$V$2:$V$7)*(Y2<=$W$2:$W$7),0)

---

Caveat: I am not sure whether you want the sum of those values between two dates or not if so, then XLOOKUP() is not the right function here, then you would need to use either SUM() or SUMPRODUCT().

---

Answer 2

You can't use XLOOKUPlike this - but

=SUMPRODUCT(A2:A4*(B2:B4<=Datum)*(C2:C4>=Datum))

will work

Answer 3

In your context, where there are no days skipped between the earliest from date and the latest till date you could also go retro and avoid any boolean tests: