Search for targets and indicate where they come from
I would like to search for the occurrences of the targets and present the result as shown in the image below using just one formula in M5, without dragging. --> Google Spreadsheet sample.
This is the closest that I get:
=join(" - ",transpose(ifna(QUERY(BYROW($D$5:$H$49, LAMBDA(row, IF(COUNTIF(row, $L5) > 0, INDEX(row, 1), ""))),"WHERE Col1 is not null"))))
But it does not populate the results.
Any thoughts or ideas would be appreciated, thank you!
Solution
If your current formula works, then use MAP() or BYROW() to make it dynamic spill array formula. Try-
=MAP(TOCOL(L5:L,1),LAMBDA(x,
join(" - ",transpose(ifna(QUERY(BYROW($D$5:$H$49, LAMBDA(row, IF(COUNTIF(row, x) > 0, INDEX(row, 1), ""))),"WHERE Col1 is not null"))))))
You can also use simplified formula using TEXTJOIN().
=MAP(TOCOL(L5:L,1),LAMBDA(x,TEXTJOIN("-",1,BYROW($D$5:$H$49, LAMBDA(row, IF(COUNTIF(row, x) > 0, INDEX(row, 1), ""))))))
And for A.x column use-
=MAP(TOCOL(L5:L,1),LAMBDA(x,TEXTJOIN("-",1,BYROW(HSTACK(SCAN("",C5:C49,LAMBDA(p,q,IF(q="",p,q))),$D$5:$H$49), LAMBDA(row, IF(COUNTIF(row, x) > 0, INDEX(row, 1), ""))))))
And you can combine two formula into one cell using HSTACK(). See you sheet Q5 cell.
=HSTACK(MAP(TOCOL(L5:L,1),LAMBDA(x,TEXTJOIN("-",1,BYROW($D$5:$H, LAMBDA(row, IF(COUNTIF(row, x) > 0, INDEX(row, 1), "")))))),
MAP(TOCOL(L5:L,1),LAMBDA(x,TEXTJOIN("-",1,BYROW(HSTACK(SCAN("",C5:C,LAMBDA(p,q,IF(q="",p,q))),$D$5:$H), LAMBDA(row, IF(COUNTIF(row, x) > 0, INDEX(row, 1), "")))))))
- How can I resolve this `Error: You must select all cells in a merged range to merge or unmerge them.` while merging cells through App Script?
- Casting method asTextItem throws error for Google form item
- ARRAYFORMULA with GOOGLEFINANCE function
- Conditional formatting depending on two cells
- How do I find the highest sum of multiple cells in column A where the sum is based on multiple cells matching in column B in Google Sheets?
- Returning the last input entry
- Draw and filter Charts in App Script Web app
- Google Sheets - select "most recent " cell in a column and return corresponding value in same row
- Byrow formula with reference to last row and last column
- 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?
- Duplicate whole spreadsheet using Google Sheets API
- Array Formula to omit certain values
- Google Sheets Query While Removing Duplicates
- NAME error hiperlink function Apps Script Google Sheets
- Three-dimensional referencing
- Can I use a named range as column headers?
- Trying to get Google App Script Code to Acknowledge Cells In A Column in Excel to do Individual Math
- How to get the price of a stock at a particular date from Google Finance?
- How to Split a String Up into Individual Characters
- How to integrating Google Sign-In into your google script web app
- Formula is currently double-counting a cell
- Get dropdown options in google sheets in table
- Search for targets and indicate where they come from
- One-to-one assignment in Google sheets with data validation
- Google Sheets: Number Format option of horizontal axis labels in a histogram
- Textjoin with header values
- Specifying a time span / duration constant in Google Sheets formula over 24h
- If spreadsheet ID is not equal to and number of rows less than?
- Using a gs function with an input from a selection in html on appscript
- Generate unique random numbers in Google Sheets