Function Problems

How to Match Short and Full Names in Excel Using XLOOKUP

Muhammad TalhaMuhammad Talha Nov 10, 2026 869 views

Question details

The user needs to match short names with full names across different worksheets, accounting for variations in exact wording and capitalization.

How to Match Short and Full Names in Excel Using XLOOKUP
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?.
Before you start

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.

Solution 1Recommended

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.

1
Select the destination cell

Click on the cell where you want the lookup result to be displayed.

2
Enter the case-sensitive formula

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.

3
Execute the formula

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.

Perform a Case-Sensitive Exact Match Using XLOOKUP
Array Sizes Must Match: Ensure your lookup array (e.g., A3:A13) and return array (e.g., B3:B13) are exactly the same size to prevent #VALUE! errors.
Powerful Spreadsheet Formulas

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. 1. Open your workbook: Launch WPS Spreadsheet and open the file containing your short and full department names.
  2. 2. Select the target cell: Click on the empty cell where you want the matched data to be returned.
  3. 3. Insert the XLOOKUP function: Go to the 'Formulas' tab and click 'Insert Function', or simply type =XLOOKUP directly into the cell.
  4. 4. Complete the formula: Define your lookup value, lookup array, and return array. Press Enter to instantly view your matched results.
Fully compatible with Microsoft Excel formats (.xlsx) and advanced formulas including XLOOKUP and EXACT.Built-in text normalization functions like UPPER, LOWER, and TRIM to clean messy data.Free, lightweight, and fast spreadsheet processing with a familiar user interface.Intuitive formula builder and advanced error checking to resolve #NAME? issues quickly.
microsoft office alternative - wps office

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).