If cell contains certain text dont return vaule of other cell

[Novman]

Using this fourmla I get what I'm looking for..
=if(isnumber(search("light blue",C3)),D3)*isnumber(search(B3,"CAR")) I get the value of yellow cars from cell D3
What I can't get is, if cell C3 has part text as the word "blue" and B3 "CAR" then DONT return value of cell D3
Basically if cell C3 has word blue then DONT return value of cellD3.

 

[Fluff]

Hi & welcome to MrExcel.
That formula will not return the value of D3 if C3 is blue.

 

[Novman]

Hi & welcome to MrExcel.
That formula will not return the value of D3 if C3 is blue.

The fourmla I have posted won't return cell D3. Can I upload a sheet here to make things easier.

 

[Fluff]

The fourmla I have posted won't return cell D3

But that's what you said shouldn't happen?

Can I upload a sheet here to make things easier.

No, but you can use the XL2BB add-in

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[Novman]

But that's what you said shouldn't happen?


No, but you can use the XL2BB add-in

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Yes sorry think I'm complicating things..
I hope the pitcher iv attached can help.
Thanks for you patience so far

 

[Fluff]

How about

=IF(AND(B3="car",ISERROR(SEARCH("blue",C3))),D3,"")

 

[snjpverma]

Try this

=IF(ISERROR(SEARCH("blue",B2)),C2,"")

 

[Novman]

How about

=IF(AND(B3="car",ISERROR(SEARCH("blue",C3))),D3,"")

Thank you for you patience and help, that fourmla worked great.

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[Novman]

Glad we could help & thanks for the feedback.

Fluff.... are you able to help me alittle more... tried making a new post but keep saying file to big... it based on this same file