How to use XLOOKUP in Google Sheets and comparison with VLOOKUP
You may already be familiar with the VLOOKUP function in Google Sheets, but there is a XLOOKUP function that can replace it nicely. In this article I am going to tell you the difference between these two functions and I will teach you how you can use it!
Before starting, check out the video tutorial about it:
XLOOKUP function in Google Sheets
Example 1
Basically, these are the two tables we have. What we need to do is to complete the Order Status column in the first table with Order status from the second table.
To do it we use XLOOKUP function that is a perfect fit in this case.
The formula looks like this.
To simplify it, first of all, we state an argument that needs to be found in the second table. Followingly, we state a data range where it has to be searched – in this case Order ID column from the second table. Then we need to mention a data range with Order Status, that we basically need to insert. Lastly, you have to write a message that will appear in the cell in case there is no match in the table. So, if the match is not found, you will see the message “No found!”
Look how the formula placed everything in its places!
Example 2
In this example I want to show how to find a match and replicate the existing column. In this case, the column Revenue.
We start our formula with an argument that needs to be found in the table. Then, we make a data range where it has to be searched, in our case – it has to be searched among the titles. Lastly, we select a date range to present the data belonging to this column.
That is how the final result looks!
What is the difference between XLOOKUP and VLOOKUP:
The key differences between an XLOOKUP and a VLOOKUP are:
- XLOOKUP can look for values to the left and right of the lookup array. VLOOKUP is limited to only looking for values to the right of the lookup value column.
- XLOOKUP allows you to customize text when a valid match is not found. VLOOKUP only shows you an #N/A (error sign).
- XLOOKUP allows you to specify a search mode (such as starting to look from the top or the bottom of a table) while VLOOKUP can only start looking for values from the top.
Simply speaking, XLOOKUP function is an upgraded version of VLOOKUP and offers a great flexibility!
Hope you enjoyed this article! I would like to remind you that you can subscribe to our newsletter and get a free template! Share your impressions in the comments section!