How to Use XLOOKUP to Find Names and Return Team Names in Excel
Question details
The user needs a formula to look up a list of names from one column against another, and return their corresponding team names using the XLOOKUP function.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Organizing personnel or roster data by matching individual names to their respective team assignments located in a different column.
- Observed behavior
- A working XLOOKUP formula that correctly maps names from column A to team names in column H by searching column I, outputting a custom 'Not found' message if the name is missing.
Ensure that your names in column A exactly match the spelling and format of the names in column I. It is highly recommended to use bounded ranges instead of entire columns to improve calculation performance in large workbooks.
Use the Standard XLOOKUP Formula
Use the basic XLOOKUP syntax to quickly find exact matches for names and retrieve their corresponding team assignments.
XLOOKUP is a powerful function that allows you to search for a value in one column and return a corresponding value from another. In this scenario, it enables you to look to the left (returning from column H while searching in column I), which traditional VLOOKUP cannot do natively.
Click on the empty cell beside the first name in column A where you want the corresponding team name to appear.
Type the formula =XLOOKUP(A2,$I:$I,$H:$H,"Not found") into the formula bar. Here, A2 is the name to search for, $I:$I is the column to search in, and $H:$H is the column containing the team names.
Press Enter to view the result for the first name. Then, click the bottom-right corner of the cell and drag the fill handle down to apply the lookup formula to the rest of your data.
Use XLOOKUP with Partial Matching (Wildcards)
If the names in your dataset contain additional text or slight variations, utilize XLOOKUP's wildcard matching mode.
Easily Apply XLOOKUP and Advanced Formulas with WPS Office
WPS Spreadsheet fully supports modern advanced functions like XLOOKUP, VLOOKUP, and INDEX/MATCH. You can flawlessly manage large datasets, extract team names, and perform complex data analysis completely for free.
- 1. Open your dataset in WPS Spreadsheet: Launch WPS Office and open your .xlsx workbook containing the names and team lists.
- 2. Type the XLOOKUP formula: Select the target cell, type =XLOOKUP(, and use your mouse to select your lookup value, lookup array, and return array.
- 3. Press Enter and drag to fill: Hit Enter to calculate the exact team name, then double-click the fill handle to automatically complete the rest of the column.
Frequently Asked Questions
Why does my XLOOKUP formula return an #N/A or 'Not found' error?
This usually happens if the exact name does not exist in the lookup column, or if there are hidden trailing spaces in the text. You can use the TRIM function around your cell reference (e.g., TRIM(A2)) to remove accidental spaces before performing the lookup.
What is the primary difference between XLOOKUP and VLOOKUP?
XLOOKUP is much more flexible than VLOOKUP. It can look to the left (returning a value located in a column before the search column), automatically defaults to an exact match, and lets you specify a custom 'not found' message directly inside the formula without needing an extra IFERROR function.
Can I use multiple criteria with XLOOKUP to find a team name?
Yes. You can combine lookup arrays by using the ampersand (&) operator. For example, using the formula =XLOOKUP(A2&B2, FirstNames&LastNames, TeamCol) allows you to search based on both first and last name columns simultaneously for a highly precise match.
