|For the morning....|
lookup_value is whatever it is that you are looking up.
In my example, that would be whatever is in A1, for A1, whatever is in A2 for A2, etc.
table_array is the range of cells that VLOOKUP will look up the lookup_value in, and return a value from if the lookup_value is found. VLOOKUP always looks in the first column of the table_array for the lookup_value.
In my example, the table_array is the range I named emailadds.
col_index_num is the column number of the table_array that VLOOKUP will return a value from if it finds the lookup_value in the first column of the table_array.
In my example, I am asking VLOOKUP to return the value in the first column of the table_array, but that doesn't really matter as I'll explain later.
range_lookup tells VLOOKUP whether to look for an exact match or not.
In my example, I used "0" which means I want VLOOKUP to find an exact match.
If VLOOKUP does not find the lookup_value in the first column of the table_array, it will return #N/A.
Excel provides provides a function that allows you to check for a #N/A error which is =ISNA(). It's often used with VLOOKUP and an IF function to prevent the display of the #N/A error. For example,
What this will do is return "Not Found" if VLOOKUP can't find what it is looking for or it will return the correct value from the table_array if the lookup_value is found. "Not Found" is prettier than #N/A.
So, when using VLOOKUP for Conditional Formatting, we don't really want it to return a value, we just want to know if the lookup_value is found or not.
If the lookup_value is not found, it will return #N/A, which will make the ISNA() function TRUE, which in turn will make the IF statement TRUE which should should format the cell with whatever formats you choose via Conditional Formatting.
You did choose a format to apply to the cells didn't you? If you didn't, nothing will happen even if the IF statement evaluates to TRUE.
BTW...you can test the formula in a cell and it will return TRUE or FALSE depending on whether or not the lookup_value is found. TRUE means the lookup_value was not found.
Hope that helps...