CODEWITHSUNDEEP
excelfunction
Joel Bournival
Joel Bournival

Reputation: 25

Issues with Excel And/OR Multiple IFs

Column F checks my "database"(Sheet1) and tells me if there's a match from current sheet Column G. 

=VLOOKUP(ShippingPlanPREP!C2,MainDatabase!A:G,E2,FALSE)

I get a return of either a Price, a Zero or an #N/A error. At the moment I don't care about the price, because it means I have the unit and needed condition in Sheet1 "MainDatabase".

So I have basically two different directions I'd like to go;

Adding a new Unit (#N/A error) And condition's price

OR

Adding a new Unit Condition price (0 was returned)

Here's where my problem is I believe:

Column I,J and K represent different Conditions/prices and need to tell me, based off Column F, to either look for the already known condition/price in the database, look to see if this item condition is even being updated, or add the price (from Column C) because the needed criteria match.

Code I've tried: 

=IF(AND(AND($F2=0,(ISNA($F2)),ShippingPlanPREP!$B2="A")),$C2,VLOOKUP($G2,MainDatabase!A:E,3,FALSE))

I tried AND AND with some success. I'm getting Database numbers now, But I'm not getting C2's price in "Amazon_A" etc when it needs to be there.

=IF(AND(OR($F2=0,(ISNA($F2)),ShippingPlanPREP!$B2="A")),$C2,VLOOKUP($G2,MainDatabase!A:E,3,FALSE))

I don't think OR is the answer because then I ended up with the same Condition Price on all category's/columns.

I've tried much more variations than this without success. Please let me know if more info is needed. Thank you in advance.

Example

Upvotes: 0

Views: 167

Answers (1)

Forward Ed
Forward Ed

Reputation: 9874

Let see if breaking it down to simpler terms will help with the logic process and getting what you want done.

Value of F | Process to follow
------------------------------
#NA        |       A
0          |       B
$42.42     |       C

Going the if route, we would want to use a formula like this:

=IF(isNA(F2), "A" , IF(F2=0,"B","C"))

You should be able to toss that into your sheet and test that you are getting your three letters. When that is working, substitue your equations for what you want to happen in for each of the letters. Make sure the individual equations are working first.

So to help clarify your equations lets look at that table again.

Value of F | Process to follow
------------------------------
#NA        | "What to do when F is #NA"
0          | $C2,VLOOKUP($G2,MainDatabase!A:E,3,FALSE)
$42.42     | "What to do when F is a price"

That is one formula. If I place it there it means that is the action to take when F is equal to 0. I only saw one formula, so the question now it what formula or action do you want to take for the other two results. Now I may have substituted your formula in for the wrong F value, but you can adjust that to suit your needs. If I were to substitute your formula into the previous if statement it would look something like this:

=IF(isNA(F2),"What to do when F is #NA",IF(F2=0,$C2,VLOOKUP($G2,MainDatabase!A:E,3,FALSE),"What to do when F is a price"))

Supplemental Information

If the logic table above is over simplified and you have other conditions that must be true before and action is taken, you can add columns before the action is taken column to build up your thought process. IF that is the case, update your question with your logic table, and we can help you out with building IF statements with AND and OR as needed. The main thing when building your table to help keep things clearer for you is to treat each row as unique in terms of what much be true in order for your process to happen.

Example

Value of F | Value of another cell | Process to follow
------------------------------------------------------
#NA        |  True                 |     A
0          |  True                 |     B
$42.42     |  True                 |     C
#NA        |  False                |     A
0          |  False                |     C
$42.42     |  False                |     B

This is just one of many approaches.

Upvotes: 1

Related Questions