# "If" statements

#### Lil~Bit

##### New Member
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.

### Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
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.

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.

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"))

You almost got it, if this is your desired result.

=IFERROR(IF(AND(C2<8999,SEARCH("Hello",G2)>0),500,C2),C2)

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"))

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),"")

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),"")

I copied your cell over, but it returns a blank. How can I attach what I have so you can view it?

Replies
5
Views
90
Replies
7
Views
314
Replies
18
Views
420
Replies
5
Views
165
Replies
6
Views
234

1,196,328
Messages
6,014,674
Members
441,835
Latest member
rthomas268

### 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.

### Which adblocker are you using?

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

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