Nested if/and inside of Lookup

Microsoft Excel 2003 (full product)
June 16, 2010 at 09:16:24
Specs: Windows XP
 =LOOKUP(D2,{"CT","NJ","NY","PA"},{75,25,125,100})I am trying to nest the following If statement inside of where the value 125 is above:=IF(AND(D2="NY",F2>499),150,125)This is what I tried but it did not work:=LOOKUP(D2,{"CT","NJ","NY","PA"},{75,25,IF(AND(D2="NY",F2>499),150,125,100}))All I am trying to do is change the value of NY with an if statement to 150 if F2 is over 499, or 125 if its not

See More: Nested if/and inside of Lookup

#1
June 16, 2010 at 09:27:12
 Why not just test for your "special case" and then do the LOOKUP if the "special case" is false?=IF(AND(D2="NY",F2>499),150,LOOKUP(D2,{"CT","NJ","NY","PA"},{75,25,125,100}))

Report •

#2
June 16, 2010 at 09:35:05
 I didn't know I could do it that way actually. Thanks!

Report •

#3
June 16, 2010 at 10:04:30
 Well, now you do! Have fun!

Report •

Related Solutions

#4
June 25, 2010 at 10:52:59
 =IF(AND(D4="NY",F4>499),150,LOOKUP(D4,{"CT","NJ","NY","PA"},{75,25,125,100}))I am trying to make the above formula have an or statement (I believe thats what I need), is to change the value of NY with to 175 if F2 is over 599. Would I need to add in an or statement, or add on to what I have?

Report •

#5
June 25, 2010 at 11:08:43
 Didn't I cover this in my earlier response?All you need to do is test for your special case(s) first and if it's (or they're) not true, proceed with your LOOKUP.

Report •

#6
June 25, 2010 at 11:23:48
 I understand what you are saying, but I am very new to Excel functions:=IF(AND(D41="NY",F41>499),150,IF(F41>599,175,LOOKUP(D41,{"CT","NJ","NY","PA"},{75,25,125,100})))This comes up with the incorrect amount. I am sure I dont need an OR statement, but then what do I need? Am I even close to what you said?

Report •

#7
June 25, 2010 at 11:44:22
 I also tried this:=IF(AND(D94="NY",F94>499),150,AND(D94="NY",F94>590,175,LOOKUP(D94,{"CT","NJ","NY","PA"},{75,25,125,100})))

Report •

#8
June 25, 2010 at 11:46:02
 Yes, I think you are close, but keep in mind that an IF statement follows a logical left to right sequence. Once it finds something that is TRUE, it stops checking.What is the very first thing your IF statement checks?

Report •

#9
June 25, 2010 at 12:04:35

Report •

#10
June 25, 2010 at 13:13:14
 I am using the same formula, changing the cell numbers based on which row it is in. With that being said it does not matter which number is after the letter, as long as it corresponds to the other letters:=IF(AND(D94="NY",F94>499),150,AND(D94="NY",F94>590,175,LOOKUP(D94,{"CT","NJ","NY","PA"},{75,25,125,100})))F94 can be higher than 590 (which is what I lowered the formula to, instead of 599. Can you please let me know why if F94 is 700, it would not show 175?

Report •

#11
June 25, 2010 at 13:57:20
 re: With that being said it does not matter which number is after the letter, as long as it corresponds to the other lettersI know that, which is why I mentioned that when you used F2 and F4 in the same post (Response #4) you confuse the reader. re: Can you please let me know why if F94 is 700, it would not show 175?I already answered that question in Response # 8. What is the first thing your IF statement checks?It checks whether Fx is greater than 499, right?Is 700 > 499? Where I come from it is, therefore the first IF condition is TRUE and the formula is going to return the value_if_true for that condition, which is 150.It will not check anything else because it has already found a TRUE condition. An IF statement does not check all of the conditions and then make a decision, instead it checks the conditions, 1 by 1, from left to right. It returns the value for the first TRUE condition it finds and then stops.Rearrange your IF so it checks for the largest value first and you should be all set.Note: Also make sure you close all of your AND functions with a parenthesis. You've left one open, but that is not why you aren't getting the answer you expect.

Report •

#12
June 25, 2010 at 14:16:33
 I now understand what you are saying better.=IF(AND(D2="NY",F2>590,175),AND(D2="NY",F2>499,150,LOOKUP(D2,{"CT","NJ","NY","PA"},{75,25,125,100})))Is returning false, because D2 contains NJ. Do I have to change the parenthesis to nest the lookup function better?

Report •

#13
June 25, 2010 at 15:42:18
 Look up the syntax for the AND function in the Excel Help files.I said it in Response 11 and I'll say it again. You need to close your AND functions if you expect them to work correctly.Ask yourself this question (I already know the answer)Are 175 and 150 arguments for the AND function or value_if_true results for the IF functions?Look at the difference between the formula I posted in Response 1 and the one you posted in Response 12. Specifically, look at the AND function - after you look it up in the Excel Help files and understand the syntax.

Report •