1

I'm working on a formula for a "result" column in a table that looks up data in another table based on some criteria in the main one, then displays it. Of the point where I'm getting stumped at, it is returning a dynamic 2D array (row returns are variable dependent on an FILTER/MATCH lookup). Example (4 columns x 3 rows) return value: {1,2,3,4;1,2,3,4;1,2,3,4}

I want to get the product of the values in each column such that the output from the formula using the example return value above would be this: {1,8,27,64}

(Which is the equivalent of doing this: {1,2,3,4} * {1,2,3,4} * {1,2,3,4})

How do I accomplish this using only formula functions?

All of my google searches have been coming back with methods that assume static table size, multiply the columns against each other, and/or the table "existing" within a sheet somewhere (and still don't give the result I'm looking for).

I've already figured this out via VBA, but due to the nature of how I'm using the sheet, waiting for the updates to run via VBA are too slow. It's not that it takes forever - it's only about 3-4 seconds for ~60 rows - but rather that I make a change to my lookup table, wait for the update to the result column in the main table, check the results, make another change, rinse & repeat 50 some odd times while I fine tune things to look the way I want them to, and the delays are wearing on me.

I'm also unsure that I can use helper rows/columns as the results would need to be packed too close together and would just display #SPILL, which results in the formulas referencing it just showing the same thing. So it looks like this needs to be self contained.

Plugins are an absolute no.

(Other little edit: Whoops, I put INDEX/MATCH above when I meant FILTER/MATCH.)

EDIT: Hopefully this will help clarify?

Here's the structure of my main table where this formula resides: enter image description here

My Formula takes the words from ColA and ColB and uses FILTER/MATCH to do a lookup that 1) returns multiple results, 2) the return results are entire rows, and 3) is an exact match (it returns nothing for words in ColA and ColB that don't exist in the lookup table).

This means that, as of the current state of the formula, I'm getting 2D arrays back for most of the rows in the main table. AFAICT, I'm not going to be able to stick these into a helper column somewhere as they'll overlap and just cause #SPILL which then won't evaluate in whatever formulas then reference them.

I need to turn these into 1D arrays that are 1 row x same-#-as-source columns where the result in each column is the product of only that column's contents. I'm then going to continue building off from this formula with more formula to achieve my final result that will fit nicely in the single cell. (I already have that piece solved. It's just going from the 2D -> 1D product array I need help with.)

Improve this question
12
  • Post your input then expected output and explain how to achieve that. Commented Feb 13, 2022 at 8:11
  • Check out sumproduct(). Commented Feb 13, 2022 at 8:13
  • Until MS release functions such as BYROW and BYCOL, there is no shorthand method by which an array of products (by column or row) can be obtained from an array (there is such a method when querying a range, but that isn't relevant to your case). The only option would be to create a formula which comprises as many arguments as there are columns in the input array and then pass these to CHOOSE. If the number of columns to be considered is fixed and small, this might be a feasible option. Otherwise, share a dataset so that alternatives to your INDEX/MATCH set-up can be sought. Commented Feb 13, 2022 at 8:42
  • @Harun24HR, "input" (example) is the posted {1,2,3,4;1,2,3,4;1,2,3,4} and expected output with that example is the posted {1,8,27,64}. The input is simply a result of another formula that I want to nest inside of the solution formula. So, if we want to say MyFormula represents the input array, then a (mockup) solution would be PRODUCT(MyFormula) (though this one obviously doesn't work as it multiplies all of the values together). Commented Feb 13, 2022 at 13:58
  • @SolarMike, SUMPRODUCT() does not do what I want. It multiplies the values in the wrong order and then sums them. Commented Feb 13, 2022 at 14:05

2 Answers 2

Reset to default
1

=LET(z,MyArray,a,INDEX(z,,1),b,INDEX(z,,2),c,INDEX(z,,3),d,INDEX(z,,4),CHOOSE({1,2,3,4},PRODUCT(a),PRODUCT(b),PRODUCT(c),PRODUCT(d)))

where MyArray is the construction which returns your 2D array, e.g.

{1,2,3,4;1,2,3,4;1,2,3,4}

The above assumes that MyArray returns an array comprising 4 columns. For each additional column beyond that number, an additional name and calculation parameter will be required within the LET function. What's more, the array passed to CHOOSE should be adjusted such that it comprises an array of integers from 1 up to the number of columns returned by MyArray.

For example, if MyArray returned instead:

{1,2,3,4,5;1,2,3,4,5;1,2,3,4,5}

the required construction would be:

=LET(z,MyArray,a,INDEX(z,,1),b,INDEX(z,,2),c,INDEX(z,,3),d,INDEX(z,,4),e,INDEX(z,,5),CHOOSE({1,2,3,4,5},PRODUCT(a),PRODUCT(b),PRODUCT(c),PRODUCT(d),PRODUCT(e)))

Edit: better is to replace the static array passed to CHOOSE with a SEQUENCE construction:

=LET(z,MyArray,a,INDEX(z,,1),b,INDEX(z,,2),c,INDEX(z,,3),d,INDEX(z,,4),e,INDEX(z,,5),CHOOSE(SEQUENCE(,COLUMNS(z)),PRODUCT(a),PRODUCT(b),PRODUCT(c),PRODUCT(d),PRODUCT(e)))

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Thank you, this works. Not the most elegant solution considering I have 10 columns at the moment and this may change in the future, but until the new BYCOL function is released, I think this is the best way. (Which, by the way, I took the time to get a hold of the Insider version for purposes of playing with those new functions, and BYCOL(MyArray,LAMBDA(array,PRODUCT(array))) (where "array" is literally typed in there) does work.) Your answer with LET, though, has made me realize that the next steps I need to take have a better solution. XD
Agreed that it's far from ideal! FYI have added a slighly improved version which replaces the static array passed to CHOOSE with a SEQUENCE construction.
0

So:

EDIT based on comment: If the sumproduct is in the wrong order, then the alternative is simple:

=SUMPRODUCT(D10:F10,D11:F11,D12:F12,D13:F13)


=sumproduct(A1:A4,B1:B4,C1:C4)

Then A1:A4 (and the other arrays) could be the index with match retrieving the data.

Or have the index and match in other cells and use those result in the sumproduct.

Had a play, making up some data as so:

enter image description here

In Cell D88:

=INDEX($B$2:$H$5,MATCH($C10,$A$2:$A$5,0),MATCH(D$9,$B$1:$H$1,0))

In cell D15:

=SUMPRODUCT(D10:D13,E10:E13,F10:F13)

In cells D9:F9 is data validation to choose the column heading A:G.

Improve this answer

6 Comments

This does not work. 1) Requires the "input" array to exist somewhere on the sheet (how do I do this for ~60 input arrays that can also potentially shrink or grow if I remove or add lines to the main table?), and 2) SUMPRODUCT() (to my understanding) multiplies the values row-wise (equivalent of {1;1;1} * {2;2;2} * {3;3;3} * {4;4;4}, not what I want) and then sums them (also not what I want).
“Wrong order”? ABC is the same as BAC is the same as ACB etc
@FotS Check the edit, sumproduct works on rows or columns....
Ok, but it's still summing it. I don't want the results summed. I'm also not able to put the array result into a helper column somewhere as then it'll just show #SPILL and won't evaluate into a formula that references it.
@FotS well, I would start looking at &
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.