Function Problems

How to Match Excel Values and Return Codes Using Lookup Formulas

Khadija Khan Oct 22, 2026 870 views

Question details

The user needs to match specific text values (like district names) in a main table with a separate reference table to automatically return the corresponding codes for each entry.

How to Match Excel Values and Return Codes from a Reference Table

Product: Excel
Device & OS: not provided
Scenario: Automating data entry by retrieving corresponding codes from a reference table based on matching text values.
Observed behavior: The goal is to correctly display the matched code in the target cell without manual entry, properly handling missing data or errors if a match is not found.

Before you start

Ensure that your reference table does not contain duplicate lookup values and that the data formats (text vs. numbers) match exactly between your source data and the reference column.

Solution 1Recommended

Use the XLOOKUP Function (Microsoft 365 & Excel 2021+)

XLOOKUP is the most robust and flexible modern formula to find data in a table or range by row, with built-in error handling.

If you are using a newer version of Excel, XLOOKUP easily searches for a value and returns a corresponding value from another column, regardless of the column's position.

Select the target cell

Click on the cell where you want the returned code to appear, for example, cell C2.

Enter the XLOOKUP formula

Type `=XLOOKUP(B2, $D$2:$D$1000, $E$2:$E$1000, "")`. Here, B2 is the lookup value, $D$2:$D$1000 is the lookup array, and $E$2:$E$1000 is the return array. The "" argument returns a blank cell if no match is found.

Apply to the entire column

Press Enter to generate the result. Then, click and drag the fill handle (the small square at the bottom right corner of the cell) down to copy the formula to the rest of your table.

Use the XLOOKUP Function (Microsoft 365 & Excel 2021+)

Absolute References: Using dollar signs ($) creates an absolute reference. This prevents the lookup range from shifting downwards when you drag the formula to other cells.

Solution 2

Use VLOOKUP with IFERROR (All Excel Versions)

For older versions of Excel that do not support XLOOKUP, you can use VLOOKUP combined with the IFERROR function to handle any missing matches.

Advanced Spreadsheet Functions

Easily Match Values and Perform Lookups in WPS Spreadsheet

WPS Spreadsheet fully supports advanced functions like VLOOKUP and XLOOKUP, allowing you to seamlessly process complex data, match values across tables, and handle large datasets for free.

1. Open your file in WPS Spreadsheet: Launch WPS Office and open your workbook containing the data and reference tables.
2. Insert the lookup formula: Select the target cell and enter your preferred lookup formula, such as `=XLOOKUP()` or `=VLOOKUP()`.
3. Autofill the results: Press Enter, then double-click or drag the fill handle down to apply the formula across all required rows instantly.

Fully compatible with Microsoft Excel formulas and .xlsx files.Natively supports modern lookup functions including XLOOKUP.Free, lightweight, and fast performance even with large reference tables.Intuitive interface for formula insertion and error checking.

microsoft office alternative - wps office

Frequently Asked Questions

Why is my VLOOKUP formula returning an #N/A error?

The #N/A error occurs when the formula cannot find an exact match for your lookup value in the reference table. Check for trailing spaces, mismatched data types (e.g., text vs. numbers), or ensure you selected the correct lookup array.

Can I lookup values across different worksheets?

Yes, both VLOOKUP and XLOOKUP work across different sheets. Simply click on the target sheet while selecting your lookup range, and the software will automatically add the sheet name reference (e.g., Sheet2!$A$2:$B$100).

What is the difference between XLOOKUP and VLOOKUP?

XLOOKUP is a newer, more versatile function that can look up values in any direction (left or right), defaults to an exact match, and has built-in error handling. VLOOKUP only searches from left to right and requires an additional IFERROR function to hide errors gracefully.

Excel & Data Problems