CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
David Cantler
David Cantler

Reputation: 39

return row header if text present

On the sheet Dynamic 3-5 Checklist I want to use the cells in blue (columns B, D, F, etc.) as a reference to look for the text in the range B9:P21 on sheet Dynamic Grade 5, then display the words in column A.

For example, C1 on Dynamic 3-5 Checklist should be populated with the text Typing Program from B12 on Dynamic Grade 5. Also, the text from C1 appeared for any other project the name of that project from the A column would also appear in C1.

https://docs.google.com/spreadsheets/d/1lCgHPPXML5Ll9XsrMPXmNnHOEQO0RZP-aWSbMtiCfg4/edit?usp=sharingxxx

Upvotes: 1

Views: 77

Answers (1)

NightEye
NightEye

Reputation: 11214

I added the formula on your sample sheet (C1, E1, G1, I1), please check if those cells show what you wanted to get.

Formula:

C1:

=textjoin(CHAR(10), true, arrayformula(if(isblank(B2:B), "", ifna(vlookup("*"&B2:B&"*", {'Dynamic Grade 5'!B$9:B,'Dynamic Grade 5'!$A$9:$A}, 2, false), ""))))

Breakdown of the important parts:

  1. {'Dynamic Grade 5'!B$9:B,'Dynamic Grade 5'!$A$9:$A} - create an array where the 1st column is where the search term is to be searched, and 2nd column is the result we want which is column A
  2. vlookup("*"&B2:B&"*" - pads the search term with wildcard to capture partial matches (multiple search terms can be found in one cell so normal vlookup fails)
  3. textjoin with true - joins all results of arrayformula except blank cells

Output:

output

  • If you want to get only the unique values, prepend the arrayformula with unique.
=textjoin(CHAR(10), true, unique(arrayformula(if(isblank(B2:B), "", ifna(vlookup("*"&B2:B&"*", {'Dynamic Grade 5'!B$9:B,'Dynamic Grade 5'!$A$9:$A}, 2, false), "")))))

unique

Note:

  • When copying the formula to other cells in 1:1 range, you only need to update the 1st column of {} array since B2:B correctly adjusts automatically when copied. See the pattern on the inserted formula on your sheet and you'll get the idea

UPDATE:

C2:

=ifna(join(CHAR(10), index(filter({'Dynamic Grade 5'!B$9:B, 'Dynamic Grade 5'!A$9:A}, 'Dynamic Grade 5'!B$9:B = B2), , 2)), "")

Upvotes: 1

Related Questions