Solved If and statement in Excel

February 7, 2014 at 10:23:33
Specs: Windows 7
I am trying to get Excel to evaluate if g3 is the "Red" then evaluate b6 to determine the price. It works until I get to 501. For some reason Excel wants the * but I do not know why. Any suggestions?

See More: If and statement in Excel

Report •

February 7, 2014 at 13:08:19
✔ Best Answer
First, I'll discuss your formula, then I'll suggested a method using VLOOKUP that might be more efficient.

I copied your formula into a spreadsheet. I put Red in G3 and started entering values in B6.

Starting at 501, I get a #VALUE error which is what I would have expected. The * is the multiplication operator and Excel can't figure out what to multiply by 40.

As soon as I changed the *'s to commas, the formula works fine.


However, you have way too many parenthesis. Even though the formula works, it can be written in a shorter manner:


That said, you might want to consider a VLOOKUP formula instead of a Nested IF. By using VLOOKUP and a lookup table for your quantity/price relationships, you can enter your values in the table and never have to touch the formula when the criteria change.

For example, if you set up a table like this...

       A        B
1       0      25
2     251      35
3     501      40
4    1001      50
5    2501      75

... then you can use this formula:


The range_lookup argument of "1" means that VLOOKUP will find the value that is equal to or less than the value in B6 and return the corresponding price.

e.g. When B6 = 752, 40 will be returned because 752 isn't found in the table so VLOOKUP uses the next value that is less than 752 which is 501.

As I mentioned earlier, when the price or quantity criteria changes, all you have to do is change the table and not mess with a cumbersome Nested IF.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

February 7, 2014 at 13:43:34
Yes, I realized the stupid comma after I posted. I am however going to work on the vlookup. I have one already but the all other results in g3 are set costs-not varying based on quantity. I think that I should be able to add a vlookup within a vlookup but couldn't figure out exactly how. Thank you so much for your help. It was driving me crazy!

Report •

February 8, 2014 at 05:11:02
How about this:

Using the same example lookup_table as I used above, create another lookup_table for the other items in G3 with the item and the price. Then try something like this (untested)...

       C       D
1    Blue      10
2    Green     20
3    Tan       30


This will lookup the non-Red items in the new table and return its fixed price. For Red items it will use the varying price lookup_table.

Note that the range_lookup argument is 0 (FALSE) for the first VLOOKUP because you are looking for an exact match.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03

Report •

Related Solutions

February 10, 2014 at 06:03:15
That did it. You are wonderful. Thank you so much for your help!

Report •

Ask Question