How to Match Short and Full Names in Excel Using XLOOKUP
Question details
The user needs to match short names with full names across different worksheets, accounting for variations in exact wording and capitalization.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Comparing and matching varying text strings, such as department short names and full names, across multiple worksheets to retrieve corresponding data.
- Observed behavior
- Standard lookups may fail or return incorrect results due to case sensitivity differences, partial match limitations, or unsupported function errors like #NAME?.
Ensure you are using a version of Excel or WPS Spreadsheet that supports the XLOOKUP function (e.g., Microsoft 365, Excel 2021, or the latest WPS Office). It is also recommended to clean your data by removing any accidental leading or trailing spaces.
Perform a Case-Sensitive Exact Match Using XLOOKUP
Standard Excel lookups are case-insensitive. If your short or full names require strict case matching to distinguish them, combine XLOOKUP with the EXACT function.
By default, XLOOKUP treats 'Sales' and 'SALES' as identical. When analyzing data where capitalization matters, nesting the EXACT function inside XLOOKUP forces Excel to respect the text case. This approach compares the lookup value against the array and generates an array of TRUE/FALSE values for XLOOKUP to evaluate.
Click on the cell where you want the lookup result to be displayed.
Type the formula: =XLOOKUP(TRUE, EXACT(A3:A13, C3), B3:B13, "Not Found"). Replace 'C3' with your lookup value, 'A3:A13' with your lookup array, and 'B3:B13' with your return array.
Press Enter. The EXACT function checks the case, returning TRUE only for the exact case match, which XLOOKUP then finds and returns the corresponding value.

Normalize Text Before Matching
If your data is inconsistent with mixed casing and you want to ensure a clean, case-insensitive comparison, normalize the text using UPPER or LOWER functions.
Resolve the #NAME? Error with XLOOKUP
The #NAME? error typically occurs if your spreadsheet software does not support XLOOKUP or if the function name is misspelled.
Use XLOOKUP Seamlessly in WPS Spreadsheet
WPS Office provides full support for the modern XLOOKUP function, allowing you to match short and full names, handle case-sensitive searches, and manage data across worksheets effortlessly without encountering compatibility errors.
- 1. Open your workbook: Launch WPS Spreadsheet and open the file containing your short and full department names.
- 2. Select the target cell: Click on the empty cell where you want the matched data to be returned.
- 3. Insert the XLOOKUP function: Go to the 'Formulas' tab and click 'Insert Function', or simply type =XLOOKUP directly into the cell.
- 4. Complete the formula: Define your lookup value, lookup array, and return array. Press Enter to instantly view your matched results.

Frequently Asked Questions
Why does my XLOOKUP formula return a #NAME? error?
The #NAME? error indicates that Excel doesn't recognize the function. This usually happens if you are using an older version of Excel (like Excel 2016 or 2019) that does not support XLOOKUP, or if there is a typo in the formula name. Updating your software or switching to WPS Office will resolve this.
Can XLOOKUP match partial text like a short department name to a full name?
Yes, you can use wildcards for partial matches. You must set the match_mode argument in XLOOKUP to 2 (wildcard character match) and add asterisks around your lookup value, such as: =XLOOKUP("*"&C3&"*", A3:A13, B3:B13, "Not Found", 2).
Is the standard XLOOKUP function case-sensitive?
No, standard lookup functions in Excel and WPS Spreadsheet, including XLOOKUP and VLOOKUP, are case-insensitive by default. They view 'Admin' and 'ADMIN' as identical. To force case sensitivity, you must nest the EXACT function within your XLOOKUP formula.
How do I handle hidden spaces that cause XLOOKUP to fail?
Extraneous leading or trailing spaces will cause exact matches to fail. You can wrap your lookup value and lookup array inside the TRIM function to ignore accidental spaces. For example: =XLOOKUP(TRIM(C3), TRIM(A3:A13), B3:B13).




