The XLOOKUP function is a powerful tool in Excel that allows you to look up and retrieve data from a table or range of cells. It’s similar to the VLOOKUP and HLOOKUP functions, but with some important differences that make it easier to use and more versatile. Here’s how to use XLOOKUP in Excel:
Step 1: Set up your data
Before you can use XLOOKUP, you need to have your data arranged in a table or range of cells. This table should have at least two columns: one column for the lookup value (the value you want to find), and one column for the return value (the value you want to retrieve).
Step 2: Insert the XLOOKUP function
To insert the XLOOKUP function, click on the cell where you want the result to appear, and then type “=” to start the formula. Then, start typing “XLOOKUP(” and Excel will display a list of arguments that you need to provide for the function.
Step 3: Provide the arguments for the XLOOKUP function
The XLOOKUP function requires four arguments:
- lookup_value: The value you want to look up. This can be a cell reference, a number, a text string, or a formula that evaluates to a value.
- lookup_array: The range of cells that contains the lookup values. This can be a named range, a table, or a reference to a range of cells.
- return_array: The range of cells that contains the values you want to retrieve. This can be a named range, a table, or a reference to a range of cells.
- [match_mode]: An optional argument that specifies how Excel should match the lookup value to the lookup array. This can be “0” (exact match), “1” (exact or next smallest match), “-1” (exact or next largest match), or “2” (wildcard match using “?” and “*”).
Here’s an example of how to use XLOOKUP:
=XLOOKUP(B2,A2:A10,C2:C10)
In this example, the lookup value is in cell B2, the lookup array is in the range A2:A10, and the return array is in the range C2:C10. The XLOOKUP function will look up the value in B2 in the range A2:A10, and then return the corresponding value from the range C2:C10.
Step 4: Enter the XLOOKUP function
After you’ve provided the arguments for the XLOOKUP function, press Enter to complete the formula. The XLOOKUP function will then return the result, which you can see in the cell where you entered the function.
That’s it! With these steps, you should now be able to use XLOOKUP in Excel to look up and retrieve data from your tables or ranges of cells.