How to combine values from two columns in all possible combinations using Google Sheets

[sophialee92]

I need assistance in devising a formula that sums every value from column A with each corresponding value from column B to generate all possible results. The challenge here is that the sizes of the two columns may vary.

Column A | Column B | Output
---------|----------|--------
5        | 100      | 105
15       | 200      | 115
25       | 300      | 125
35       |          | 205
45       |          | 215
         |          | 225
         |          | 305
         |          | 315
         |          | 325
         |          | 405
         |          | 415
         |          | 425

I was considering using ARRAYFORMULA because it can manage changing data ranges seamlessly. Is there a way to achieve this with a formula rather than coding a custom script? The outcome should refresh automatically when I modify the values in either column.

[WhisperingWind]

I encountered this same problem when comparing pricing across product tiers. What worked well for me was combining SEQUENCE and INDEX functions to generate the cartesian product. Start by determining your data ranges using COUNTA, then use SEQUENCE for both column indices. The final formula I used is =INDEX(A:A,MOD(SEQUENCE(COUNTA(A:A)*COUNTA(B:B))-1,COUNTA(A:A))+1) + INDEX(B:B,CEILING(SEQUENCE(COUNTA(A:A)*COUNTA(B:B))/COUNTA(A:A))). This approach automatically accommodates variable column lengths and refreshes as you update the source data. While the MOD and CEILING concepts can be tricky at first, this method proves to be much more reliable than matrix multiplication in cases with sparse data.

[emmat83]

you can try this formula: =ARRAYFORMULA(FLATTEN(TRANSPOSE(A1:A5)+B1:C1)). just make sure your ranges are correct, or it might break. if one column is shorter, throw in some zeros to fill the gap.

[Oscar64]

This is a cross join operation - gets tricky in Google Sheets without scripting. I’ve tackled similar problems and found FLATTEN with nested arrays works best. Here’s what I’d try: create a helper matrix using MMULT for the cartesian product, then FLATTEN it into a single column. Something like =FLATTEN(MMULT(TRANSPOSE(A:A),ONES(1,COUNTA(B:B)))+MMULT(ONES(COUNTA(A:A),1),TRANSPOSE(B:B))) but adjust ranges to exclude empty cells first. Make sure both arrays are sized properly before multiplication. I wrap each column range with FILTER to remove blanks - prevents errors when column lengths don’t match. More complex than ARRAYFORMULA but handles dynamic ranges well once you get it set up.