google-sheetsgoogle-sheets-formula

How Do I Give rows in a table of contents a key. Then look up the key Later using the data from the rows in table of contents?

I abridged what I'm trying to accomplish on my actual sheet, with a lot of data points to this test. It should be simple. Associate the "G"values with the "S" values in the table.

I tried to do it myself. I tried to use AI. To give me fix ideas. I don't understand how this can be so puzzling.

enter image description here

I included the formula in the picture but here it is:

`=ARRAYFORMULA(IF(H2:H="", "", IFERROR(VLOOKUP(H2:H, {FLATTEN(B2:F7), A2:A7}, 1, False), "No Match"))`)

Please let me know if its simple and I'm just being stupid. because FML For context to the bigger sheet. Google forms pulls large data into a table, this table then sorts the multiple data points into keys S is Steam IDs (signup) and G is Group keys (people who want to play together)

Solution

  • You may try the following formula-

    =MAP(TOCOL(H2:H,1),LAMBDA(x,IFERROR(CHOOSEROWS(A:A,MAX(INDEX(ROW(B2:F7)*(B2:F7=x)))),"No Match")))

    Base formula is =CHOOSEROWS(A:A,MAX(INDEX(ROW($B$2:$F$7)*($B$2:$F$7=H2)))) and MAP() is used to make it dynamic spill formula.

    enter image description here