Lookup a random sorted array

[Elias_Haddad]

Hi All,

I cannot seem to find an answer for my requirements, and hope you can come to my aid please.

Data

In Column A I have the names of countries sorted randomly.

In Column B I have the names of countries sorted randomly.

Request

1. I wish to use conditional formatting to fill the cells when A is found in B.

2. I wish to use conditional formatting to fill the cells when B is not found in A.

Thanks for your feedback.

[#2]

@Elias_Haddad Both can be done with custom CF rules based on a formula.

For 1:

    =MATCH(active cell, column B, 0)

For 2:

    =ISERROR(MATCH(active cell, column A, 0))

[#3]

@Savia 

Thank you for your feedback.

Only partially successful though...

@Savia

[#4]

oops, what happened to the picture I inserted???

[#5]

@Savia 

[#6]

@Elias_Haddad You should only write the rule for the active cell, so F1:F5 should just be F1.  And you need to make sure that the reference to the other region doesn't move as each cell is considered, so replace G1:G10 with $G$1:$G$10.

[#7]

@Savia So I have to create 5 separate CF's to manage the range F1:F5 ? That's a pity because my original set of data has circa 200 names in each column.

[#8]

No, just one rule. You write the rule for the active cell as an example and then Excel alters it for all the others based on which references do / do not have $s in them, just the same as copying and pasting a normal formula.

[#9]

@Savia Works now. Fantastic!  With much appreciation, thank you.