"If" statements

Lil~Bit

New Member
Joined
Dec 30, 2013
Messages
18
Another one for all you wonderful people on here!

I have a spreadsheet that has multiple columns and rows of data, which I guess is the point of a spreadsheet. However, I need one column to do and return specific things.

In column B, I would like the following to happen.

If column C is less than 8999 and column G contains the word "Hello" anywhere in the cell, return the value "500". If column C is greater than 8999 return the value in column C.

Is that possible? I can get each one to work individually and all is fine. I have tried nesting these functions, but I cannot get the entire thing to work.

Thanks in advance!
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
I think you have a another possiblity. What happens if column C is less than 8999 and column G does not have "hello"? What do you want to return? Or is that scenerio not a possibility.
 
Upvote 0
Having column C returned would be optimal. This is what I have so far and I cannot figure it out.

=IF(AND(SEARCH("hello",G2),C2<8999),"500",C2).


I think you have a another possiblity. What happens if column C is less than 8999 and column G does not have "hello"? What do you want to return? Or is that scenerio not a possibility.
 
Upvote 0
Hi
try this, you will have an issue with less / greater than 8999, because it will never give a result, so i have change the 2nd part to 8998, if there are no match's to your criteria, it will return no match
=IF(AND(C1<8999,IFERROR(FIND("hello",G1,1),0)),500,IF(C1>8998,C1,"no match"))
 
Upvote 0
You almost got it, if this is your desired result.

=IFERROR(IF(AND(C2<8999,SEARCH("Hello",G2)>0),500,C2),C2)
 
Upvote 0
Thanks. I changed what I was specifically looking for to avoid confusing the answerers and now I am a bit confused.

Let me do it this way (with what I need exactly)

In cell B2, if C2 is less than 9000 and cell G2 contains the word Boyne somewhere, return 631. If cell G2 does not contain Boyne anywhere or if C2 is greater than 8999, return cell C2.

LOL! Should have done that in the beginning. Not sure if that is more clear or not. Thanks for your help!


Hi
try this, you will have an issue with less / greater than 8999, because it will never give a result, so i have change the 2nd part to 8998, if there are no match's to your criteria, it will return no match
=IF(AND(C1<8999,IFERROR(FIND("hello",G1,1),0)),500,IF(C1>8998,C1,"no match"))
 
Upvote 0
Thanks. I changed what I was specifically looking for to avoid confusing the answerers and now I am a bit confused.

Let me do it this way (with what I need exactly)

In cell B2, if C2 is less than 9000 and cell G2 contains the word Boyne somewhere, return 631. If cell G2 does not contain Boyne anywhere or if C2 is greater than 8999, return cell C2.

LOL! Should have done that in the beginning. Not sure if that is more clear or not. Thanks for your help!

Looks like...

=IF(ISNUMBER(C2),IF(C2 < 9000,IF(ISNUMBER(SEARCH("Boyner",G2)),631,C2),C2),"")
 
Upvote 0
Thanks!
That is getting better results for every cell except the ones that contain Boyne (yes, I did change the Boyner ... lol). It is still not returning 631 where G2 contains Boyne and C2 is less than 9000. The other part works great!


Looks like...

=IF(ISNUMBER(C2),IF(C2 < 9000,IF(ISNUMBER(SEARCH("Boyner",G2)),631,C2),C2),"")
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,218
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