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:$...
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.
"=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.
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?
