# Using the =IF(ISNUMBER(SEARCH function to search multiple cells...

This is a discussion on Using the =IF(ISNUMBER(SEARCH function to search multiple cells... within the Excel Questions forums, part of the Question Forums category; Hi, I' having trouble using the =IF(ISNUMBER(SEARCH formula to search multiple cells. I can get it to work to search ...

1. ## Using the =IF(ISNUMBER(SEARCH function to search multiple cells...

Hi,

I' having trouble using the =IF(ISNUMBER(SEARCH formula to search multiple cells.

I can get it to work to search one cell (as below):

=IF(ISNUMBER(SEARCH("Same",G10)),"No Change",'Aug 09 Matrix'!F10)

(In this case the respone is No Change as Cell G10 contains "Same")

However cannot get it to work for several cells. I have pasted below and highlighted the function I would like it to perform:

=IF(ISNUMBER(SEARCH("Same",G10:R10)),"No Change",'Aug 09 Matrix'!F10)

Essentially the other cells in that row (G10:R10) all contain "Same", however for some reason it is identifying this as FALSE and putting in the data from the 'Aug 09 Matrix' sheet.

Any idea?

Any help much appreciated!

Best regards

Nick

2. ## Re: Using the =IF(ISNUMBER(SEARCH function to search multiple cells...

Are you looking for?...

=IF(ISNUMBER(LOOKUP(9.99999999999999E+307,SEARCH("Same",G10:R10))),"No Change",'Aug 09 Matrix'!F10)

3. ## Re: Using the =IF(ISNUMBER(SEARCH function to search multiple cells...

try this
Code:
```
=IF(SUMPRODUCT(--(ISNUMBER(SEARCH("Same",G10:R10)))),"No Change",'Aug 09 Matrix'!F10)```

4. ## Re: Using the =IF(ISNUMBER(SEARCH function to search multiple cells...

I tried that but for the cell below where one of the cells contains 40 instead of "Same" it has come up with "No Change" again where it should be reading off the 'Aug 09 Matrix' sheet

Really appreciate the help though!

N

5. ## Re: Using the =IF(ISNUMBER(SEARCH function to search multiple cells...

Originally Posted by nickwood1066

I tried that but for the cell below where one of the cells contains 40 instead of "Same" it has come up with "No Change" again where it should be reading off the 'Aug 09 Matrix' sheet

Really appreciate the help though!

N
Care to state exactly what you are after in words, not in terms of formulas?

6. ## Re: Using the =IF(ISNUMBER(SEARCH function to search multiple cells...

Thanks Texasalynn,

I've tried that too but it doesn't work either....

Basically row G10:R10 Contains the Text "Same"

Cell G11 Contains 40, F11:R11 contain "Same"

As such I want the Formula Cells (F10 & F11) to either contain "No Change" (if all the cells to up to column R contain "Same"), or to import the data from 'Aug 09 Matrix' tab if any of the cells from Row G to Row R contain anything other than "Same".

BR

N

7. ## Re: Using the =IF(ISNUMBER(SEARCH function to search multiple cells...

the formula

=IF(ISNUMBER(SEARCH("Same",G10:R10)), "No Change",'Aug 09 Matrix'!F10)

will only look at the first cell G10

do you want all the cells to have "same"

then

Code:
`=if(sum(ISNUMBER(SEARCH("Same",G10:R10)))=columns(G10:R10)," "No Change",'Aug 09 Matrix'!F10)`
confirmed with control shift enter
should work

8. ## Re: Using the =IF(ISNUMBER(SEARCH function to search multiple cells...

Originally Posted by nickwood1066
Thanks Texasalynn,

I've tried that too but it doesn't work either....

Basically row G10:R10 Contains the Text "Same"

Cell G11 Contains 40, F11:R11 contain "Same"

As such I want the Formula Cells (F10 & F11) to either contain "No Change" (if all the cells to up to column R contain "Same"), or to import the data from 'Aug 09 Matrix' tab if any of the cells from Row G to Row R contain anything other than "Same".

BR

N
=IF(COUNTIF(G10:R10,"Same")=COLUMNS(G10:R10),"No Change",'Aug 09 Matrix'!F10)

9. ## Re: Using the =IF(ISNUMBER(SEARCH function to search multiple cells...

I had gotten the impression that there might be other things in the cell than "Same"
if there is

=IF(COUNTIF(G10:R10,"*Same*")=COLUMNS(G10:R10),"No Change",'Aug 09 Matrix'!F10)

10. ## Re: Using the =IF(ISNUMBER(SEARCH function to search multiple cells...

WOW!

It worked thank you so much! Been trying to figure it out for ages!

Really appreciate you're help!

Have a fantastic day

Best Regards

N

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•