Forum Discussion
yardarrat
Jul 16, 2021Copper Contributor
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 ...
mtarler
Jul 16, 2021Silver Contributor
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)
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)