Search code examples
arraysgoogle-sheetsgoogle-sheets-formulaformulavlookup

How do I find the heading above the first match to the right with a checkmark?

I have a sheet where I'm tracking revisions which could affect up to 4 parts, and I'm trying to figure out the best formula for cells A2:D2 to do the following:

  1. Get the part name to search for from the cell above
  2. Search range G2:AL2 for the first match of the part name which also has a checkmark in the cell directly to the left of it
  3. Return the revision number displayed in the corresponding merged header cell in Row G1:AL1 above it

In my example, the formula in Cell C2 would search for the name "PART C" in range G2:AL2 with a checked checkbox to its right, and would find the first match in cell T2 with the header value of "02", so Cell C2 would display "02".

Solution

  • try:

    =TRANSPOSE(ARRAY_CONSTRAIN(SORTN(SORT(TRANSPOSE(
 FILTER({G1:AK1; H2:AL2}, G2:AK2=TRUE)), 2, 1, 1, ), 9^9, 2, 2, 1), 9^9, 1))

    enter image description here

    update:

    =ARRAYFORMULA(TEXT(TRANSPOSE(ARRAY_CONSTRAIN(SORTN(SORT(TRANSPOSE(
 FILTER({HLOOKUP(COLUMN(G1:AK1), IF(G1:AK1<>"", {COLUMN(G1:AK1); G1:AK1}), 2, 1); 
 H2:AL2}, G2:AK2=TRUE)), 2, 1, 1, ), 9^9, 2, 2, 1), 9^9, 1)), "00"))

    enter image description here