Function Problems

How to Convert Excel IDs to Names Using Lookup Formulas

Maira Mehtab Sep 22, 2026 869 views

Question details

The user wants to automatically retrieve and display names corresponding to specific IDs using data from a source table across different worksheets.

Product: Excel
Device & OS: not provided
Scenario: Entering IDs into a destination worksheet and needing the associated names to populate automatically from a source list without manual data entry.
Observed behavior: Needs an automated way to match and display names for both individual and team IDs using lookup formulas, while handling potential errors gracefully.

Before you start

Ensure that your source table has the ID column as the first column on the left, as the VLOOKUP function always searches from left to right.

Solution 1Recommended

Use VLOOKUP to Match IDs to Names

The standard VLOOKUP function is the most reliable way to pull associated names from a source table into your current worksheet.

VLOOKUP is universally supported across most Excel versions. If you are using Excel 2016, keep in mind that XLOOKUP is not available, making VLOOKUP the standard choice.

Select the destination cell

Click on the cell in the destination sheet where you want the corresponding name to appear.

Enter the VLOOKUP formula

Type the formula =VLOOKUP(A2, Source!$A$2:$B$1000, 2, FALSE). Replace 'A2' with the cell containing your ID, and 'Source!$A$2:$B$1000' with the actual range of your source data.

Apply to the entire column

Press Enter to retrieve the name. Then, click and drag the fill handle at the bottom-right of the cell downwards to apply the formula to the rest of the column.

Exact Match Parameter: Setting the last argument to FALSE ensures the formula only returns a name if the ID matches exactly, preventing incorrect data mapping.

Solution 2

Hide N/A Errors Using IFERROR

Wrap your VLOOKUP formula in an IFERROR function to leave the cell blank instead of displaying error messages when an ID is missing.

Solution 3

Create Separate Columns for Individual and Team IDs

If your data contains multiple types of IDs, such as teams and individuals, set up dedicated columns for each to avoid overwriting original data.

Use WPS Spreadsheet

Easily Use Lookup Formulas in WPS Spreadsheet

WPS Spreadsheet provides robust support for all advanced lookup formulas, including VLOOKUP, HLOOKUP, and XLOOKUP. You can seamlessly convert IDs to names and manage large datasets with high performance.

1. Open your file: Launch WPS Spreadsheet and open the workbook containing your ID lists and source tables.
2. Trigger the formula prompt: Type =VLOOKUP( in the desired cell to trigger the intuitive formula syntax guide.
3. Select ranges and execute: Select your ID cell, highlight the source table array, specify the column index number, and press Enter to instantly retrieve the corresponding name.

100% compatible with Microsoft Excel formulas and file formats (.xlsx).Built-in formula hints and syntax guides for VLOOKUP and XLOOKUP.Lightweight software that loads large source tables instantly.

microsoft office alternative - wps office

Frequently Asked Questions

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

This error occurs when the exact ID cannot be found in the source table. Ensure that the ID exists, there are no hidden trailing spaces in the cells, and the lookup column is the first column in your selected array.

Can I use XLOOKUP instead of VLOOKUP in Excel 2016?

No, XLOOKUP is not available in Excel 2016. You must use VLOOKUP or an INDEX/MATCH combination for lookup tasks in older versions.

Do I need to lock my source table reference?

Yes. It is highly recommended to use absolute references (like $A$2:$B$1000) for your source table array. This prevents the range from shifting when you copy or drag the formula down to other rows.

Excel & Data Problems