Excel’s XLOOKUP function is a powerful tool for searching through large sets of data and returning specific values based on various search criteria. In this tutorial, we will explore how to use XLOOKUP to find and retrieve data from tables in Excel.
XLOOKUP replaced the VLOOKUP, HLOOKUP, INDEX/MATCH, and other lookup functions that have been used in previous versions of Excel. XLOOKUP offers many advantages over these older functions, including the ability to search for values in any direction (not just horizontally or vertically), to return results from multiple columns, and to handle errors more effectively.
To get started, let’s look at an example table that we want to search using XLOOKUP:
A B C D
1 Name City State Zip
2 John New York NY 10001
3 Jane Chicago IL 60601
4 Alice Houston TX 77001
5 Bob Seattle WA 98101
6 Bill Miami FL 33101
Suppose we want to retrieve the state of a person whose name is “Bob.” We can use the following formula:
=XLOOKUP("Bob", A2:A6, C2:C6)This formula searches for “Bob” in the range A2:A6 (the “Name” column) and returns the corresponding value from the range C2:C6 (the “State” column). In this case, the formula returns “WA” (the state abbreviation for Washington).
Note that XLOOKUP is case-insensitive, so it will find “bob” just as easily as “Bob.” If the search value is not found in the lookup array (in this case, the “Name” column), XLOOKUP returns the #N/A error.
XLOOKUP can also be used to search for values in a specific column and return corresponding values from a different column. For example, suppose we want to retrieve the city of a person whose zip code is “77001.” We can use the following formula:
=XLOOKUP(77001, D2:D6, B2:B6)This formula searches for “77001” in the range D2:D6 (the “Zip” column) and returns the corresponding value from the range B2:B6 (the “City” column). In this case, the formula returns “Houston.”
XLOOKUP also supports wildcard characters, which can be used to search for partial matches. For example, suppose we want to retrieve the state of a person whose name starts with “J.” We can use the following formula:
=XLOOKUP("J*", A2:A6, C2:C6)This formula searches for any value in the range A2:A6 that starts with “J” (using the asterisk wildcard character to match any number of characters after the “J”) and returns the corresponding value from the range C2:C6 (the “State” column). In this case, the formula returns “NY” (the state abbreviation for New York, corresponding to the name “John”).
In addition to these basic examples, XLOOKUP can be used to perform more complex searches and retrieve data from multiple columns. For more information on how to use XLOOKUP, consult the Excel documentation or search online for additional tutorials and examples.