CODEWITHSUNDEEP
matrixgoogle-sheetsgoogle-sheets-formulavlookup
Rachel Bryan
Rachel Bryan

Reputation: 13

Return column headers (columns B onwards) based on a text value in Column A and number value in other columns - in a Google spreadsheet

I have a matrix - 1,172 words down column A, then the same 1,172 names across row 1. Then each word is cross-referenced with all the other names to give a similarity score (this is already done).

In another sheet, I want to look up a word, and return all the words with which it has a certain similarity score - in this case, greater than or equal to 0.33. I attach a MWE, in which I give an idea of the answer I am looking for by looking it up manually.

I think it's some sort of reverse lookup. As in, instead of finding the value corresponding to a particular row and a particular column, it's finding the column based on value in the main sheet and row. I'm just really stuck at this point and would massively appreciate some help. Thanks! MWE here

Upvotes: 1

Views: 49

Answers (1)

JvdV
JvdV

Reputation: 75870

If your words on the second sheet are in the same order then:

=IFERROR(TEXTJOIN(", ",,FILTER(Scores!B$1:W$1,(Scores!B2:W2>=0.33)*((Scores!B2:W2<1)))),"-")

Drag down.

Explanation:

  • Filter the values from row 1 according to the similarity score condition, using FILTER.
  • Concatenate the filtered values using TEXTJOIN.

Upvotes: 2

Related Questions