How do I return the cell address of every instance of a key in a 1D array?-CodePudding

I’m trying to find every instance of a name in a list of names, and compile their locations in a list. I already know TEXTJOIN can do this with comma delineation, but I can’t figure out how to implement it.

I’ve tried using FIND, SEARCH, and MATCH in various ways, but can’t get it to work.

Here’s a mockup, where the filled in section is the goal. Click here This is the formula I’m trying to use: =INDEX(IFNA(VLOOKUP(E2:E, QUERY(L:O, "select L,sum(O) where L is not null group by L label sum(O)''"), 2))

Update: And here’s an actual graph photo.

For reference, Base Stakes and Results work perfectly, the only issue being that they are referring to the ELO column(the one for which the desired result is filled in), which is broken. How should I translate the working formula to function without detecting circular logic?

CodePudding user response：

try:

=QUERY(D:F; "select D,sum(F) where D is not null group by D label sum(F)''")

if you insist on injection use:

=INDEX(IFNA(VLOOKUP(A2:A; QUERY(D:F; 
 "select D,sum(F) where D is not null group by D label sum(F)''"); 2; )))

update:

use in F2:

=INDEX(IFNA(VLOOKUP(E2:E, QUERY({L:L, O:O}, 
 "select Col1,sum(Col2) where Col1 is not null group by Col1 label sum(Col2)''"), 2, )))

CodePudding user response：

Formula in Table 1 `$A$2`

=MAP(SORT(UNIQUE(D2:D)), 
     LAMBDA(n, IF(n<>"",
           {n,SUM(INDEX(FILTER(D:F,D:D=n),,3))},"")))

update:

Formula in Table 1 $A$2

Formula in Table 1 `$A$2`