How do I get common data from two tables in Excel? - Excel Tips and Tricks

Learn how to get common data from two tables in Excel. In another words, how do I extract common values from two columns in Excel? Or how do you compare two lists in Excel and pull matching data? We will nt be covering question to how do I use Vlookup to find matching data in two columns? but will be using FILTER() function instead. Essentially, find the Common Values between two lists in Excel.

These are the formulas outlined in my video.

Get Common Names Between 2 Tables
=FILTER(A4:A44,(COUNTIF(D4:D44,A4:A44)))

Get Common Names Between 2 Tables (record)
=FILTER(A4:B44,(COUNTIF(D4:D44,A4:A44)))


Here's the breakdown of the formula.
FILTER: The FILTER function is used to extract specific data from a range based on certain criteria.

A4:A44: This is the range of cells that we want to filter. In this case, it's the range from cell A4 to A44.

COUNTIF: The COUNTIF function counts the number of times a specific value appears in a range.

D4:D44: This is the range of cells where the COUNTIF function will look for the values from column A. In this case, it's the range from cell D4 to D44.

A4:A44 (inside COUNTIF): This is the range of cells from column A that will be counted in the COUNTIF function. It matches the same range used in the FILTER function.

The logic behind this formula is to filter the values in column A (A4:A44) based on the condition that each value appears at least once in column D (D4:D44). In other words, it returns only the values from column A that have at least one corresponding occurrence in column D.


How do I get common data from two tables in Excel?,How do I extract common values from two columns in Excel?,How do you compare two lists in Excel and pull matching data?,How do I use Vlookup to find matching data in two columns?,Find the Common Values between two lists in Excel,