SOLVED
Home

Conditional formatting searching a list within another list

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!
Related Conversations
Documentation Migration
SunLeo in Office 365 on
0 Replies
SharePoint 2013 LIst Filters (by latest date)
dmphil in SharePoint on
0 Replies
SharePoint Lists - Expanded View Roadmap ID: 57302
Kotus-Tech in SharePoint on
12 Replies