SOLVED

Conditional formatting searching a list within another list

Highlighted
New Contributor

Hi, thanks for helping!

 

I have two columns that contain three lists. My objective is to use conditional formatting to highlight cells in Column A if they are also listed in Column D. I figured that out by using a conditional formatting formula:

=VLOOKUP($A1,$D$1:$D$22,1,0)=$A1

 

The complication occurs when the individuals cells in Column A will sometimes have *more* alpha-numeric characters than their Column D counterparts. I still need the cells in Column A to highlight.

 

For example, D4 shows FA666610F8009. I need it to identify duplicates in A3, which shows FA666610F8009, and A5, which shows FA666610F8009RS56.

 

I have tried diligently to find the right formulaic variation, but I have failed. The below linked excel file is as far as I could get on my own.

 

Any help would be appreciated.

 

Thank you!

2 Replies
Highlighted
Solution

I'd add a helper column to give the first 13 characters

 

See attached and let me know what you think

 

 

Highlighted
It worked! Thank you Wyn!