# 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?=IF((AND(G3="Red",B6<=250)),25,((IF((AND(G3="Red",B6<=500)),35,((IF((AND(G3="Red",B6<=1000)),40*((IF((AND(G3="Red",B6<=2500)),50*((IF((AND(G3="Red",B6>=2501)),75,""))))))))))))) See More: If and statement in Excel

#1 February 7, 2014 at 13:08:19
 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.=IF((AND(G3="Red",B6<=250)),25,((IF((AND(G3="Red",B6<=500)),35,((IF((AND(G3="Red",B6<=1000)),40,((IF((AND(G3="Red",B6<=2500)),50,((IF((AND(G3="Red",B6>=2501)),75,"")))))))))))))However, you have way too many parenthesis. Even though the formula works, it can be written in a shorter manner:=IF(AND(G3="Red",B6<=250),25,IF(AND(G3="Red",B6<=500),35,IF(AND(G3="Red",B6<=1000),40,IF(AND(G3="Red",B6<=2500),50,IF(AND(G3="Red",B6>=2501),75,"")))))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:=IF(G3="Red",VLOOKUP(B6,\$A\$1:\$B\$5,2,1),"")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.

Report •

#2
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 •

#3 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```=IF(G3<>”Red”,VLOOKUP(G3,\$C\$1:\$D\$3,2,0),VLOOKUP(B6,\$A\$1:\$B\$5,2,1))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.message edited by DerbyDad03

Report •

Related Solutions

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

Report • 