How to Return a Contact Email When Company Names Match in WPS Office Excel
Question details
The user needs a lookup formula that matches company names in Sheet1 against a company list in Sheet2 and returns the corresponding contact email address.
- Product
- WPS Office Spreadsheets (Excel-compatible)
- Device & OS
- not provided
- Scenario
- A workbook has company names in Sheet1 and a master company/contact list in Sheet2; the user wants to populate the Contact Email column in Sheet1 automatically.
- Observed behavior
- For matching company names, the Contact Email cell should fill with the correct email address; for non-matching names, it should return a blank or an error-handled result.
Before entering the formula, confirm that Sheet1 has the company name column and a blank Contact Email column, and that Sheet2 contains the company list in one column and the email address in another. Also make sure company names are cleaned of extra spaces so lookup matches do not fail.
Use XLOOKUP to return the contact email from Sheet2
Use this in Microsoft 365, Excel 2021, or WPS Office versions with XLOOKUP to return one email per company or to spill all results with a range reference.
XLOOKUP looks up A3 in Sheet2 column A and returns the corresponding email from Sheet2 column D. The fourth argument "" supplies an empty result when no company match is found.
In Sheet1, click cell I3 where the first contact email should appear.
Type =XLOOKUP(A3,Sheet2!A:A,Sheet2!D:D,"") and press Enter.
Use the fill handle from I3 down through the remaining rows, or use =XLOOKUP(A3:A10,Sheet2!A:A,Sheet2!D:D,"") to spill the results automatically where dynamic arrays are supported.
Use VLOOKUP with IFERROR to return email in older versions
Use this when XLOOKUP is not available and you need a formula that works in older Excel and WPS Spreadsheet versions.
Use INDEX and MATCH as an alternative lookup
Use this if your email column is not the fourth column or you need a flexible lookup that can return values to the left of the company column.
Return a Contact Email by Matching Company Names in WPS Spreadsheets
WPS Spreadsheets supports the same lookup formulas as Excel, so you can open your workbook and use XLOOKUP, VLOOKUP, or INDEX/MATCH to match company names in Sheet1 and return the contact email from Sheet2. It keeps .xlsx formatting and formula behavior intact without a Microsoft 365 subscription.
- 1. Open the workbook in WPS Spreadsheets: Launch WPS Office, click Open, and select the .xlsx file that contains Sheet1 and Sheet2.
- 2. Select the first Contact Email cell: Go to Sheet1 and click cell I3 where the email address should appear.
- 3. Apply the lookup formula: Enter =XLOOKUP(A3,Sheet2!A:A,Sheet2!D:D,"") or the VLOOKUP/INDEX-MATCH version and press Enter.
- 4. Fill the formula down: Drag the fill handle from I3 through the remaining company rows to populate the contact email column.
- 5. Save the workbook: Click File > Save or press Ctrl+S to keep the results in the Excel-compatible .xlsx format.
Frequently Asked Questions
Can I use XLOOKUP in WPS Office Spreadsheets?
Recent WPS Office versions that support dynamic arrays include XLOOKUP. If your WPS version returns #NAME?, use the VLOOKUP or INDEX/MATCH formula instead, or update WPS Office to a newer version.
How do I return a blank instead of #N/A when a company is not found?
Wrap the lookup in IFERROR, such as =IFERROR(VLOOKUP(A3,Sheet2!A:D,4,FALSE),""), or use XLOOKUP's fourth argument: =XLOOKUP(A3,Sheet2!A:A,Sheet2!D:D,"").
Will the formula include new rows added to Sheet2 later?
Using full-column references such as Sheet2!A:A and Sheet2!D:D includes newly added rows automatically. If you use a fixed range, expand the range to include the new company rows.
What should I do if company names look the same but do not match?
Check for leading or trailing spaces, non-breaking spaces, or case differences. Use =TRIM(A3) in a helper column or clean the source company column, then reapply the lookup formula.
