Google sheets query multiple rows+cols to one cell with lookup table

1
votes

Cannot figure out how to pull the results of a query with multiple rows and columns into single cells and find their details from my lookup table. Any help is greatly appreciated.

Trying this =ArrayFormula( QUERY({ QUERY({A4:A16,TEXTJOIN(",",TRUE,B4:C16)}, "SELECT Col1, Col2, Col3 WHERE Col1 MATCHES 'Core 1|Core 2|Core 3' LABEL Col1 'Core ID', Col2 'Full Result'") }, "SELECT Col2 WHERE Col1 = Col1") )

Sheet to help

2
google-sheetsgoogle-sheets-formula

2 Answers

1
votes

It's always a headache trying to mix Query with ArrayFormula. They don't go well together.

I arrived at a nice formula, but the catch is that it relies on you sorting your data by Core Item first. You can easily achieve this with:

=SORT(A4:C16)

For this example, I placed this formula is cell N4. Then your resultant formula is:

=ArrayFormula(
{
  "Core ID", "Full Result";
  UNIQUE(FILTER(N4:N,N4:N<>"")),
  SUBSTITUTE(
    TRANSPOSE(
      SPLIT(
        REGEXREPLACE(
          TEXTJOIN(
            ",",
            ,
            UNIQUE(
              TRANSPOSE(
                IF(
                  TRANSPOSE(FILTER(N4:N,N4:N<>""))=FILTER(N4:N,N4:N<>""),
                  FILTER(O4:O,O4:O<>"")&
                  " ("&
                    VLOOKUP(FILTER(O3:O,O3:O<>""),A23:B53,2,)&
                  ") - "&
                  FILTER(P4:P,P4:P<>"")&
                  " ("&
                    VLOOKUP(FILTER(P4:P,P4:P<>""),A23:B53,2,)&
                  ")",
                  ";"
                )
              )
            )
          ),
          ",(;,)+",
          ";"
        ),";"
      )
    ),
    ",",
    CHAR(10)
  )
})

It uses the Group Concatenate method I've adopted for stuff like this with UNIQUE, TRANSPOSE, and IF. This can probably be solved without using the precalculated SORT by substituting all instances of the N4:N, but doing it this way saves on computation time. If you need it gone, you can also hide the column or stow it in another sheet.

0
votes

If you are ok breaking apart your formula then this would be an acceptable approach.

Start by Building your query criteria.

=ArrayFormula(
    QUERY({QUERY({A4:A16,ArrayFormula(B4:B16&" ("&VLOOKUP(B4:B16,A23:B53,2,false)&") - 
("&VLOOKUP(C4:C16,A23:B53,2,false)&")")}, "SELECT Col1, Col2 WHERE 
Col1 MATCHES 'Core 1|Core 2|Core 3' LABEL Col1 'Core ID', Col2 'ALL Result'")}, 
"SELECT Col1, Col2 WHERE Col1 = Col1"))

Then in another column get the unique values and lastly, join the filtered values.

=UNIQUE(E4:E17)

=Join(Char(10),FILTER(F4:F12,E4:E12 = H4))

The downside to this is you will need to have multiple columns to achieve your desired result but it is easy to follow. Good luck!