Search code examples
excelrow

excel get in one cell all the cells containing specific text

In excel, i would like to see in only one cell a list of all the cells location which contains specific text, is that feasible?

For example: I want to see in only one cell, A7, the location of all cells containing "FALSE" value: A2, B3

enter image description here

To get the location, i think i can use the formula IF(ISERROR(A1:B4),ROW()) but i get a table as a result instead of all the values in one cell.

Solution

  • Final formula:

    (Replace semicolon with comma for your Excel version)

    =SUBSTITUTE(SUBSTITUTE(CONCAT(IF($A$1:$B$5=FALSE;ADDRESS(ROW($A$1:$B$5);COLUMN($A$1:$B$5));"")&", ");"$";"");", , ";"")

    enter image description here