Conditional formatting in one array which searches a second array for a value, then formats based on a different cell

[SaintNick]

Hi all,

I am sorry for the confusing title, I am unsure how to properly express what it is I need in a single title.

I am using Microsoft Office Professional Plus 2010.

I have two arrays in a sheet that look similar to this:
<center>

</center>

The column A shows the "ID" of a task.
Column B shows the completion date of the task

I want to be able to automatically highlight the "Actual" array IDs and dates, according to if the "Actual" is matching ( or greater/less than) the "Proposal". Also if there is no matching task ID in proposal.

- Red if there is no matching task ID in the proposal array
- Orange if the Actual date is later than the proposal date
- Dark green if the Actual date is the same as the proposal date
- Light green if the Actual date is earlier than the proposal date.

Is this possible? I am struggling with how to get conditional formating to search an array, match the ID, then apply the formatting based on a different cell in the same row of the matching ID.

Any help here would be greatly appreciated!

To help visualise the needed automatic formatting:

<center>

</center>

Is this possible to do? It is also fine to use the 3 color scaling for the dates, and then a seperate rule for the dark red to indicate there is no matching ID in the other array.

 

[Snakehips]

SaintNick,

Welcome to MrExcel.

Assuming you create named ranges for Actual and Proposal then try..

Select 'Actual' and apply the following custom cf formulas as per shown for A3 below.
Order them as shown and tick to stop.

Excel Workbook
	A	B
2	Actual	*
3	249	01/08/2016
4	345	09/09/2016
5	346	09/09/2016
6	347	09/09/2016
7	391	09/11/2016
8	*	*
9	Proposal	*
10	249	01/08/2016
11	346	01/09/2016
12	391	13/11/2016
Sheet2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A3	1. / Formula is =COUNTIF(Proposal,$A3)=0	Abc
A3	2. / Formula is =LOOKUP($A3,Proposal)<$B3	Abc
A3	3. / Formula is =LOOKUP($A3,Proposal)=$B3	Abc
A3	4. / Formula is =LOOKUP($A3,Proposal)>$B3	Abc
B3	1. / Formula is =COUNTIF(Proposal,$A3)=0	Abc
B3	2. / Formula is =LOOKUP($A3,Proposal)<$B3	Abc
B3	3. / Formula is =LOOKUP($A3,Proposal)=$B3	Abc
B3	4. / Formula is =LOOKUP($A3,Proposal)>$B3	Abc

Hope that helps.

 

[Special-K99]

Try this (untested)
Make sure your dates are dates (numbers) and not text.

Select A3:A7

Conditional Formatting
New Rule
Use a formula to determine...
4 colors required so you need 4 formulas

=ISNA(VLOOKUP(A$3,A$10:A$12,1,0))
format as red

=AND(NOT(ISNA(VLOOKUP(A$3,A$10:A$12,0)),B$3>VLOOKUP(A$3,A$:10,B$12,2,0))
format as orange

=AND(NOT(ISNA(VLOOKUP(A$3,A$10:A$12,0)),B$3=VLOOKUP(A$3,A$:10,B$12,2,0))
format as dark green

=AND(NOT(ISNA(VLOOKUP(A$3,A$10:A$12,0)),B$3<VLOOKUP(A$3,A$:10,B$12,2,0))
format as light green

 

[SaintNick]

Thanky ou all for your replies. I will try them today and provide feedback.

 

[SaintNick]

Hi Special K99... If I try to use your formulas, I just get a "Formula contains an error" message. I have tried all, none seem to be correct =(.

 

[SaintNick]

Slightly annoying I cannot edit my replies....

Special-K99:
I realised I needed to remove the equals sign, and then I can enter the formulas. However, there is no formatting. The dates in the sheet are formatted as dates, not text or numbers.

Snakehips:
I am unsure you understood what it was I needed. Sorry... entirely my fault, it is difficult to explain and I really appreciate your help here!

Basically, imagine a list of tasks, thousands long, with each task having an assigned ID and proposed completion date. i.e. a high list of column A being numbers, and column B being dates.

Now I have a seperate sheet (or tab) which is a list of the same IDs, but in a totally random order (and sometimes missing enirely) and a list of different dates.

I need to format the first sheet where it will look at the task ID, then find the same task in the other sheet. Then it will compare the dates and formate the dates as explained before.

So, sheet one looks like this:

The dates on Sheet 1 need to be formatted according to the matching TASK ID date found on sheet 2:

So sheet 1 contains my master data, and with this daily extract I can quickly and easily see if the actual dates are not in schedule anymore, without me manually finding each task ID.

 

[Gerald Higgins]

The dates in the sheet are formatted as dates, not text or numbers.

The formatting doesn't really matter.
What matters is whether the underlying data is text or numbers, those are the only two options.
You may have a number formatted to look like a date.
OR, you may have a text string that looks like a date.
If you have a date in - say - cell B2, what does this formula return ?

=isnumber(b2)

If it returns TRUE, then your date is a number.
If it returns FALSE, then your date is text.

Let's assume you actually have numbers formated as dates.

This is how I replicated your requirements in the OP.

Select cell A3.
In Conditional Formating . . .
New Rule
Use a formula to determine which cells to format
Format values where this formula is true
=ISERROR(VLOOKUP($A3,$A$10:$A$12,1,FALSE))
Format
Apply a red fill
OK
OK
Applies to
=$A$3:$B$7
OK

Repeat to add two more formats . . .
Use this formula to apply the yellow format
=$B3>VLOOKUP($A3,$A$10:$B$12,2,FALSE)

Use this formula to apply the light green format
=$B3< VLOOKUP($A3,$A$10:$B$12,2,FALSE)<vlookup($a3,$a$10:$b$12,2,false)


I haven't applied a fourth format for dark green, as you seem to only have 4 possible conditions.
Therefore there is no need to apply the dark green colour through conditional formating, this can be done simply by applying a dark green colour to all cells in the range A3:B7 - the conditional formating will over-write this whenever it is appropriate to do so.</vlookup($a3,$a$10:$b$12,2,false)

 

[SaintNick]

Hi Gerald,

Thank you! This is very very close.

Red is correctly highlighted. The Others are soemtimes incorrect. I will do some checks and post back the cause once I find it. Thanks!

 

[SaintNick]

Solved! Thanks so much all! I am truly impressed with how quick and willing you were to help.

I created a slightly more complex "test sheet" so I could be satisfied the formatting was correct and not just by chance because I had made an error somewhere =). It works absolutely perfectly. Details are below in case anyone else needs the same solution (I hope its enough to explain how it works, though Gerald Higgins answer is all you need if you use the same list seperator and are not terrible with using VLOOKUP .)