IF(count(search) Function not working when search from text from a cell

**joshnathan** · 10-09-2013, 04:32 AM

Hello,
I'm trying to get this formula to work but i cant get it to

=IF(COUNT(SEARCH(H2,I2, 'Data Sheet'!C3)), 1, "")

If i replace H2 and I2 with the words i want to serach it works but it wont work with the cell names there.

IF this can be ammended is there a way to do it with multiple searches
For example some times i might want to search for the contents of cell H2, I2 and J2 and other times H2, I2, J2 and K2. Can i just keep all these values in the formula and fill them in as needed or will the blanks cause me problems?

**RobertMika** · 10-09-2013, 04:42 AM

search.xlsx
Try like this

**joshnathan** · 10-09-2013, 04:50 AM

Hi thanks for the response and an example. Although i'm a little confused. at the moment irespective of whats in the list i get a 0 value. even if the terms dont match up. What should i be receiving?

**RobertMika** · 10-09-2013, 04:52 AM

If the term do not match you have 0- I assume that's correct?(no matching terms)
What would you like to recive?
State your outcome or post a sample workbook

**joshnathan** · 10-09-2013, 04:58 AM

Example Book.xls

If you look at this book using your formula
Basically in Cell C3 on search it should have a 1 i assume to say the search terms have been found. If you look at the coresponding cells in Data Sheet you can see that C3 does have both search terms in it.
Thanks for the help

**RobertMika** · 10-09-2013, 05:03 AM

Replace your list in Name Manger with this
=OFFSET(Search!$H$2,,,,COUNTA(Search!$H$2:$M$2))
and confirm the formula in C6 with Control+Shift+Enter
To return "" instead of 0
=IF(SUM(--ISNUMBER(SEARCH(List, 'Data Sheet'!C3)))=0,"",SUM(--ISNUMBER(SEARCH(List, 'Data Sheet'!C3))))
or use Custom Format
0;-0;;@

**joshnathan** · 10-09-2013, 05:05 AM

Thank you very much,
The number that appears in the cell, what does it represent?

**RobertMika** · 10-09-2013, 05:06 AM

Which number?

Please add reputation if you are happy with the solution

**joshnathan** · 10-09-2013, 05:10 AM

Sorry should have clarified the number that comes up in cel c6. it seems to go down from 6 the smaller the number of matched search terms

**RobertMika** · 10-09-2013, 05:13 AM

Example Book.xls
Not sure but you do not have any data yet in the Data tab.
Please see attached

IF(count(search) Function not working when search from text from a cell

LinkBack

Thread Tools

Rate This Thread

Display

IF(count(search) Function not working when search from text from a cell

Re: IF(count(search) Function not working when search from text from a cell

Re: IF(count(search) Function not working when search from text from a cell

Re: IF(count(search) Function not working when search from text from a cell

Re: IF(count(search) Function not working when search from text from a cell

Re: IF(count(search) Function not working when search from text from a cell

Re: IF(count(search) Function not working when search from text from a cell

Re: IF(count(search) Function not working when search from text from a cell

Re: IF(count(search) Function not working when search from text from a cell

Re: IF(count(search) Function not working when search from text from a cell

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Search function to search if worksheet contains specified text

[SOLVED] search for text, then search for previous text, replace 1st text including number from 2nd

Function to Search COUNT

Search Text In A Cell + If Function

Function in a cell to search for a text

Bookmarks

Bookmarks

Posting Permissions