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

by

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:

enter image description here

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?

  • 1

    FILTER or SUMIFS is likely more appropriate here.

    – 

  • The BOOLEAN Logic needs to be within Brackets Look the way I have posted in answers! But If you want to return multiple results then FILTER() would be better.

    – 




Yes you can use XLOOKUP() function as well:

enter image description here

• 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( )

enter image description here

=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().

You can’t use XLOOKUPlike this – but

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

will work
enter image description here

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:
Screenshot illustrating INDEX/MATCH approach

Leave a Comment