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

Occasional Visitor

macOS Big Sur 11.3.1

MacBook Air (M1, 2020)


Microsoft Excel for Mac

Version 16.51


I have these two tables:

Bildschirmfoto 2021-07-16 um 17.19.54.png


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:









but when I try using the # operator it breaks:









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

thanks for your help!

1 Reply
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)