Add a Condition to Existing Formula

[meppwc]

The formula below works perfectly. I am hoping to add one additional condition it that basically tells the formula to run or not to run at all.

The table in the formula below is used for lookup is in column A of the “Table” worksheet (cells A2 to A30.
On the “Table” worksheet, in column “X” if the word “No” appears, then the formula is not run. But if the cell in “X” is blank, then the formula runs as it currently does. So, the logic that I need to add is if a cell in “X” has the value of “No”, then do not run the formula.

I apologize if this is too wordy or is confusing. I tried my best to word it as accurately as possible.

=IF(OR(C200<1,A200=0),"",IFERROR(LOOKUP(2^15,SEARCH(Table!$A$2:$A$30,A200),Table!$A$2:$A$30),""))

 

[jtakw]

Hi,

Technically, you can't add a condition to Stop or make the formula not run, you can only add conditions and make a formula give you a certain result.
If I understand you correctly, can you just add that if Column X is "No", then return "" Blank to the beginning of your formula by adding another OR criteria?

=IF(OR(C200<1,A200=0,X200="No"),"",IFERROR(LOOKUP(2^15,SEARCH(Table!$A$2:$A$30,A200),Table!$A$2:$A$30),""))

 

[meppwc]

I do understand what you are saying. In looking at the addition to the formula that you have provided I believe there is an error...............the formula resides on worksheet "tblAvailableItems"...........the column "X" that I refer to is on worksheet page "Table".............the addition that you made (X200="No") seems that it is looking for the word "No" in column X of the tblAvailableItems worksheet and not the Tables worksheet

 

[jtakw]

Then just add the tab name:

=IF(OR(C200<1,A200=0,Table!X200="No"),"",IFERROR(LOOKUP(2^15,SEARCH(Table!$A$2:$A$30,A200),Table!$A$2:$A$30),""))

 

[uacdub]

It's a little hard to help you without actually seeing some sample data. One thing that stands out is where you have the IFERROR statement. If you put that there it's going to run regardless if the first OR conditions are not meet "IF(OR(C200<1,A200=0)" because it's placed where the IF statement = false.

You can nest IF statements so maybe that is the route you need to take.

Logic - =IF(OR(RULES,"TRUE","FALSE")

I.E. =IF(OR(RULES,"RESULT IF YES",IF(OR(RULES,"RESULT IF TRUE",IF(OR(RULES....

Not sure how much that helps but we can probably figure this out if you provide a little bit of a sample.

 

[meppwc]

I am sorry jtakw..........that did not work................and uacdub I understand about not seeing some sample data...............do I have the option to upload an excel spreadsheet to the forum?

 

[uacdub]

you can go into "Go Advanced" and create a sample table(s) and just put in fake data.

 

[meppwc]

Selected "Go Advanced" and still don't see an option to create a table..............I feel like such an idiot

 

[jtakw]

I am sorry jtakw..........that did not work................and uacdub I understand about not seeing some sample data...............do I have the option to upload an excel spreadsheet to the forum?

Sorry, don't think that the OR function supports 3D, try this:

=IF(Table!X200="No","",IF(OR(C200<1,A200=0),"",IFERROR(LOOKUP(2^15,SEARCH(Table!$A$2:$A$30,A200),Table!$A$2:$A$30),"")))

 

[uacdub]

Try this!

Link to Image