google-sheetsgoogle-sheets-formula

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!

enter image description here

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), "")))))))

    enter image description here