#SPILL! in VLOOKUP - multiple columns

[phillygirl777]

Hello... I am trying to add multiple columns through VLOOKUP to get the sum for the entire year rather than adding up 12 lookups for 12 months, doing something like this:
,
=VLOOKUP($B$6,'Data by Dept'!$A:$BU,{38,39,40,41,42,43,44,45,46,47,48,49},FALSE)

I am getting the #SPILL! error. Does Excel 365 support this?

Any input is greatly appreciated.

P.S.: I know that SUMIF/SUMIFS would have worked better, but I still want to know if it was wrong to use the curly brackets.

 

[Debaser]

Your formula is returning 12 values in a horizontal array. The #SPILL error means that you have data already in the cells that the formula is trying to output to. If you wanted the total value, you should wrap that in a SUM.
=SUM(VLOOKUP($B$6,'Data by Dept'!$A:$BU,{38,39,40,41,42,43,44,45,46,47,48,49},FALSE))

or just:

=SUM(XLOOKUP($B$6,'Data by Dept'!$A:$A,'Data by Dept'!$AL:$AW))

 

[phillygirl777]

Your formula is returning 12 values in a horizontal array. The #SPILL error means that you have data already in the cells that the formula is trying to output to. If you wanted the total value, you should wrap that in a SUM.
=SUM(VLOOKUP($B$6,'Data by Dept'!$A:$BU,{38,39,40,41,42,43,44,45,46,47,48,49},FALSE))

or just:

=SUM(XLOOKUP($B$6,'Data by Dept'!$A:$A,'Data by Dept'!$AL:$AW))

It worked - thank you so much!