Function Problems

How to Search Another Excel Worksheet to Return a Phone Number

Bushra Parveen Sep 30, 2026 868 views

Question details

The user needs to find a way to look up a name on one worksheet and automatically retrieve the matching phone number into a different worksheet.

How to Search Another Excel Worksheet and Return a Phone Number

Product: Excel
Device & OS: not provided
Scenario: Pulling related contact information from a master data sheet into a working sheet using a common identifier.
Observed behavior: Instead of manually searching and copying phone numbers, the user wants a formula-based approach to retrieve values from column B based on exact name matches in column A across sheets.

Before you start

Ensure that the names on both worksheets are spelled exactly the same, without extra spaces, as the lookup formula requires an exact match to return the correct phone number.

Solution 1Recommended

Use the VLOOKUP Function

The VLOOKUP function is the standard and most efficient method to search for a specific value in one worksheet and return corresponding data from another worksheet.

VLOOKUP searches for a value in the leftmost column of a specified table array and returns a value in the same row from a column you specify. In this scenario, it will look up the name in Sheet2, find it in Sheet1, and return the phone number.

Select the Target Cell

Go to Sheet2 and click on the cell where you want the first phone number to appear (for example, cell B2).

Enter the VLOOKUP Formula

Type the following formula: =VLOOKUP(A2,Sheet1!A:B,2,FALSE). This tells Excel to look for the name in A2 within columns A and B of Sheet1.

Apply and Fill Down

Press Enter to see the returned phone number. Then, click the small square at the bottom-right corner of cell B2 and drag it down to apply the formula to the rest of the names in your list.

Exact Match Required: Using 'FALSE' at the end of the formula ensures that the function only returns a phone number if it finds an exact match for the name. If no match is found, it will return an #N/A error.

Advanced Spreadsheet Data Management

Perform Cross-Sheet Lookups Effortlessly with WPS Spreadsheet

WPS Spreadsheet fully supports VLOOKUP and other advanced data-mapping formulas, allowing you to seamlessly pull contact details across worksheets. It provides an intuitive formula builder and syntax guide to help you link your data without errors.

1. Open Your Workbook: Launch WPS Spreadsheet and open the file containing your names and phone numbers.
2. Navigate to the Destination Sheet: Switch to Sheet2 and select the blank cell next to the first name where the phone number should go.
3. Insert the Formula: Type =VLOOKUP(A2,Sheet1!A:B,2,FALSE) into the cell or use the Formulas tab to insert the function using the visual dialog box.
4. Drag to Autofill: Hit Enter to get your result, then double-click the fill handle to automatically populate the phone numbers for all the names in your column.

100% compatibility with Microsoft Excel formulas (.xlsx)Intuitive formula hints and syntax guides to prevent errorsBuilt-in error checking for lookup functionsFree and lightweight alternative for everyday spreadsheet tasks

microsoft office alternative - wps office

Frequently Asked Questions

Why does my VLOOKUP formula return an #N/A error?

The #N/A error occurs when the formula cannot find an exact match for the lookup value in the source sheet. This is often caused by typos, mismatched spelling, or hidden trailing spaces in the text. Check both sheets to ensure the names are identical.

Can I use XLOOKUP instead of VLOOKUP for this task?

Yes, if your spreadsheet software supports it, XLOOKUP is a powerful alternative. The equivalent formula would be =XLOOKUP(A2, Sheet1!A:A, Sheet1!B:B), which does not require the lookup column to be the leftmost column.

What if the phone numbers are located to the left of the names in Sheet1?

VLOOKUP can only search the leftmost column of the specified range and return values to the right. If the phone numbers are to the left of the names, you must either rearrange your columns or use the INDEX and MATCH combination formula instead.

Excel & Data Problems