Function Problems

How to Filter One Excel List Using Matching Values from Another List

Olivia Miller Oct 1, 2026 869 views

Question details

The user needs to filter a primary data table to display only the rows containing values that exist in a secondary reference list.

How to Filter One Excel List Using Matching Values from Another List

Product: Microsoft Excel
Device & OS: not provided
Scenario: Comparing two lists of data, such as truck load names, and filtering the main data table to display only the items that are present in the reference list.
Observed behavior: The user wants to extract or display matching rows automatically, seeking the best formula depending on the specific Excel version in use.

Before you start

Ensure both your primary list and reference list are organized in columns without blank headers. Identify which Excel version you are using, as the optimal formula differs between Microsoft 365 and older legacy versions.

Solution 1Recommended

Use the FILTER and XMATCH Functions (Excel 365)

For users with Excel 365 or newer versions, combining the dynamic array FILTER and XMATCH functions is the most efficient way to extract matching rows without modifying the original data.

This method utilizes dynamic arrays. It will automatically spill the filtered results into adjacent cells without the need for manual filtering or helper columns.

Select an output cell

Click on an empty cell where you want the filtered results to appear. Ensure there is enough blank space below and to the right for the data to spill.

Enter the FILTER formula

Type the formula: =FILTER(A2:D100,ISNUMBER(XMATCH(B2:B100,F2:F50)),"No matches") into the formula bar.

Adjust the ranges

Replace A2:D100 with your main data range, B2:B100 with the specific column in the main data you want to check, and F2:F50 with your secondary reference list.

Execute the formula

Press Enter. The filtered list will dynamically generate, showing only the rows that match the reference list.

Use the FILTER and XMATCH Functions (Excel 365)

Dynamic Updates: If you add or remove items from your reference list, the filtered results will update automatically.

Solution 2

Use a Helper Column with the COUNTIF Function (Older Excel Versions)

If you are using an older version of Excel (like Excel 2016 or 2019) that does not support dynamic arrays, use a helper column with the COUNTIF function to identify and manually filter matches.

Efficient Data Management with WPS Office

Easily Filter and Compare Lists Using WPS Spreadsheet

WPS Spreadsheet provides powerful data processing capabilities, including full support for advanced formulas like COUNTIF, MATCH, and dynamic filters, allowing you to compare and filter lists effortlessly.

1. Open your file: Launch WPS Spreadsheet and open the document containing your primary and reference lists.
2. Add a helper formula: Create a new column next to your data and enter the formula =COUNTIF($F$2:$F$50, B2)>0 to check for matches.
3. Apply to the column: Double-click the fill handle to apply this formula to the entire dataset.
4. Filter the results: Go to the 'Data' tab, select 'AutoFilter', and filter your new column by 'TRUE' to reveal only the matching records.

100% compatible with Microsoft Excel formulas and file formats (.xlsx).Lightweight software with fast processing for large datasets and complex arrays.Familiar interface requiring zero learning curve for existing Excel users.Free to use with comprehensive data analysis and visualization tools.

microsoft office alternative - wps office

Frequently Asked Questions

How do I filter out values that do NOT match the second list?

To show non-matching rows, you can modify the helper column formula to =COUNTIF($F$2:$F$50,B2)=0 and filter for TRUE. If using Excel 365, you can change the formula to =FILTER(A2:D100,ISNA(XMATCH(B2:B100,F2:F50))) to extract rows that don't appear in the reference list.

Can I use MATCH or VLOOKUP instead of COUNTIF?

Yes. You can use =ISNUMBER(MATCH(B2,$F$2:$F$50,0)) in a helper column. It works exactly like the COUNTIF method by returning TRUE if the value is successfully located in the reference list.

Will these formulas work if the reference list is on another worksheet?

Absolutely. You just need to include the sheet name in your reference range. For example, your formula would look like =COUNTIF(Sheet2!$A$2:$A$50, B2)>0.

Why is my FILTER function returning a #CALC! error?

The #CALC! error in the FILTER function typically means there are no matching results to display. You can avoid this error by utilizing the third argument in the FILTER function to provide a fallback message, such as =FILTER(range, condition, "No matches found").

Excel & Data Problems