IF Statement

[Ambassador]

Cell A64 is a pulldown list for 4 contractors. Depending on the selection is
want Cell G42 to fill in the contract number for the contractor in A64. I
have
=(if(a64="LOT I",1300123722,if(a64="LOT II",130012394,if(a64="LOT
III,1300127987,if(a64="LOT IV",130012453,"")))

If LOT I is selected the formula works, but not the rest, it's just blank. I
know i'm messing up in the TRUE,FALSE area but I can't figure it out.

Another area of the same sheet I use a VLOOKUP but need to change the range
of the LOOKUP based on the above contractor i.e. LOT I will use range
CLINS!A228 whereas LOT II will use range CLINS!A2994 etc...
Here I try:
=IF(A53<>"",IF(A64="LOT
I",VLOOKUP(A53,CLINS!$A$2:$D$28,2,FALSE),"")+IF(A64="LOT
II",VLOOKUP(A53,CLINS!$A$29:$D$94,2,FALSE),"")

This one works if i separate them however together when I try LOT II i get a
#VALUE! error. Obviously once I get this running I'll add on the other
ranges for the other contractors....it has to be in the "+" symbol but I've
tried the "&" and imbedding the second IF within the first but you can see a
common problem I'm not figuring out - nested IF's....

If(someone, anyone, can help) I would appreciate some guidance....

amb

 

[Rick Rothstein]

The first formula you posted seems to be missing a quote mark and a closing
parenthesis. After putting them in, the formula appears to work fine...

=(IF(A64="LOT I",1300123722,IF(A64="LOT II",130012394,
IF(A64="LOT III",1300127987,IF(A64="LOT IV",130012453,"")))))

 

[Mike Josephson]

Thanks, I also figured out the basics for the second formula - a nested IF
using VLOOKUP on different ranges based on the IF statement....if anyone
would ever need this:

=(IF(D2="lot1",VLOOKUP(D1,Sheet2!A1:B4,2,FALSE),IF(D2="lot2",VLOOKUP(D1,Sheet2!A5:B7,2,FALSE),"")))

Thanks for pointing out the error of my ways....

amb