Menu
  1. Accueil
  2. Tech Forum
  3. Office Software
  4. Excel

Conditional formatting expert needed! Comparing 2 arrays. [Closed]

Report
Posts
1
Registration date
Wednesday November 9, 2016
Status
Member
Last seen
November 9, 2016
 -
Posts
2693
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
January 18, 2021
 -
Hi all,

I apologize for the vague title. but I could not think of a way to properly explain the problem in a single line with so few characters... So I was hoping to pull in the conditional formatting experts who can tell me if it is even possible to accomplish what I am trying to do here!

I am using Microsoft Office Professional Plus 2010.

I have two arrays that look like this:


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:


I will also edit this post in case I managed to resolve it myself. Thanks for your time.
See more

1 reply

Answer 1 / 1
Posts
2693
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
January 18, 2021
 455
Hi SaintNick,

For red use:
=IF(ISERROR(VLOOKUP($A3,$A$10:$B$12,2,0)),TRUE,FALSE)

For orange use:
=IF(VLOOKUP($A3,$A$10:$B$12,2)<$B3,TRUE,FALSE)

For dark green use:
=IF(VLOOKUP($A3,$A$10:$B$12,2)$B3,TRUE,FALSE)

For light green use:
=IF(VLOOKUP($A3,$A$10:$B$12,2)>$B3,TRUE,FALSE)


Always happy to help a saint ;).

Best regards,
Trowa
Report
Posts
33
Registration date
Sunday June 19, 2016
Status
Contributor
Last seen
November 20, 2016
 1
No need to use the syntax you have used there, CF only works on TRUE/FALSE (or 1/0) so you just need to construct a formula that will return 1 of those....
=IF(ISERROR(VLOOKUP($A3,$A$10:$B$12,2,0)),TRUE,FALSE)
becomes...
=VLOOKUP($A3,$A$10:$B$12,2,0)
etc

Also, to just find the ID...
=MATCH($A3,$A$10:$B$12,0)
Report
Posts
2693
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
January 18, 2021
 455
But that formula will reverse the result. It will change rows 3, 5 and 7 to red instead of 4 and 6.
Ask a question

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!