Function Problems

How to Match Duplicate Phone Numbers to Names in Excel

Camila Milosovich Oct 10, 2026 871 views

Question details

The user needs a way to automatically return the corresponding contact name for each phone number in a call-log workbook, correctly handling instances where the same phone number appears multiple times.

How to Match Duplicate Phone Numbers to Names in Excel

Product: Excel
Device & OS: not provided
Scenario: Managing a call-log worksheet where recurring phone numbers need to be quickly matched and paired with their respective contact names.
Observed behavior: The user wants to establish a lookup table and use an exact-match formula to populate the correct names against duplicated phone numbers automatically.

Before you start

Ensure that the phone numbers in both your main call log and your lookup table share the exact same formatting. Inconsistencies like extra spaces, country codes, or text-versus-number cell formats will cause exact-match formulas to fail.

Solution 1Recommended

Use VLOOKUP to Match Phone Numbers to Names

This is the most common and widely compatible method for matching data across tables using a dedicated lookup table.

VLOOKUP searches for a specific value in the first column of a table array and returns a value in the same row from another column. It requires the phone numbers to be to the left of the names in your lookup table.

Set up a lookup table

Create a dedicated reference table containing unique phone numbers in one column (e.g., Column G) and their corresponding contact names in the adjacent column to the right (e.g., Column H).

Enter the VLOOKUP formula

Select the cell in your call log where you want the matched name to appear. Type the formula =VLOOKUP(A2,$G$2:$H$4,2,FALSE). Replace A2 with the cell of the first phone number in your log, and $G$2:$H$4 with the actual range of your lookup table.

Apply the formula to all rows

Press Enter to execute the formula. Then, click the small square handle at the bottom-right corner of the selected cell and drag it down to apply the lookup formula to all other duplicate phone number entries in the column.

Use VLOOKUP to Match Phone Numbers to Names

Absolute Cell References: Using dollar signs ($) in the table array ($G$2:$H$4) locks the reference so that the lookup table range doesn't shift downwards when you copy the formula down your sheet.

Solution 2

Use XLOOKUP for Enhanced Flexibility

For newer versions of Excel, XLOOKUP provides a more robust way to match data, removing the requirement for the search column to be on the left.

Powerful Spreadsheet Tool

Easily Match Data with WPS Spreadsheet

You can quickly match duplicate phone numbers to names using standard lookup formulas like VLOOKUP and XLOOKUP in WPS Office Spreadsheet. It provides a highly familiar interface and seamless handling of large datasets.

1. Open your file in WPS Spreadsheet: Launch WPS Office and open your call-log workbook containing the phone numbers.
2. Launch the Insert Function dialog: Select the cell where you want the name to appear, go to the 'Formulas' tab on the ribbon, and click 'Insert Function'.
3. Configure the VLOOKUP function: Search for and select VLOOKUP. Use the prompt boxes to easily select your Lookup_value (the phone number cell), Table_array (your reference list), and Col_index_num (the column containing the names).
4. Apply and fill down: Click OK to generate the result, then drag the cell's fill handle down to match all remaining duplicate phone numbers instantly.

100% compatibility with Microsoft Excel formulas and .xlsx formatsFull support for advanced lookup functions including VLOOKUP, HLOOKUP, and XLOOKUPBuilt-in function dialogs that guide you step-by-step to prevent formula syntax errorsLightweight and free to use across Windows, Mac, and mobile devices

microsoft office alternative - wps office

Frequently Asked Questions

Why is my VLOOKUP formula returning an #N/A error for exact phone number matches?

This error typically occurs due to formatting mismatches. For example, one column might be formatted as Text while the other is formatted as a Number, or there might be hidden leading/trailing spaces. Ensure both columns share the exact same cell format, and consider using the TRIM() function to remove invisible spaces.

How can I extract a list of unique phone numbers to create my lookup table?

You can easily create a unique list by copying your entire column of phone numbers and pasting it into a new column. Select this new column, navigate to the Data tab on the ribbon, and click 'Remove Duplicates'. This will delete the repeated entries, leaving you with a clean list to assign names to.

Can I use INDEX and MATCH to find names instead of VLOOKUP?

Yes, INDEX and MATCH is an excellent alternative, especially if your lookup table has the names to the left of the phone numbers. You can use a formula like =INDEX($H$2:$H$10, MATCH(A2, $G$2:$G$10, 0)), which looks for the phone number in column G and returns the corresponding name from column H without relying on column order.

Excel & Data Problems