=LOOKUP(D2,{"CT","NJ","NY","PA"},{75,25,1

25,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

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}))

I didn't know I could do it that way actually. Thanks!

Well, now you do! Have fun!

=IF(AND(D4="NY",F4>499),150,LOOKUP(D4,{"CT","NJ","NY","P

A"},{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?

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.

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(D4

1,{"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?

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})))

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?

Now, because my user name contains "Dad", I'm going to offer some fatherly advice. Please accept this criticism in the spirit it is intended. In Response 4, you included a formula that references D4 and F4. Then you said that you wanted to

change the value of NY with to 175 ifF2is over 599Note that you said F

2. I assumed that that was a typo, and that you really meant F4, but I could wrong. If I don't make that assumption I might offer a solution that doesn't fit your needs and also confuses the issue.In Response 7 you used

590in your formula but earlier you used599in your question. Another typo? I don't know...maybe you've changed your requirements.My point here is that if you are going to ask questions in this (or any) forum, you need to proofread your posts and make sure that you haven't confused us poor readers by changing values, cell references. etc.

We can't see your spreadsheet from where we're sitting nor can we read minds, so all we can do is assume that what you tell us is what you want.

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,1

75,

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?

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

andF4 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_truefor 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

allof 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

notwhy you aren't getting the answer you expect.

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?

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

yourselfthis question (I already know the answer)Are 175 and 150 arguments for the AND function or

value_if_trueresults 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.

Ask Your Question

Weekly Poll

Do you think ride and car sharing are the future of transportation?

Discuss in The Lounge

Poll History