Search for text

Hello !

I have to do a search on a dataset (about 3000 entries) and part of the exercise is to look in some project titles if there is a country or other key words included.

I have look on the web and unable to find a formula that works. Basically what I want to do is to have a formula a looks in the column of titles for a list of keywords on another sheet in the same book.

Does anyone knows a formula that could help me?

Thanks,

Milo

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

See attached...

First I created a Named Range to hold all the keywords that you might list in column B of Hoja3 sheet.

The Named Range is created via: Formulas tab, Define Name

Enter Name: keywords

Refers to: =OFFSET(Hoja3!$B$1,1,0,COUNTA(Hoja3!$B:$B)-1,1)

Then in the Projects Sheet, in C2:

=MAX($C$1:C1)+OR(ISNUMBER(SEARCH(keywords,B2)))

confirmed with CTRL+SHIFT+ENTER not just ENTER to get the { } brackets..

and copied down... this to count number of projects with keyword found within...

then in D2: =Max(C:C) to give total matched entries...

then in Hoja sheet: D2:

=IF(ROWS($A$2:$A2)>Projects!$D$2,"",INDEX(Projects!$B:$B,MATCH(ROWS($A$2:$A2),Projects!$C:$C,0)))

copied down as far as you want...

Now add/remove keywords and this list will expand/collapse appropriately.