Function Problems

How to Return a Value When Two Cells Match in Excel

Amos Gikunda Oct 1, 2026 868 views

Question details

The user needs an Excel formula to compare a lookup value on one worksheet against a list on another worksheet and return the corresponding value from an adjacent column.

How to Return a Value When Two Cells Match in Excel

Product: Excel
Device & OS: not provided
Scenario: Cross-sheet data matching and information retrieval.
Observed behavior: Requires a formula to automate outputting a specific column's value when a matching cell is successfully found.

Before you start

Ensure both worksheets are within the same workbook and clearly identify which column contains your lookup values and which column contains the results you want to retrieve.

Solution 1Recommended

Use the XLOOKUP Formula (Newer Excel Versions)

XLOOKUP is the most robust and easiest way to find matching cells and return adjacent values in modern Excel versions.

XLOOKUP replaces older lookup functions by offering a simpler syntax and the ability to look both left and right of your search column. It natively supports custom text when no match is found.

Select the destination cell

Click on the cell where you want the matched value to be displayed.

Type the XLOOKUP formula

Enter the formula: =XLOOKUP(A2, Sheet2!B:B, Sheet2!C:C, "No Match").

Customize the references

Replace 'A2' with your lookup cell, 'Sheet2!B:B' with the column you want to search, and 'Sheet2!C:C' with the column containing the data you want to return.

Execute the formula

Press Enter. The formula will search for the value and return the matched result.

Use the XLOOKUP Formula (Newer Excel Versions)

Native Error Handling: Unlike older functions, XLOOKUP automatically handles errors by allowing you to define a 'Not Found' message, such as "No Match", directly within the formula.

Solution 2

Use VLOOKUP with IFERROR (All Excel Versions)

If you are using an older version of Excel that does not support XLOOKUP, VLOOKUP combined with IFERROR is the standard approach.

Powerful Spreadsheet Tool

Effortlessly Match Cells and Return Values Using WPS Spreadsheet

WPS Office Spreadsheet fully supports advanced lookup functions like VLOOKUP and XLOOKUP, allowing you to compare lists and extract data across worksheets seamlessly with an intuitive interface.

1. Open your workbook: Launch WPS Spreadsheet and open the file containing your data.
2. Launch the function wizard: Select the target cell and click the 'Insert Function' (fx) button next to the formula bar.
3. Search for the lookup formula: Search for XLOOKUP or VLOOKUP and follow the dialog prompts to select your lookup arrays easily.
4. Apply to multiple rows: Click OK to calculate the result, then drag the fill handle down to apply the matching formula to the rest of your data.

Fully compatible with Microsoft Excel formulas and .xlsx files.Built-in function wizard makes complex lookups simple to write without memorizing syntax.Lightweight software ensures smooth operation even with massive cross-sheet datasets.Free to download and use for your daily spreadsheet and data matching tasks.

microsoft office alternative - wps office

Frequently Asked Questions

Why is my VLOOKUP returning #N/A even when I can see a matching cell?

This usually happens due to formatting differences or hidden characters. Ensure that one cell isn't formatted as text while the other is a number, and use the TRIM() function to remove any accidental trailing spaces.

Can I use VLOOKUP to return a value from a column to the left of my lookup column?

No, VLOOKUP only searches the first column of the specified range and returns values to the right. To look left, you should use the newer XLOOKUP formula or an INDEX and MATCH combination.

How do I match values between two entirely different Excel workbooks?

Both VLOOKUP and XLOOKUP work across separate workbooks. Keep both files open, write your formula normally, and when selecting your search array, simply click over to the second workbook to select the target column. Excel will automatically generate the correct external reference link.

Excel & Data Problems