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


See More: Nested if/and inside of Lookup

Report •


#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","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?


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


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
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 if F2 is over 599

Note that you said F2. 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 590 in your formula but earlier you used 599 in 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.


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


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 letters

I 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 •

Ask Question