How to Find Partial Matches and Return Current Email Addresses in Excel 365
Question details
The user needs to find a current email address in a worksheet when only a previous or partially matching address is available, using Excel 365 lookup and dynamic array functions.
- Product
- WPS Office Spreadsheets / Excel 365
- Device & OS
- not provided
- Scenario
- A user has a list of previous email addresses or partial email strings and needs to return the matching current email address from another range.
- Observed behavior
- Standard exact-match lookups do not return the correct address because the available value is only a partial text match.
Confirm that your spreadsheet contains separate ranges for current email addresses and previous or partial email values, and that your version of WPS Office or Excel supports XLOOKUP and dynamic array functions.
Use XLOOKUP with SEARCH and ISNUMBER to Find the First Partial Email Match
This is the quickest Excel 365 method for returning the first current email address whose previous address contains the partial text you enter.
XLOOKUP can look up TRUE as the search result of ISNUMBER(SEARCH(...)), which makes the formula case-insensitive and partial-match friendly without wildcards.
Click the cell where you want the current email address to appear.
Type =XLOOKUP(TRUE,ISNUMBER(SEARCH(A2,previous_range)),current_range,"Not found"), where A2 is the partial email value, previous_range is the column of older addresses, and current_range is the column of current addresses.
Press Enter to calculate the result. The formula returns the first current email address that contains the partial text.
Use FILTER to Return All Current Email Addresses That Match a Partial Value
Choose this dynamic array method when more than one previous email address may contain the partial text and you want all matching current addresses in a spill range.
Use INDEX and MATCH for an Alternative Partial Match Lookup
Use this method in older Excel versions or when you prefer a classic formula without XLOOKUP or FILTER.
Find Partial Email Matches with XLOOKUP in WPS Office Spreadsheets
WPS Office Spreadsheets supports Excel 365-style lookup and dynamic array functions, so you can use the same partial-match formulas to return current email addresses without changing your spreadsheet workflow.
- 1. Open your workbook in WPS Office: Launch WPS Office Spreadsheets and open the .xlsx or .xls workbook that contains the previous and current email address ranges.
- 2. Select the result cell: Click the cell where you want the current email address to appear.
- 3. Enter the partial-match formula: Type =XLOOKUP(TRUE,ISNUMBER(SEARCH(A2,previous_range)),current_range,"Not found") and replace A2 and the ranges with your actual columns.
- 4. Press Enter and review: Press Enter to return the first matching current email address, then review the result for accuracy.
- 5. Save your file: Save the workbook as an .xlsx file to keep the formulas and formatting intact for Microsoft Excel users.
Frequently Asked Questions
Can XLOOKUP find a partial email match without using wildcards?
Yes. Use =XLOOKUP(TRUE,ISNUMBER(SEARCH(A2,previous_range)),current_range,"Not found"). The SEARCH function checks whether the partial value in A2 appears anywhere inside each previous address, and ISNUMBER converts that check into TRUE or FALSE for XLOOKUP.
How can I return all email addresses that match a partial value?
Use the dynamic array FILTER function: =FILTER(current_range,ISNUMBER(SEARCH(A2,previous_range)),"No match"). This spills every matching current email address starting from the selected cell.
Is the partial match lookup case-sensitive?
No. SEARCH is case-insensitive, so it matches uppercase and lowercase letters equally. If you need case-sensitive matching, replace SEARCH with FIND in the same formula structure.
Why does my XLOOKUP return the wrong email address?
XLOOKUP returns the first match in the lookup array. If the partial text appears in multiple previous addresses, the first matching row determines the returned current address. Check the order of your previous_range and use FILTER if you need all matches.
Does this partial match formula work in older Excel versions?
XLOOKUP and FILTER require Excel 365, Excel 2021, or WPS Office with dynamic array support. In older Excel versions, use the INDEX and MATCH array formula =INDEX(current_range,MATCH(1,--ISNUMBER(SEARCH(A2,previous_range)),0)) and press Ctrl+Shift+Enter.
