How to Use XLOOKUP for Partial Client Numbers and Suffixes
Question details
The user needs to retrieve phone numbers or other associated data using XLOOKUP when the unique identifier is split into a base client number and a suffix (like -0 or -1) across different columns.
- Product
- Spreadsheets
- Device & OS
- not provided
- Scenario
- Attempting to match a specific record when the unique lookup value is divided into two separate components (base number and suffix) in the master data table.
- Observed behavior
- Standard XLOOKUP searches only a single column by default, failing to find exact matches unless the criteria and lookup arrays are combined or modified.
Ensure that both tables format the client numbers and suffixes identically (e.g., both stored as Text or both as Numbers) and clear any trailing spaces to avoid #N/A errors.
Combine Lookup Arrays Directly in the XLOOKUP Formula
Use the ampersand (&) operator to concatenate multiple criteria and lookup arrays directly within the XLOOKUP formula without altering your source data.
This method evaluates the combined string in memory, allowing you to match two or more columns simultaneously without the need to permanently change your spreadsheet structure.
Click on the cell where you want the retrieved phone number to be displayed.
Type the formula combining the criteria: =XLOOKUP([@[Client Number]], Table1[Client Number]&Table1[-0 / -1], Table1[Phone Number], "No match"). Adjust the table and column names to match your actual data.
Press Enter to execute the formula. The formula concatenates the base number and suffix columns on the fly to find the exact match.
Drag the fill handle down from the bottom right corner of the cell to apply the formula to the rest of the rows in your table.
Create a Helper Column for Easier Maintenance
Add a dedicated column to combine the base number and suffix in the master table, simplifying the XLOOKUP formula for future audits.
Master Complex Lookups with WPS Spreadsheet
WPS Spreadsheet fully supports advanced functions like XLOOKUP, allowing you to combine criteria and retrieve complex data sets effortlessly. It offers a familiar interface and seamless compatibility with Microsoft Excel files.
- 1. Open your workbook: Launch WPS Spreadsheet and open your existing Excel workbook (.xlsx).
- 2. Select the target cell: Click on the cell where you want to output the lookup result.
- 3. Enter the formula: Type your combined XLOOKUP formula, using the formula autocomplete feature to easily select table columns.
- 4. Retrieve the data: Press Enter to execute the function and pull your data accurately.
Frequently Asked Questions
Why does my XLOOKUP formula return "No match" even when the data exists?
This usually happens due to data type mismatches (e.g., numbers stored as text in one table but not the other) or hidden trailing spaces. Use the TRIM function or ensure both tables format the client numbers identically before looking them up.
Can I use multiple criteria with XLOOKUP without concatenating?
Yes, you can use boolean logic by multiplying arrays. For example, =XLOOKUP(1, (Table1[Client Number]=A2)*(Table1[Suffix]=B2), Table1[Phone Number]) is highly effective for multiple conditions without joining text strings.
Is XLOOKUP available in older spreadsheet versions?
XLOOKUP is a newer function available in Microsoft 365, Excel 2021, and modern versions of WPS Office. For older versions, you may need to use an INDEX and MATCH combination using array entry (Ctrl+Shift+Enter) to evaluate combined criteria.
