How to Use XLOOKUP
XLOOKUP is a relatively new lookup function that has advantages in terms of flexibility and simpler syntax over VLOOKUP. We will explore why these advantages and why VLOOKUP is more prone to issues that XLOOKUP escapes. Also, there may be a specific need for the user to choose XLOOKUP. First, let’s dive into a quick summary of the pro and cons of both functions.
| Measure | XLOOKUP | VLOOKUP |
|---|---|---|
| Pros | More flexible with multi-directional lookups, simpler syntax, better error handling | Compatibility, familiarity, potentially faster |
| Cons | Not available in all versions of Excel | Limited lookup capability, error-prone |
| Lookup range | Can search for values in any column of a table | Limited to searching for values in the first column |
| Syntax | Simpler | More complex |
| Error handling | Improved | Prone to errors |
Now let’s explore the new XLOOKUP syntax and understand why this may be a better option for you. We can use the chart below to get a better idea of the pros and cons and comparative advantages of XLOOKUP.
Watch the Video Presentation on How to Use VLOOKUP and Comparison to VLOOKUP
How to Use XLOOKUP
XLOOKUP is actually now different than VLOOKUP if you are trying to return a single result from a lookup. The Syntax itself is easier.
In this function, we don’t need to specify the column location, we don’t need to fix our lookup value at being the leftmost column. XLOOKUP can go in any direction. Let’s see how the VLOOKUP and XLOOKUP literally return the same information just much less direction needed
How to Use XLOOKUP
- Type =XLOOUP() into the cell where you want to return the value.
- Enter the lookup value.
- Enter the look-up array. This is where find the match for the lookup value.
- Enter the return array. This is where the value exists where you want to return.
- Optional: Enter the message or value, if the value is not found.
- Optional: Match Mode. This is the match type such as exact, approximate, or wildcard match.
- Optional: Search Mode: Allows you to bring back the value you want to search from top-up or bottom-down.
Let’s Compare the XLOOKUP syntax to the VLOOKUP syntax
You can see the simplicity of the syntax when comparing XLOOKUP to VLOOKUP syntax. The XLOOKUP function is sophisticated enough to bring back the return value in the array or column regardless of where it is on the table. So, we don’t need to count the columns from left most lookup value column.
Where XLOOPUP is Better than VLOOKUP?
Finding Values Anywhere
Where XLOOKUP is so much better than VLOOKUP is it can find values anywhere in an array (left or right, horizontal or vertical). We are not required to go from left to right when looking up values.
Error Handling
Also for error, handling, XLOOKUP lets you specify the exact message if there are any issues with the lookup value.
Wild Card Search
When entering the lookup value we can use a wildcard search if you enter ” H*” for any lookup value that starts with H. Specifically if you are looking for names.
Search Up and Down
We can enter an optional parameter to say search from the bottom or search from the top of the column. This helps if you have similar values in the same column. For so you can specify if you want to target the first value from the top of the first value from the bottom. With VLOOKUP, it will bring back the first value from the top.
Return an array of information
XLOOKUP can actually bring back an array of information. So you will bring back all the values that mack the lookup value.
