Searching between two separate lists for matches

Question

I have two SharePoint Lists each with a column called Unique Key, what I need to do is search list B for matches in List A.

List B is the larger of the two and each List contains only unique entries in their respective lists (with matches between the two lists)

A separate column will be populated in List A stating if a match has been found with all values in List B.

Is this possible with OOTB functionality?

I could move List B's contains to List A if that makes getting a solution easier but I would prefer not to.

Answer 1

You can try using list relationships. Create a new look-up column in List A that references List B, if it returns data you have your match.

If all you need to do is report on the data you can also just use Excel. Use PowerPivot and import the two lists as data sources, and do essentially the same thing. With PowerPivot however you have a lot of flexibility in how you present that data. If you have Enterprise version of SharePoint 2010/2013 or O365, you can host the PowerPivot workbook in SharePoint (if it's configured).

A third option if you have Enterprise SKU and it's enabled, is to use Access Web App, again using your lists as data sources.