Lookup a random sorted array

%3CLINGO-SUB%20id%3D%22lingo-sub-1251642%22%20slang%3D%22en-US%22%3ELookup%20a%20random%20sorted%20array%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1251642%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSTRONG%3EHi%20All%2C%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20cannot%20seem%20to%20find%20an%20answer%20for%20my%20requirement%2C%20and%20hope%20you%20can%20come%20to%20my%20aid%20please.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EData%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3EIn%20Column%20A%20I%20have%20the%20names%20of%20countries%20sorted%20randomly.%3C%2FP%3E%3CP%3EIn%20Column%20B%20I%20have%20the%20names%20of%20countries%20sorted%20randomly.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3ERequest%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3EI%20wish%20to%20use%20conditional%20formatting%20to%20fill%20the%20cells%20when%20A%20is%20found%20in%20B%2C%20and%20B%20is%20found%20in%20A.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EOptional%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3ECells%20where%20no%20match%20exists%20to%20be%20filled%20with%20an%20alternate%20color.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20your%20feedback.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1251642%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1251808%22%20slang%3D%22en-US%22%3ERe%3A%20Lookup%20a%20random%20sorted%20array%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1251808%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F594175%22%20target%3D%22_blank%22%3E%40Elias_Haddad%3C%2FA%3E%26nbsp%3BBoth%20can%20be%20done%20with%20custom%20CF%20rules%20based%20on%20a%20formula.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%201%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B%20%3DMATCH(active%20cell%2C%20column%20B%2C%200)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%202%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B%20%3DISERROR(MATCH(active%20cell%2C%20column%20A%2C%200))%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1251893%22%20slang%3D%22en-US%22%3ERe%3A%20Lookup%20a%20random%20sorted%20array%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1251893%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F544433%22%20target%3D%22_blank%22%3E%40Savia%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20your%20feedback.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOnly%20partially%20successful%20though...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F544433%22%20target%3D%22_blank%22%3E%40Savia%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1251897%22%20slang%3D%22en-US%22%3ERe%3A%20Lookup%20a%20random%20sorted%20array%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1251897%22%20slang%3D%22en-US%22%3Eoops%2C%20what%20happened%20to%20the%20picture%20I%20inserted%3F%3F%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1251903%22%20slang%3D%22en-US%22%3ERe%3A%20Lookup%20a%20random%20sorted%20array%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1251903%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F544433%22%20target%3D%22_blank%22%3E%40Savia%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20image-alt%3D%22CF%231.jpg%22%20style%3D%22width%3A%20577px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F179351i9F217989B48DFC74%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22CF%231.jpg%22%20alt%3D%22CF%231.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1251945%22%20slang%3D%22en-US%22%3ERe%3A%20Lookup%20a%20random%20sorted%20array%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1251945%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F594175%22%20target%3D%22_blank%22%3E%40Elias_Haddad%3C%2FA%3E%26nbsp%3BYou%20should%20only%20write%20the%20rule%20for%20the%20active%20cell%2C%20so%20F1%3AF5%20should%20just%20be%20F1.%26nbsp%3B%20And%20you%20need%20to%20make%20sure%20that%20the%20reference%20to%20the%20other%20region%20doesn't%20move%20as%20each%20cell%20is%20considered%2C%20so%20replace%20G1%3AG10%20with%20%24G%241%3A%24G%2410.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1251953%22%20slang%3D%22en-US%22%3ERe%3A%20Lookup%20a%20random%20sorted%20array%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1251953%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F544433%22%20target%3D%22_blank%22%3E%40Savia%3C%2FA%3E%26nbsp%3BSo%20I%20have%20to%20create%205%20separate%20CF's%20to%20manage%20the%20range%20F1%3AF5%20%3F%20That's%20a%20pity%20because%20my%20original%20set%20of%20data%20has%20circa%20200%20names%20in%20each%20column.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1252010%22%20slang%3D%22en-US%22%3ERe%3A%20Lookup%20a%20random%20sorted%20array%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1252010%22%20slang%3D%22en-US%22%3ENo%2C%20just%20one%20rule.%20You%20write%20the%20rule%20for%20the%20active%20cell%20as%20an%20example%20and%20then%20Excel%20alters%20it%20for%20all%20the%20others%20based%20on%20which%20references%20do%20%2F%20do%20not%20have%20%24s%20in%20them%2C%20just%20the%20same%20as%20copying%20and%20pasting%20a%20normal%20formula.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1252048%22%20slang%3D%22en-US%22%3ERe%3A%20Lookup%20a%20random%20sorted%20array%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1252048%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F544433%22%20target%3D%22_blank%22%3E%40Savia%3C%2FA%3E%26nbsp%3BWorks%20now.%20Fantastic!%26nbsp%3B%26nbsp%3BWith%20much%20appreciation%2C%20thank%20you.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

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.

 

 

8 Replies
Highlighted

@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))

Highlighted

@Savia 

 

Thank you for your feedback.

 

Only partially successful though...

 

@Savia

 

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

@Savia 

 

CF#1.jpg

 

Highlighted

@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.

Highlighted

@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.

Highlighted
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.
Highlighted

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