Forum Discussion

yardarrat's avatar
yardarrat
Copper Contributor
Jul 16, 2021

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

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!

excel

1 Reply

  • mtarler's avatar
    mtarler
    Silver Contributor
    Jul 16, 2021
    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)

Resources