Combining the 2 kinds of array (CSE) formulae in Excel

[tzar25]

0th question: If I write "=VLOOKUP($B3;Items;J$71)+...+VLOOKUP($G3;Items;J$71)" it does the correct thing. As I understand the single value CSE is supposed to work in these cases as

"{=VLOOKUP($B3:$G3;Items;J$71)}" (obviously entered without curly brackets and pressing ctrl+shift+enter. For me, it only works in the singular case when the value is found in the first row by vlookup. What am I doing wrong?

The question itself, after the 0th case works:

Say, the result of the previous goes into the cell J1. Now I would like the same for J1:V1 with the last argument of vlookup being J71:V71 respectively. If I select the whole J1:V1 interval and enter {=VLOOKUP($B3:$G3;Items;J$71:V$71)} the same way it should work right? Unfortunately neither works for me and I am kinda lost.

ps: ignore or correct $ marks, I think they are not the source of my problem.

ps2: Items is a table I made and excel likes to move around the columns, which I hardly ever want, so I tried with absolute range reference instead as well. Didn't solve the problem. (1: how to do absolute reference with a table?)

I dare ask a completely unrelated side question, for which the answer I couldn't manage to find anywhere. Found similar issues with suggestions, none solved mine. (2: A lot of functions seem to be missing for me. One day they were there and I could use them, and the other day Excel just decided that those functions simply do not exist. Previously written formulae didn't work, it wouldn't suggest the formula as auto complete and so on. XLOOKUP and FILTER were 2 I noticed, but I am convinced they are not the only ones)

Thank you very much for your answer! Extra special thanks if I can get answers for my 2 extra questions, too! Have a nice day!

[#2]

@tzar25 

Does

=SUM(VLOOKUP($B3:$G3;Items;J$71))

confirmed by pressing Ctrl+Shift+Enter work in your version of Excel?

This should work too:

=SUM(VLOOKUP($B3;Items;J$71:V$71))

But

=SUM(VLOOKUP($B3:$G3;Items;J$71:V$71))

is too much to ask of Excel.

P.S. Functions such as XLOOKUP and FILTER are available in Excel in Microsoft 365 and Office 2021, and in Excel Online as well.

[#3]

"=SUM(VLOOKUP($B3:$G3;Items;J$71))
confirmed by pressing Ctrl+Shift+Enter work in your version of Excel?"
- It "works", as it does something, but not the correct thing. The only time it did the correct thing if the singular nonzero value was found in the first row of Items.

[#4]

@tzar25 

Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?

[#5]

@tzar25 

If you on Excel with dynamic arrays (for web, 365, 2021) CSE returns only first value of the resulting array.

Also, your tag is Excel for web.

Here

=SUM(
    FILTER( CHOOSECOLS(items, FILTER( $J71:$V71, $J71:$V71 )),
            IFNA(XMATCH( TAKE(items,,1), $B$3:$G$3),0)
    )
)

could work.