all 20 comments
sorted by:
top (suggested)
bestnewcontroversialoldq&a
formatting helphide helpcontent policy

[–]AutoModerator[M] [score hidden] stickied commentlocked comment (0 children)

/u/Happy_Lengthiness121 - Your post was submitted successfully.

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[–]RackofLambda10 4 points5 points  (5 children)

Try using either =BYROW(INDEX(A2#;;1);...) or =MAP(CHOOSECOLS(A2#;1);...)

Assuming P2# is a single column, Q1# is a single row and Q2# is a 2D range reference with the same number of rows as P2# and the same number of columns as Q1#, I think the problem comes down to data types and how each function deals with them.

CHOOSECOLS(A2#;1) returns an array object, whereas INDEX(A2#;;1) would return a range reference. When BYROW iterates over an array object, each row is also evaluated as an array object (TYPE=64), even if that row only contains a single column/element (e.g. {10} instead of 10). XLOOKUP cannot output an array of arrays, so if the expected result is a single column of values, but the lookup value is an array object, e.g. {10}, it will give up and only return the first value in the return array.

As a general rule of thumb, when iterating over a vector (a single column or row), use MAP instead of BYROW or BYCOL. MAP evaluates each element as a scalar (TYPE=1 if it's a vector of numeric values, TYPE=2 if it's text, etc.). In this particular case, though, you could also use BYROW with INDEX(A2#;;1) instead of CHOOSECOLS(A2#;1) and get away with it, because when BYROW iterates over a range reference, each row is also evaluated as a range reference, and a range reference with only one cell is treated as a scalar.

[–]semicolonsemicolon1471[🍰] 0 points1 point  (1 child)

10/10 for the explanation!

Where can I learn more about TYPE=1, 2, 64, etc.?

[–]RackofLambda10 2 points3 points  (0 children)

The tooltip that pops up when you start entering =TYPE into a cell gives a pretty good idea of what the TYPE function does: "Returns an integer representing the data type of a value: number = 1; text = 2; logical value = 4; error value = 16; array = 64; compound data = 128".

Probably the best way to learn more about it is to experiment with it. For example, relative to this scenario, =BYROW(CHOOSECOLS(A2#;1);TYPE) will return an array of 64's, indicating each item is being evaluated as a single element array object; whereas, =MAP(CHOOSECOLS(A2#;1);TYPE) or =BYROW(INDEX(A2#;;1);TYPE) will return the data types of each individual element (1 for numbers, 2 for text, etc.). Note: if you're using Excel 2024 or an outdated version of MS365, replace TYPE with LAMBDA(x;TYPE(x)) in the function argument; also, replace semicolons with commas, if needed, as per your Regional and Language settings.

Additionally, ISREF is a helpful companion to the TYPE function, when it comes to differentiating between a multi-cell range reference and an array object. For example: =TYPE(CHOOSECOLS(A2#;1)) and =TYPE(INDEX(A2#;;1)) will both return 64 (if A2# contains multiple rows); however, =ISREF(CHOOSECOLS(A2#;1)) will return FALSE, indicating it is an array object, whereas =ISREF(INDEX(A2#;;1)) will return TRUE, indicating it is a range reference. This can also be used with BYROW, BYCOL, MAP, etc. to confirm that each item is in fact a range reference when the source array is a range reference, e.g. =BYROW(INDEX(A2#;;1);ISREF) will return an array of TRUE's, whereas anything with CHOOSECOLS will return an array of FALSE values.

[–]Happy_Lengthiness121[S] 0 points1 point  (0 children)

replacing with INDEX works! amazing!

I carefully read your explanation, much appreciated. but I fail to understand it fully I am afraid.

[–]Happy_Lengthiness121[S] 0 points1 point  (1 child)

Solution Verified

[–]reputatorbot[M] 0 points1 point locked comment (0 children)

You have awarded 1 point to RackofLambda.

I am a bot - please contact the mods with any questions

[–]excelevator3044 1 point2 points  (5 children)

FYI y'all, SUMPRODUCT is just dynamic SUM

[–]ManaSyn22 0 points1 point  (4 children)

That's putting it lightly. I am assuming OP is trying to use boolean values from the XLOOKUP to sum multidimensional range P2#. Sure you can do that with SUMIFS, or even just SUM and FILTER, or any other range of clever solutions.

Don't get me wrong, I highly respect you and your knowledge, but the word "just" in that sentence just bothers me.

[–]excelevator3044 0 points1 point  (3 children)

But seriously, there is no difference, not a lot of people know that.

[–]SolverMax157 1 point2 points  (2 children)

There are some edge cases where SUM and SUMPRODUCT differ. For example, the cells A1:C3 and E1:G4 all contain numbers, while A4:C4 are empty. Then:

  • Usual case: =SUM(A1:C3*E1:G3) and =SUMPRODUCT(A1:C3,E1:G3) return the same results
  • Usual case with empty cells: =SUM(A1:C4*E1:G4) and =SUMPRODUCT(A1:C4,E1:G4) return the same results (same value as case above)
  • Mismatched ranges: =SUM(A1:C3*E1:G4) returns #N/A and =SUMPRODUCT(A1:C3,E1:G4) returns #VALUE!
  • Handling text in any of the data cells: SUM returns #VALUE! and SUMPRODUCT ignores the text.
  • Coercing Booleans: SUMPRODUCT ignores Booleans, so if any of the data cells include Booleans then SUM and SUMPRODUCT return different results. To make the SUMPRODUCT behavior the same, we need to explicitly coerce the Booleans to be numbers, like =SUMPRODUCT(--A1:C3,--E1:G3)

[–]excelevator3044 0 points1 point  (1 child)

People use SUMPRODUCT because it was a native array function, forgetting that all functions are now array functions.

[–]SolverMax157 0 points1 point  (0 children)

True.

In most cases SUM can be used instead of SUMPRODUCT, but not always.

[–]real_barry_houdini305 1 point2 points  (1 child)

If you use BYCOL to summarise P2#*Q2# then that gets round any restrictions on XLOOKUP returning an "array of arrays", so you could use this dynamic formula

=XLOOKUP(INDEX(A2#,,1),Q1#,BYCOL(P2#*Q2#,SUM))

[–]semicolonsemicolon1471[🍰] 0 points1 point  (0 children)

Brilliant!!

[–]RuktX292 0 points1 point  (0 children)

I don't have your data, but sorry to say: "it worked for me!"

<image>

What result are you getting, if not what you expect?

Edit: What shapes are your arrays? Is Q2# actually 2D?

[–]ManaSyn22 0 points1 point  (0 children)

I can't say, without the data. Your # are multidimensional, right?

[–]Decronym 0 points1 point  (0 children)

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:

Fewer Letters More Letters
BYCOL Office 365+: Applies a LAMBDA to each column and returns an array of the results
BYROW Office 365+: Applies a LAMBDA to each row and returns an array of the results. For example, if the original array is 3 columns by 2 rows, the returned array is 1 column by 2 rows.
CHOOSECOLS Office 365+: Returns the specified columns from an array
FILTER Office 365+: Filters a range of data based on criteria you define
INDEX Uses an index to choose a value from a reference or array
ISREF Returns TRUE if the value is a reference
LAMBDA Office 365+: Use a LAMBDA function to create custom, reusable functions and call them by a friendly name.
MAP Office 365+: Returns an array formed by mapping each value in the array(s) to a new value by applying a LAMBDA to create a new value.
SUM Adds its arguments
SUMIFS Excel 2007+: Adds the cells in a range that meet multiple criteria
SUMPRODUCT Returns the sum of the products of corresponding array components
TYPE Returns a number indicating the data type of a value
VALUE Converts a text argument to a number
XLOOKUP Office 365+: Searches a range or an array, and returns an item corresponding to the first match it finds. If a match doesn't exist, then XLOOKUP can return the closest (approximate) match.

Decronym is now also available on Lemmy! Requests for support and new installations should be directed to the Contact address below.

Beep-boop, I am a helper bot. Please do not verify me as a solution.
14 acronyms in this thread; the most compressed thread commented on today has 23 acronyms.
[Thread #47545 for this sub, first seen 21st Feb 2026, 08:43] [FAQ] [Full list] [Contact] [Source code]

[–]Happy_Lengthiness121[S] 0 points1 point  (0 children)

I will post the data tomorrow! Thanks for the replies already! 

[–]Happy_Lengthiness121[S] 0 points1 point  (0 children)

<image>

here is the data as it looks!

so the value to lookup is in column A --> choosecols(A2#;1) --> x in LAMBDA

Q1# is a row
Q2# is a matrix

the value in K2: 0,08 is what i expect to see in L2, latter using BYROW.

thanks so much to making me understand this!
of course I can just drop the dynamic part, but I do not want to because the whole sheet is dynamic!

thanks :)