Power Query Problems

How to Filter Excel Tables by Matching Phone Numbers

Kushani Nimanthika Oct 12, 2026 869 views

Question details

The user needs to filter a phone database table to display only the records that match phone numbers associated with a known list of contractors.

How to Filter Excel Tables by Matching Phone Numbers

Product: Microsoft 365 Excel
Device & OS: not provided
Scenario: Matching a large database of phone calls or messages against a master list of known contractor phone numbers.
Observed behavior: Lookup errors frequently occur due to inconsistent phone number formats, missing values, or incorrect VLOOKUP formulas, often resulting in #N/A or #VALUE! errors.

Before you start

Before matching your tables, ensure all phone numbers in both your source database and contractor list are normalized to the exact same format (e.g., removing spaces, dashes, or inconsistent country codes).

Solution 1Recommended

Use Power Query to Merge and Filter Tables

Power Query is the most robust and recommended method for combining and filtering tables without relying on complex, resource-heavy formulas.

Power Query allows you to seamlessly append different data sources and merge them based on matching criteria. It handles large datasets much better than traditional lookup formulas and significantly reduces the risk of referencing errors.

Normalize Contractor Data

Format your contractor data so that all phone numbers, including any alternate contact numbers, are consolidated into a single column.

Load Tables into Power Query

Select your data, go to the Data tab, and click 'From Table/Range' to load both the PhoneDatabase and the Contractor tables into the Power Query Editor.

Merge the Queries

In the Power Query Editor, select your main PhoneDatabase query. Go to the Home tab and click 'Merge Queries'.

Configure the Match

Select the Contractor query from the second dropdown. Highlight the 'Phone Number' column in both preview panes, and choose the 'Inner' join kind to filter out non-matches and keep only rows that exist in both tables.

Load the Filtered Results

Click OK to apply the merge, then click 'Close & Load' on the Home tab to return the newly matched and filtered data to a fresh Excel worksheet.

Use Power Query to Merge and Filter Tables

Solution 2

Use a COUNTIF Helper Column to Filter Matches

If you prefer not to use Power Query, you can use a formula to flag matching phone numbers in a helper column.

Powerful Spreadsheet Tool

Filter and Match Tables Easily in WPS Spreadsheet

WPS Spreadsheet offers powerful data handling capabilities, allowing you to use advanced formulas like COUNTIF and robust data filtering to instantly match phone numbers across different databases. It delivers a fast, smooth user experience while maintaining perfect compatibility with Microsoft Excel.

1. Open Your Tables: Launch WPS Spreadsheet and open the workbook containing your phone database and contractor tables.
2. Add a Formula Column: Create a new helper column in your primary table and input the COUNTIF matching formula to identify corresponding numbers.
3. Apply Data Filtering: Navigate to the Data tab on the top ribbon and click 'AutoFilter' to add dropdown menus to your table headers.
4. Display Matched Records: Click the filter icon on your new helper column and select the value that represents a successful match to hide unmatched records.

Seamlessly filter complex datasets and match phone numbers across tables with advanced formulas.Fully compatible with Microsoft Excel (.xlsx) formats, ensuring your lookup formulas work perfectly.Lightweight installation with rapid processing speeds for large data arrays.Clean, familiar interface that requires zero learning curve to master.

microsoft office alternative - wps office

Frequently Asked Questions

Why does VLOOKUP return #VALUE! or #N/A when matching phone numbers?

An #N/A error occurs when the phone number does not exist in the lookup table. A #VALUE! error usually happens due to mismatched data types (e.g., comparing a text string with a numeric value) or incorrect argument inputs. Using an IFERROR wrapper or ensuring both columns share the exact same formatting resolves this.

Should I format phone numbers as text or numbers in Excel?

It is highly recommended to format phone numbers as Text. This prevents Excel from automatically removing leading zeros (like in international codes) or truncating long digit strings. The most critical rule for matching tables is that both lists must use the exact same data format.

What is the advantage of Power Query over lookup formulas?

Power Query automatically processes large tables without slowing down your workbook's calculation speed. It simplifies data transformation, avoids messy helper columns, and removes the risk of manual errors associated with writing thousands of VLOOKUP or COUNTIF formulas across multiple sheets.

Excel & Data Problems