Return multiple addresses of a lookup result

Question

I have a query which checks a column for multiple instances of any cell value and counts them. This indicates how many times social media content has been reposted.

=query(query('Editorial 2022'!I:S,"select S, count(S) group by S label S 'Repost Link Column', count(S) 'No. of Reposts'"),"WHERE Col2 > 1 order by Col2 desc")

What I'm attempting and failing to do is then to return the cell references for all of those instances using CELL, INDEX, MATCH.

=IF(A4="",,CELL("address",INDEX('Editorial 2022'!$S$3:$S$100,MATCH(A4,'Editorial 2022'!$S$3:$S$100,0))))

I know that these only return the first cell reference that matches the criteria in the formula. Does anyone know how would I go about returning all cell references, ideally displayed in the cell to the right of the previous match?

Spreadsheet - Reposts Check Spreadsheet - Editorial 2022 Sheet

Grateful to anyone who can help.

Many thanks,

Mark

those cell references you want to use in some other formula or only you want to create hyperlinks? — player0, Nov 11 '21 at 15:05
Questions should be self contained. While external links are welcome(note: *Google sheets [exposes your email address](https://meta.stackoverflow.com/questions/394304/)*), consider adding a [table](https://meta.stackexchange.com/questions/356997/) or screenshots or csv text(like [this](https://stackoverflow.com/a/64186520/) by *export*ing as csv) to show your data structure. — TheMaster, Nov 11 '21 at 19:21
@player0 I do not need to use the cell references for anything after this point. This will only be needed by humans to locate the cells where multiples have been found. — markwhiley, Nov 12 '21 at 08:32

score 0 · Answer 1 · answered Nov 11 '21 at 15:17

QUERY() can help you with this. But first you should prepare your data so that when a match is found, QUERY() outputs the cell address, not the value itself

Using the following formula, we add a column with cell addresses to the cells themselves ArrayFormula({ADDRESS(ROW('Editorial 2022'!S:S),COLUMN('Editorial 2022'!S:S),1,true,"Editorial 2022"),'Editorial 2022'!S:S})

Now use QUERY() to find all matches ArrayFormula(query({ADDRESS(ROW('Editorial 2022'!S:S),COLUMN('Editorial 2022'!S:S),1,true,"Editorial 2022"),'Editorial 2022'!S:S},"select Col1 where Col2 matches '"&A4&"'"))

So we put everything in one cell with the JOIN() function

=ArrayFormula(JOIN(",",query({ADDRESS(ROW('Editorial 2022'!S:S),COLUMN('Editorial 2022'!S:S),1,true,"Editorial 2022"),'Editorial 2022'!S:S},"select Col1 where Col2 matches '"&A4&"'")))

player0 · Answer 2 · 2021-11-12T10:21:14.500

try:

=INDEX(IFNA(REGEXREPLACE(SPLIT(VLOOKUP(A4:A, 
 TRIM(SPLIT(FLATTEN(QUERY(QUERY({'Editorial 2022'!S2:S&"♠", 
 TEXT(ROW('Editorial 2022'!S2:S), "♦00000")&"×'Editorial 2022'!S"&ROW('Editorial 2022'!S2:S)}, 
 "select max(Col2) where not Col1 = '♠' group by Col2 pivot Col1"),,9^9)), 
 "♠")), 2, 0), "♦"), "^\d+×", )))

demo spreadsheet

update:

=INDEX(HYPERLINK(IFNA(REGEXREPLACE(SPLIT(VLOOKUP(A4:A, 
 TRIM(SPLIT(FLATTEN(QUERY(QUERY({'Editorial 2022'!S2:S&"♠", 
 TEXT(ROW('Editorial 2022'!S2:S), "♦00000")&"×#gid=950541462&range=S"&ROW('Editorial 2022'!S2:S)}, 
 "select max(Col2) where not Col1 = '♠' group by Col2 pivot Col1"),,9^9)), 
 "♠")), 2, 0), "♦"), "^\d+×", )), IFNA(REGEXREPLACE(SPLIT(VLOOKUP(A4:A, 
 TRIM(SPLIT(FLATTEN(QUERY(QUERY({'Editorial 2022'!S2:S&"♠", 
 TEXT(ROW('Editorial 2022'!S2:S), "♦00000")&"×jump to S"&ROW('Editorial 2022'!S2:S)}, 
 "select max(Col2) where not Col1 = '♠' group by Col2 pivot Col1"),,9^9)), 
 "♠")), 2, 0), "♦"), "^\d+×", ))))

demo

note: gid can be found in URL address after you enter your tab named Editorial 2022

Return multiple addresses of a lookup result

2 Answers2

demo spreadsheet

update:

demo