Function Problems

How to Return a Reference Number from a Description in Excel

Maira Mehtab Sep 22, 2026 871 views

Question details

The user needs a lookup formula to match a text description selected from a drop-down list and automatically return its corresponding reference number located in a different worksheet.

Product: Excel
Device & OS: not provided
Scenario: Retrieving related data across different worksheets dynamically based on a user's drop-down selection.
Observed behavior: Requires a functional formula to accurately pair a selected description with its assigned reference number and handle unmatched cases.

Before you start

Ensure that the source worksheet containing your descriptions and reference numbers does not have duplicate descriptions, as lookup formulas will only return the first matching result they find.

Solution 1Recommended

Use the XLOOKUP Function (Recommended)

XLOOKUP is the most straightforward and modern formula to search for a value and return a corresponding result, with built-in error handling.

The XLOOKUP function looks for a value in one array and returns a value in the same position from a second array. It is highly recommended due to its simplicity and native ability to handle 'Not found' scenarios without requiring extra functions.

Select the target cell

Click on the cell adjacent to your drop-down list where you want the reference number to be displayed.

Enter the XLOOKUP formula

Type the formula `=XLOOKUP(D1,Sheet2!E1:E10,Sheet2!F1:F10,"Not found")`. In this formula, D1 is your drop-down cell, Sheet2!E1:E10 is the column of descriptions, and Sheet2!F1:F10 is the column containing reference numbers.

Apply the formula

Press the Enter key. The cell will now automatically display the correct reference number whenever a new description is selected from the drop-down list.

Built-in Error Handling: By adding "Not found" as the fourth argument, XLOOKUP automatically replaces the standard #N/A error with a clean text message if the description is missing.

Solution 2

Use INDEX and MATCH with IFERROR

If you are using an older version of Excel that does not support XLOOKUP, the combination of INDEX and MATCH is a powerful and robust alternative.

Free Microsoft Office alternative

Effortlessly Manage Data and Lookup Formulas with WPS Office

WPS Spreadsheet provides comprehensive support for advanced functions like XLOOKUP, INDEX, and MATCH, allowing you to seamlessly retrieve data across worksheets just like in Microsoft Excel.

1. Open your workbook: Launch WPS Spreadsheet and open the file containing your drop-down list and reference data.
2. Select the destination cell: Click on the cell where you want the reference number to be displayed.
3. Insert the lookup formula: Type `=XLOOKUP()` and select your lookup value, lookup array, and return array using the intuitive formula prompter.
4. View your dynamic data: Press Enter to instantly view your cross-sheet data update as you change drop-down selections.

Fully compatible with Microsoft Excel file formats (.xlsx, .xls) and functions.Built-in support for modern lookup formulas like XLOOKUP for easier data matching.Lightweight architecture ensuring fast processing for complex cross-sheet lookups.Free and intuitive interface available across Windows, Mac, and mobile platforms.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my XLOOKUP formula return a #N/A error?

A #N/A error occurs if the description you selected from the drop-down list does not perfectly match any entry in the lookup array (e.g., due to extra spaces). To prevent the error display, utilize the 'if_not_found' argument by adding "Not found" before closing the parentheses in your XLOOKUP formula.

Can I use VLOOKUP instead of XLOOKUP for this task?

You can use VLOOKUP only if the descriptions are located in a column to the left of your reference numbers. XLOOKUP and INDEX/MATCH are far more flexible because they can look up data in any direction regardless of column order.

How do I create a drop-down list for the descriptions in the first place?

To create a drop-down list, select the target cell, navigate to the Data tab, and click Data Validation. Under the 'Allow' drop-down, choose 'List', and then select the range of cells containing your descriptions as the 'Source'.

What if my reference numbers and descriptions are in a different workbook?

Both XLOOKUP and INDEX/MATCH can reference external workbooks. Ensure the external workbook is open, then simply highlight the ranges in the other workbook while writing your formula. Excel will automatically insert the external file path and sheet names.

Excel & Data Problems