Spill Range and XLOOKUP with complex criteria, one of which uses the # operator

[yardarrat]

macOS Big Sur 11.3.1

MacBook Air (M1, 2020)

Microsoft Excel for Mac

Version 16.51

I have these two tables:

A2:D7 has the different VAT rates and the corresponding dates when they were valid.

F2:G14 has records containing the VAT type

In column H I want to find the VAT rate that corresponds with both the VAT id and the date range. 

But it has to be a dynamic formula because F2:G14 is already a spilled range and has a variable number of records.

this works as expected:

=XLOOKUP(1;--(G2=$A$2:$A$7)*($C$2:$C$7<=F2)*($D$2:$D$7>=F2);$B$2:$B$7;0)

but when I try using the # operator it breaks:

=XLOOKUP(1;--(G2#=$A$2:$A$7)*($C$2:$C$7<=F2)*($D$2:$D$7>=F2);$B$2:$B$7;0)

how can I dynamically get a lookup value as long as there is a value in the G column?

thanks for your help!

[#2]

It breaks because you have the array G2# but still have F2 a single value. Also you say F2:G14 is a spilled range so does that mean there is 1 equation in F2 that spills into both columns or 1 equation in F2 and another equation in G2?
if you have 1 equation in each then replace F2 with F2# in the second equation above
if you have only 1 equation in F2 then replace F2 with INDEX(F2#,,1) and replace G2 with INDEX(F2#,,2)