The XLOOKUP function in Excel is a powerful lookup function that allows you to search for a value in a table or range and return a corresponding value in the same row or column. It is similar to the VLOOKUP and HLOOKUP functions, but with added features such as the ability to search for values in any column or row of a table, and the ability to return multiple values.
To demonstrate how to use the XLOOKUP function in Excel, let’s start with a simple example. Suppose you have a table that contains the names and ages of a group of people, as shown below:
| Name | Age |
|---|---|
| John | 25 |
| Sarah | 30 |
| James | 35 |
| Mary | 40 |
| Michael | 45 |
Now, suppose you want to find the age of the person whose name is “James”. Here’s how you can use the XLOOKUP function to do this:
- Select the cell where you want to display the result.
- Type the XLOOKUP function as follows:
=XLOOKUP("James", A2:A6, B2:B6)- “James” is the value you want to search for.
- A2:A6 is the range of cells that contains the names.
- B2:B6 is the range of cells that contains the ages.
- Press Enter.
The XLOOKUP function will search for the name “James” in the range A2:A6 and return the corresponding age from the range B2:B6, which is 35.
Here’s another example. Suppose you have a table that contains the names and salaries of employees, as shown below:
| Name | Salary |
|---|---|
| John | $50,000 |
| Sarah | $60,000 |
| James | $70,000 |
| Mary | $80,000 |
| Michael | $90,000 |
Now, suppose you want to find the salary of the person whose name is “Sarah”. Here’s how you can use the XLOOKUP function to do this:
- Select the cell where you want to display the result.
- Type the XLOOKUP function as follows:
=XLOOKUP("Sarah", A2:A6, B2:B6)- “Sarah” is the value you want to search for.
- A2:A6 is the range of cells that contains the names.
- B2:B6 is the range of cells that contains the salaries.
- Press Enter.
The XLOOKUP function will search for the name “Sarah” in the range A2:A6 and return the corresponding salary from the range B2:B6, which is $60,000.
In addition to these basic examples, the XLOOKUP function can be used for more complex lookups, such as searching for approximate matches, returning multiple values, and searching for values in columns or rows other than the first column or row. The XLOOKUP function can also be combined with other Excel functions to perform calculations or formatting on the returned values.
I hope this tutorial helps you understand how to use the XLOOKUP function in Excel.