Forum Discussion
Combining the 2 kinds of array (CSE) formulae in Excel
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:$...
"=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.
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.
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.