IF statement in conditional formatting

[MurphDog]

Trying to apply some conditional formatting to M6:M20. What am I doing wrong here? The cell values are dates

This sample formula works in cells
=IF($M6>(VLOOKUP($G6,Table1,5,0)),"Pass","Fail")

Thought I could use this to turn the font red if the date in M6 (format mmm-yy) is greater than the corresponding date in Table1 Column 5 (date format mm/dd/yyyy)

=$M6>(VLOOKUP($G6,Table1,5,0))

 

[jasonb75]

The formula is correct, the only thing that I can think of is that you're applying it to the range incorrectly. Make sure that you select the range to apply it to by selecting M6 first, then drag down. If you select M20 first and drag up then the rows will be out of sync.

 

[MurphDog]

Going from top to bottom. Nogo

One thing I've noticed is that if I use the actual table name, i get the error saying "there's a problem with the formula. Not trying to type a formula?". If i use the fake table name (Table1 in this case). I don't get the error, just doesn't work of course. The table is on a different sheet and I could use an indirect function to look up the sheet and use the cell range, but the table name seems a bit easier. I'll see what happens if I try the cell range a bit later when i have a chance.

 

[Fluff]

You cannot use table names/columns in CF formulae, you have to use the actual cell references.

 

[jasonb75]

You cannot use table names/columns in CF formulae, you have to use the actual cell references.

That can be circumvented with a named range that refers to the table.

The original question didn't specify in what way it was not working so I had incorrectly assumed that Table1 was a named range not a table name.

 

[Fluff]

That can be circumvented with a named range that refers to the table.

Good point & better than using the range references.

 

[MurphDog]

OK, got the error out of the way. Thank you! The only current issue I see is something I didn't foresee previously. If the lookup value on sheet1 is blank, then do nothing

=$M6<(VLOOKUP($G6,Sheet1!$B$4:$F$19,5,0))

So I'm currently trying to figure out if a OR or And function would help in the same formula.

 

[jasonb75]

If the lookup cell was empty then the formula should return false and not apply formatting. This change should allow for formula blanks which would be greater than any number / date.

=$M6<N(VLOOKUP($G6,Sheet1!$B$4:$F$19,5,0))

 

[MurphDog]

These are the 3 things i want to happen.
if m6 > vlookup , then turn red
if m6 < vlookup, then do not format
if vlookup = blank, then do not format

 

[MurphDog]

Giving that a try Jason