Dynamic Row/Column ArrayFormula

DaveG 0
Viewed 65

Looking for a single ArrayFormula which can populate a matrix calculation where the row and columns are dynamically generated from separate lists.

I have a working ArrayFormula for a single column, but cannot work out how to have this formula auto-populate as new columns are added.

There are separate sheets with Products and Companies. Each Product has attributes Height, Width, and Depth. Each company has criteria for each Height, Width, Depth attribute. A matrix is populated indicating whether a product Height, Width, and Depth fall within the constraints of each company.

Product.Height <= Company.Height AND Product.Width <= Company.Width AND Product.Depth <= Company.Depth Product / Company Matrix

Columns K and L are auto-populated using ArrayFormulas (K3 =query(A3:A,"select A") and L2 =transpose(query(F3:F7,"select F"))) allowing new Products and Companies to be added without having to separately maintain the Matrix.

The ArrayFormula copied across L3:P3 shows the desired result for single Company column:

=arrayformula(
    if(
        vlookup(indirect("K3:K"&counta($A3:$A)+2),indirect("A3:D"&counta($A3:$A)+2),2,0)<=vlookup(L$2,indirect("F3:I"&counta($F3:$F)+2),2,0),
        if(
            vlookup(indirect("K3:K"&counta($A3:$A)+2),indirect("A3:D"&counta($A3:$A)+2),3,0)<=vlookup(L$2,indirect("F3:I"&counta($F3:$F)+2),3,0),
            if (
                vlookup(indirect("K3:K"&counta($A3:$A)+2),indirect("A3:D"&counta($A3:$A)+2),4,0)<=vlookup(L$2,indirect("F3:I"&counta($F3:$F)+2),4,0),
                "✔",
                "✗"
            ),
            "✗"
        ),
        "✗"
    )   
)

The goal is to paste a single ArrayFormula in a cell, likely L3, allowing new Companies to be added without needing to manually copy/paste the ArrayFormula across the new Company columns.

The sample sheet combines Products, Companies, and the Matrix into a single sheet for simplicity.

Products and Companies

Products A2:D7

Products Height Width Depth
Product 1 10 10 10
Product 2 20 20 20
Product 3 25 30 30
Product 4 30 35 35
Product 5 50 50 50

Companies F2:I7

Company Height Width Depth
Company 1 5 5 5
Company 2 20 20 20
Company 3 25 25 25
Company 4 30 35 35
Company 5 25 25 25
array-formulas google-sheets
1 Answers

Here's what I would do:

Create another sheet which will list all combinations of Products and Companies. Use the following formula in cell A2:

=INDEX(SPLIT(FLATTEN(FILTER(Sheet1!A3:A,LEN(Sheet1!A3:A))&"#"&TRANSPOSE(FILTER(Sheet1!F3:F,LEN(Sheet1!F3:F)))),"#"))

In cells C1 to E1 of the new sheet, add the headers Height, Width and Depth.

In cell C2 of the new sheet, add this formula:

=ARRAYFORMULA(IF(VLOOKUP(FILTER(A2:A,LEN(A2:A)),Sheet1!A:D,MATCH(FILTER(C1:1,LEN(C1:1)),Sheet1!A2:D2,0),FALSE)<=VLOOKUP(FILTER(B2:B,LEN(B2:B)),Sheet1!F:I,MATCH(FILTER(C1:1,LEN(C1:1)),Sheet1!F2:I2,0),FALSE),1,0))

This creates a matrix of 1s and 0s; 1 if the company dimension is greater than or equal to the product dimension, and 0 if it is not.

Then in the original sheet, in cell L3, add this formula:

=ARRAYFORMULA(IF(VLOOKUP(FILTER(K3:K,LEN(K3:K)) & FILTER(L2:2,LEN(L2:2)),{Sheet2!A:A & Sheet2!B:B,Sheet2!C:C * Sheet2!D:D * Sheet2!E:E},2,FALSE),"✔","✗"))

This looks up the combination of Product and Company and if all three of the company dimensions are greater than the product dimensions (1 * 1 * 1 = 1), then it shows a check mark, and if not then a cross.

I'm using the cell references in the simplified single sheet you provided, so you'll need to change them obviously.

It's similar to a Sheets challenge that I faced lately, so was glad to share what I found out. Basically, it's easier to have a separate sheet for calculations then use that for a lookup reference.

userwztV0
Related