Home > database >  Return multiple addresses of a lookup result
Return multiple addresses of a lookup result

Time:11-12

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.

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

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?

Example sheet: enter image description here

CodePudding user response:

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

enter image description here

demo spreadsheet

  • Related