IF statement in conditional formatting

MurphDog

Board Regular
Joined
Oct 1, 2015
Messages
54
Office Version
  1. 365
Platform
  1. Windows
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))
 
Last edited:

Some videos you may like

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,942
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Oct 1, 2015
Messages
54
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,960
Office Version
  1. 365
Platform
  1. Windows
You cannot use table names/columns in CF formulae, you have to use the actual cell references.
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,942
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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.
 

MurphDog

Board Regular
Joined
Oct 1, 2015
Messages
54
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Dec 30, 2008
Messages
11,942
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Oct 1, 2015
Messages
54
Office Version
  1. 365
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,564
Messages
5,596,863
Members
414,106
Latest member
Tigretto

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top