# Multiple IF statements in a Conditional Forma

December 16, 2010 at 23:08:20
Specs: Windows XP
 I need some help on getting this correct. I have a tracker/scheduler that shows a persons R&R dates in a line. Depending on the R&R the cells for those dates will show 1,2 or 3 (using a formula) This was orginally set up to where it would only show one R&R at a time but now I need to change it to where it will show all 3 R&Rs. The conditional formatting that I am needing to do will highlight the cell based on wheater or not the R&R has been approved. I already have a true/false table created. The problem that I am running into is getting the conditional format to determine what to look at in the table based in the cell value. This is what I have and it did not reject the formula but it is not doing anything.IF((E4)="",0,IF((E4)=1,(VLOOKUP(\$B\$4,\$B\$100:\$E\$120,4,FALSE)="0"),IF((E4)=2,(VLOOKUP(\$B\$4,\$B\$100:\$E\$120,6,FALSE)="0"),IF((E4)=3,(VLOOKUP(\$B\$4,\$B\$100:\$E\$120,8,FALSE)="0")))))When this was setup to only show the 1 R&R date the formula that was I used was this and it worked beautifullyIF((E4)="",0,(VLOOKUP(\$B\$4,\$B\$100:\$E\$120,4,FALSE)="0")So if anyone can help me out on this I will greatly appriciate it and thank you in advance.

See More: Multiple IF statements in a Conditional Forma

#1
December 17, 2010 at 08:18:46
 Not sure how your sheet is set up, but your =VLOOKUP() won't work.The syntax of =VLOOKUP() is:=VLOOKUP(ItemToFind,RangeToLookIn,ColumnToPickFrom,SortedOrUnsorted)Your Column to Pick From is going to error out.Your Range to Look In starts in Column B and ends at Column EYour first =VLOOKUP() counts 4 columns starting at B, so it ends at column Ebut all your other's count 6 and 8 columns which is outside the range you specified.You can try changing the ranges to 4. Something like:=IF(E4="",0,IF(E4=1,VLOOKUP(\$B\$4,\$B\$100:\$E\$120,4,FALSE),IF(E4=2,VLOOKUP(\$B\$4,\$B\$100:\$E\$120,4,FALSE),IF(E4=3,VLOOKUP(\$B\$4,\$B\$100:\$E\$120,4,FALSE)))))MIKEhttp://www.skeptic.com/

Report •

#2
December 17, 2010 at 09:33:08
 Based on this statement:Depending on the R&R the cells for those dates will show 1,2 or 3 It seems like Mike's formula could be reduced to:=IF(E4="",0,IF(E4<4,VLOOKUP(\$B\$4,\$B\$100:\$E\$120,4,FALSE)))Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

#3
December 17, 2010 at 09:46:13
 Ya know, maybe I'm missing something in what you are trying to do, but a Conditional Formatting formula is supposed to return either TRUE or FALSE.The formulas in this thread all return values from a lookup_array.You said: I already have a true/false table createdWhy?Why aren't you using a CF Formula that returns TRUE or FALSE directly within the CF Wizard?Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

Related Solutions

#4
December 17, 2010 at 09:48:51
 I believe that's my error, he was forcing a true/false with his original formula:IF((E4)="",0,(VLOOKUP(\$B\$4,\$B\$100:\$E\$120,4,FALSE)="0")MIKEhttp://www.skeptic.com/

Report •

#5
December 17, 2010 at 13:25:58

Report •

#6
December 18, 2010 at 16:12:00
 Try IIF( do this, else do this)IIF is called an Inline IF. The comma separates the true from the false.

Report •

#7
December 18, 2010 at 17:10:22
 Access2000wiz:I assume by your user name that you are familiar with Access.While IIF may be a valid Access function, it is not valid in Excel.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

#8
December 20, 2010 at 23:47:44
 You are correct I did make that mistake my range was to short I originally have 3 tables and I did not make the adjustment to range. I corrected the range but it still is not working correctly. When I had this set up with just 1 table and only showing 1 R&R at a time the formula worked great but now I have to show 3 R&Rs at a time but just cannot get the formula to work correctly. The Found it got it corrected now. Below is the correct formula.IF((E4)="",0,IF((E4)="1",(VLOOKUP(\$B\$4,\$B\$100:\$K\$120,4,FALSE)="1"),IF((E4)="2",(VLOOKUP(\$B\$4,\$B\$100:\$K\$120,7,FALSE)="1"),IF((E4)="3",(VLOOKUP(\$B\$4,\$B\$100:\$K\$120,10,FALSE)="1")))))Thank you everyone who replyed back with out your help and extra set of eyes I probably would not have figured out the multiple mistakes that I had in the formula I posted. Thank you all and have a Merry Christams and a Happy New Years

Report •

#9
December 21, 2010 at 04:22:08
 2 items:1 - You don't need the parenthesis around the E4's.2 - Why do you have double quotes around all of your numbers? Double quotes tells Excel that the values are text values, not numbers.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •