# Search and If/Or

#### calliemulv

##### New Member
Hello!

I need help with an automation I am trying to build through a formula. What I want to happen is in R3, the formula would look to Q3 as the first step. If Q3 says "GIA," then run formula 1; If Q3 says "COA," then look to F3. If F3 says "Off" in the field at all, run formula 2; If Q3 says "COA," then look to F3. If F3 says "Parents" in the field at all, run formula 3; If Q3 says "COA," then look to F3. If F3 does not say "Off" or "Parents," then run formula 4.

Basically:
R3 = If Q3 is GIA, "ROUND((P3+S3)/F3,2);" if Q3 is COA, look for Off in F3, if found "ROUND((P3+S3)/(F3+2982),2)" OR look for Parents in F3, if found "ROUND((P3+S3)/(F3+2838),2)" OR "ROUND((P3+S3)/(F3+2568),2)"

Formula 1 (GIA): ROUND((P3+S3)/F3,2)
Formula 2 (COA + Off): ROUND((P3+S3)/(F3+2982),2)
Formula 3 (COA + Parents): ROUND((P3+S3)/(F3+2838),2)
Formula 4 (COA + not "Off" or "Parents"): ROUND((P3+S3)/(F3+2568),2)

I have attached the spreadsheet I'm working with.

Thank you!

### Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Hi,

Do you mean E3 rather than F3, cause F3 is a Number.

Oops - yes, that's correct: E3

Hi,

Do you mean E3 rather than F3, cause F3 is a Number.

Thanks for clarifying.

Try either one of the 2 formulas below, they're essentially the same, just written differently.

Since some of your "drop down" list is from an external source, my testing was limited.

=ROUND((P3+S3)/(F3+IF(Q3="GIA",0,IF(ISNUMBER(SEARCH("Off",E3)),2982,IF(ISNUMBER(SEARCH("Parents",E3)),2838,2568)))),2)

=ROUND((P3+S3)/(F3+IF(Q3="GIA",0,IF(COUNTIF(E3,"*Off*"),2982,IF(COUNTIF(E3,"*Parents*"),2838,2568)))),2)

Last edited:
This is perfect - thank you!!

Thanks for clarifying.

Try either one of the 2 formulas below, they're essentially the same, just written differently.

Since some of your "drop down" list is from an external source, my testing was limited.

=ROUND((P3+S3)/(F3+IF(Q3="GIA",0,IF(ISNUMBER(SEARCH("Off",E3)),2982,IF(ISNUMBER(SEARCH("Parents",E3)),2838,2568)))),2)

=ROUND((P3+S3)/(F3+IF(Q3="GIA",0,IF(COUNTIF(E3,"*Off*"),2982,IF(COUNTIF(E3,"*Parents*"),2838,2568)))),2)

You're welcome, welcome to the forum.

Replies
8
Views
449
Replies
1
Views
186
Replies
2
Views
251
Replies
5
Views
223
Replies
0
Views
203

1,207,108
Messages
6,076,590
Members
446,215
Latest member
userds5593

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

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