The XLOOKUP function is a powerful alternative to the VLOOKUP and HLOOKUP functions, as it provides more flexibility and functionality.
Syntax:
=XLOOKUP(lookup_value,lookup_array,return_array,[if_not_found],[match_mode],[search_mode])
The function has several arguments:
- lookup_value: This is the value that you want to look up. It can be a cell reference or a constant.
- lookup_array: This is the range of cells that you want to search for the lookup value. This range should be a single column or a single row.
- return_array: This is the range of cells that you want to return the value from. This range should be a single column or a single row.
- if_not_found (optional): This is the value that is returned if the lookup value is not found in the lookup array. By default, this is set to #N/A.
- match_mode (optional): This determines how exact the match should be. There are four options: 0 = exact match, -1 = exact or next smallest, 1 = exact or next largest, 2 = wildcard match.
- search_mode (optional): This determines whether the lookup_array should be searched in exact or sorted order. There are two options: 1 = search in exact order, 2 = search in sorted order.
Here’s an example of how to use the XLOOKUP formula:
Suppose we have a table that shows the sales revenue for different products:
| Product | Revenue |
|---|---|
| A | $100 |
| B | $200 |
| C | $150 |
| D | $75 |
We want to find the revenue for product B using the XLOOKUP formula.
The formula we would use is:
=XLOOKUP("B",A2:A5,B2:B5)
Here’s a breakdown of the formula:
- lookup_value = “B”
- lookup_array = A2:A5
- return_array = B2:B5
The result of the formula is $200, which is the revenue for product B.
We can also use the XLOOKUP formula to search for values in a row. For example, suppose we have the following table that shows the sales revenue for different months:
| Month | Sales |
|---|---|
| Jan | $100 |
| Feb | $200 |
| Mar | $150 |
| Apr | $75 |
We want to find the sales for the month of February using the XLOOKUP formula.
The formula we would use is:
=XLOOKUP("Feb",A2:D2,A3:D3)
Here’s a breakdown of the formula:
- lookup_value = “Feb”
- lookup_array = A2:D2
- return_array = A3:D3
The result of the formula is $200, which is the sales for the month of February.
In conclusion, the XLOOKUP formula is a powerful and flexible lookup function in Excel that can be used to search for values in a single column or row. It provides more options and functionality than the VLOOKUP and HLOOKUP functions, making it a great addition to your Excel toolkit.