Lookup a random sorted array

Question

Hi All,&nbsp;I cannot seem to find an answer for my requirements, and hope you can come to my aid please.&nbsp;DataIn Column A I have the names of countries sorted randomly.In Column B I have the names of countries sorted randomly.&nbsp;Request1. 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.&nbsp;Thanks for your feedback.&nbsp;&nbsp;

savia · Answer

Elias_Haddad&nbsp;Both can be done with custom CF rules based on a formula.&nbsp;For 1:&nbsp; &nbsp; =MATCH(active cell, column B, 0)&nbsp;For 2:&nbsp; &nbsp; =ISERROR(MATCH(active cell, column A, 0))

elias_haddad · Answer

Savia&nbsp;&nbsp;Thank you for your feedback.&nbsp;Only partially successful though...&nbsp;Savia&nbsp;

elias_haddad · Answer

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

elias_haddad · Answer

Savia&nbsp;&nbsp;&nbsp;

savia · Answer

Elias_Haddad&nbsp;You should only write the rule for the active cell, so F1:F5 should just be F1.&nbsp; 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.

Forum Discussion

Lookup a random sorted array

8 Replies

Resources