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

user_8517084 0
Viewed 29

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".

vlookup google-sheets-formula formula google-sheets arrays
2 Answers

Use filter(), like this:

=+filter($G1:$AK1, $G2:$AK2, $H2:$AL2 = A1)

See the new Solution tab in your sample spreadsheet.

The formula requires that you repeat the revision number. You can do that by using a formula to copy the value to the left as required. If the duplicated revision numbers get in the way, hide them by setting the text color to white, or put the duplicates in a row of their own, and hide the row.

user_130451930

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

user_56326290
Related