Function Problems

How to Search Multiple Excel Worksheets for a Matching Phone Number

Muhammad Talha Oct 10, 2026 870 views

Question details

The user needs to find a specific phone number by matching first and last names across approximately 40 dynamic worksheets.

How to Search Multiple Excel Worksheets for a Matching Phone Number

Product: Excel
Device & OS: not provided
Scenario: Attempting to locate a matching record (phone number) across dozens of Excel tabs where sheet layouts or row counts might vary, making standard formulas inefficient.
Observed behavior: Standard lookup formulas fail to iterate through multiple worksheet names easily, requiring overly complex workarounds or failing entirely when row counts differ.

Before you start

Before attempting to merge data or run custom scripts, ensure all your worksheets use consistent column headers (e.g., First Name, Last Name, Phone Number) to prevent data misalignment.

Solution 1Recommended

Use Power Query to Combine Data and Perform a Lookup

Consolidate all worksheets into a single master table using Power Query, making it easy to use standard lookup formulas.

A single standard Excel formula cannot dynamically loop through dozens of worksheets without severe performance issues. Power Query is the most reliable method for appending data from multiple sheets into one unified list.

Launch Power Query

Go to the Data tab on the Excel ribbon, click on 'Get Data', select 'From File', and then 'From Workbook'.

Import and Transform Worksheets

Select your current workbook. In the Navigator window, choose 'Select multiple items', check all the relevant worksheets containing the phone numbers, and click 'Transform Data'.

Append Queries

In the Power Query Editor, use the 'Append Queries as New' feature on the Home tab to combine all the selected worksheets into a single table. Ensure columns like First Name, Last Name, and Phone Number align correctly.

Load the Master Table

Click 'Close & Load' to output the combined data into a new master worksheet.

Apply XLOOKUP or INDEX/MATCH

On your summary sheet, use a two-condition lookup formula pointing to the master table, such as: =XLOOKUP(A2&B2, MasterSheet!A:A&MasterSheet!B:B, MasterSheet!D:D) where A2 and B2 are the target First and Last names.

Use Power Query to Combine Data and Perform a Lookup

Dynamic Updates: If you add new rows to your individual worksheets later, simply right-click the Power Query master table and select 'Refresh' to update your lookup source.

Solution 2

Use a VBA Macro to Loop Through Worksheets

Write a custom VBA script to automatically scan every worksheet for the matching names and return the corresponding phone number.

Powerful Data Management

Consolidate and Search Data Easily with WPS Spreadsheet

WPS Office offers robust tools for managing and querying data across multiple worksheets. You can easily consolidate your sheets and use powerful lookup functions to find matching phone numbers without complex coding.

1. Open Your Workbook: Launch WPS Spreadsheet and open the multi-sheet workbook containing your contact lists.
2. Consolidate Data: Navigate to the Data tab and utilize the 'Consolidate' feature to quickly merge your 40+ worksheets into a single, manageable master sheet.
3. Perform Your Search: Use the built-in XLOOKUP or VLOOKUP functions on the master sheet to instantly retrieve the correct phone number based on the first and last name.

Seamless compatibility with Microsoft Excel formats (.xlsx, .xls)Advanced lookup functions like XLOOKUP and VLOOKUP fully supportedBuilt-in data consolidation tools for multi-sheet managementFree, lightweight, and fast alternative for complex data processing

microsoft office alternative - wps office

Frequently Asked Questions

Can I use VLOOKUP across multiple sheets without VBA or Power Query?

While possible using a combination of VLOOKUP, IFERROR, and INDIRECT alongside a named range of all sheet names, this method is highly prone to performance issues and errors when dealing with over 40 worksheets. Consolidating the data first is highly recommended.

How do I perform a two-condition lookup for First and Last names?

You can concatenate the lookup values and lookup arrays. For example, using XLOOKUP: =XLOOKUP(Cell1&Cell2, LookupRange1&LookupRange2, ReturnRange). Alternatively, use an INDEX and MATCH array formula.

Why does the INDIRECT function fail when searching multiple sheets?

INDIRECT is a volatile function, meaning it recalculates every time any change is made to the workbook. When used to search across 40+ sheets, it requires massive computing resources and can significantly slow down or freeze your spreadsheet application.

Excel & Data Problems