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

Status
Not open for further replies.

nickwood1066

Board Regular
Joined
Aug 21, 2009
Messages
55
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
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
Are you looking for?...

=IF(ISNUMBER(LOOKUP(9.99999999999999E+307,SEARCH("Same",G10:R10))),"No Change",'Aug 09 Matrix'!F10)
 
Upvote 0
try this
Code:
=IF(SUMPRODUCT(--(ISNUMBER(SEARCH("Same",G10:R10)))),"No Change",'Aug 09 Matrix'!F10)
 
Upvote 0
Thanks for the quick reply!

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
 
Upvote 0
Thanks for the quick reply!

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?
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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)
 
Upvote 0
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)
 
Upvote 0
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
 
Upvote 0
Status
Not open for further replies.

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top