WPS Spreadsheet: Search an Excel Database by Email and Return a Matching Row
Question details
The user needs to find an email address in a large database and return the corresponding record to another worksheet.
- Product
- WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- An email address is entered on one worksheet, and the matching name, field, or complete database row needs to be retrieved automatically without manually searching the database.
- Observed behavior
- The desired matching record should populate another worksheet based on the email address used as the lookup value.
Confirm that the database has a consistent Email column and, preferably, is formatted as a table with a recognizable name such as Database. Check for leading or trailing spaces in email values if a lookup does not return a match.
Use XLOOKUP to return a field from the matching email record
Use this method when you need one value, such as a name, department, or phone number, from the row that matches an email address.
XLOOKUP searches the Email column and returns the corresponding value from another column. Replace the table and column names with the names used in your workbook.
On the destination worksheet, enter the email address to search in a cell such as A2.
Select the cell where the result should appear, choose the formula bar or a blank cell, and enter =XLOOKUP(A2,Database[Email],Database[Name],"Not found").
Replace Database[Name] with the table column you want to return, then press Enter and copy the formula down if you have multiple email addresses.
Use FILTER to return the complete matching database row
Use this method when you want to bring back every column from the record associated with an email address.
Find email records quickly with XLOOKUP or FILTER in WPS Spreadsheet
WPS Spreadsheet lets you search a structured Excel database by email and return either a selected field or the complete matching row. Its familiar formula tools make it easy to migrate existing lookup workbooks and automate results on another worksheet.
- 1. Open the workbook: Launch WPS Office, choose Spreadsheet, and open the Excel workbook containing the database.
- 2. Format the database as a table: Select the database range, open the Table tools from the ribbon, and assign a clear table name such as Database.
- 3. Create the lookup: On the destination worksheet, use XLOOKUP to return one field or FILTER to return the complete row based on the email cell.
- 4. Check the result: Verify the returned record and correct spaces or inconsistent email formatting if the formula displays "Not found".
Frequently Asked Questions
How do I return only the name for a matching email in WPS Spreadsheet?
Enter the email in a cell such as A2, then use =XLOOKUP(A2,Database[Email],Database[Name],"Not found"). Replace the table and column names with those in your workbook.
How can I return the entire row that matches an email address?
Use =FILTER(Database,Database[Email]=A2,"Not found") in the destination worksheet. The matching row will spill across adjacent cells, so keep the output area clear.
Why does the email lookup show "Not found" even when the address exists?
Check for leading or trailing spaces, different punctuation, or inconsistent text formatting. Compare the lookup email with the database value and clean unwanted spaces before running the formula again.
Can XLOOKUP return a value from a worksheet with a different name?
Yes. Use a range from the other worksheet, such as =XLOOKUP(A2,DatabaseSheet!B:B,DatabaseSheet!C:C,"Not found"), or use structured table references if the database is formatted as a table.
What should I use if multiple records have the same email address?
Use FILTER to return every matching row. XLOOKUP returns the first matching result, while FILTER can display all records that meet the email condition.
