Forum Discussion

Jovana Kujundzic's avatar
Jovana Kujundzic
Copper Contributor
Jul 04, 2018

How to see all the value for a particular text

Hi, 

I have a sheet with couple of tabs and would like to know how many times I have visited a city in one year but I need all the dates listed. Like if I put Geneva I would like to have a formula that finds dates for this city. In attach excel file and here the screenshot

 

Thanks in advance for the help!

 

 

Formulas & Functions
tips and tricks

2 Replies

  • John Jairo Vergara Domínguez's avatar
    John Jairo Vergara Domínguez
    Brass Contributor
    Jul 04, 2018

    Hi, Jovana Kujundzic!

     

    You could try this:

     

    [F2] : =COUNTIF($D$2:$D$32,"*"&F3&"*")

    And drag it right.

    [F4] : =IF(ROWS(F$4:F4)>F$2,"",INDEX($A$2:$A$32,AGGREGATE(15,6,ROW($D$2:$D$32)-ROW($D$1)/(SEARCH(F$3,$D$2:$D$32)>0),ROWS(F$4:F4))))

    And drag it right and down.

     

    Check file with the formulas.  Blessings!