Function Problems

Excel Formula to Match Names and Return a Related Reason

Partner Editor Oct 10, 2026 872 views

Question details

The user needs an Excel formula to check names from one list against two other lists, returning a corresponding reason if matched, or a blank if not found.

Excel Formula to Match Names and Return a Related Reason

Product: Excel
Device & OS: not provided
Scenario: Comparing a master list of names against referenced data in separate lists to automatically pull associated reasons.
Observed behavior: The goal is to automatically display the matching reason from the first list, or leave the cell blank when there is no matching name in the source lists.

Before you start

Ensure your data columns are clearly defined and check that the names in your lists do not contain accidental leading or trailing spaces, as this will prevent exact matches in your formula.

Solution 1Recommended

Use Nested IFERROR and VLOOKUP Functions

Combining IFERROR with VLOOKUP allows you to search the first list, fall back to the second list if no match is found, and finally return a blank cell if neither matches.

The VLOOKUP function searches for a value in the first column of a range and returns a value in the same row. By wrapping it in IFERROR, we can dictate exactly what Excel should do if the first lookup fails.

Organize your data columns

Assume names and reasons from List A are in columns A and B, List B names are in column C, and the List C names you want to check are in column D.

Select the target cell

Click on cell E2, which is where you want the first matching reason to appear.

Enter the nested formula

Type the following formula: =IFERROR(VLOOKUP(D2,A:B,2,FALSE),IFERROR(VLOOKUP(D2,C:C,1,FALSE),"")) and press Enter.

Fill the formula down

Click and hold the fill handle (the small square at the bottom-right corner of cell E2) and drag it down to apply the formula to the rest of the names in List C.

Use Nested IFERROR and VLOOKUP Functions

Formula Breakdown: The first VLOOKUP checks columns A:B and returns the reason from column 2. If it errors out, the second IFERROR checks column C. The empty quotes "" at the end ensure unmatched records return a blank cell instead of an #N/A error.

Process Data Effectively

Match Data and Use Advanced Formulas in WPS Spreadsheet

WPS Spreadsheet provides robust support for all major Excel functions, including VLOOKUP, XLOOKUP, and IFERROR, allowing you to cross-reference multiple lists with ease.

1. Open your spreadsheet: Launch WPS Office and open your workbook containing the lists you need to cross-reference.
2. Access the Formulas tab: Navigate to the 'Formulas' tab on the top ribbon to insert functions or manually type your IFERROR and VLOOKUP combination into the formula bar.
3. Apply and drag: Enter the formula into your target cell, press Enter, and double-click the fill handle to seamlessly apply it to your entire dataset.

Fully compatible with Microsoft Excel (.xlsx) file formatsExtensive formula library for complex data matching and error handlingFree and lightweight alternative for everyday spreadsheet tasks

microsoft office alternative - wps office

Frequently Asked Questions

Why is my VLOOKUP returning the wrong reason?

This usually happens if the range_lookup argument is set to TRUE (approximate match) or omitted. Always use FALSE as the 4th argument in your VLOOKUP formula to ensure it only returns exact matches.

How can I remove trailing spaces that break my matches?

You can use the TRIM function on your columns before matching. Create a helper column with =TRIM(A2) to clean the text, copy and paste the values over the original list, and try your lookup again.

Can I use XLOOKUP instead of VLOOKUP for this task?

Yes, if you are using a newer version of Excel or WPS Spreadsheet, you can use XLOOKUP. It has built-in error handling, allowing you to specify a 'if not found' value directly without needing to wrap it in the IFERROR function.

Excel & Data Problems