.png)
What is VLOOKUP?
That’s the question I asked myself the first time I opened a spreadsheet as a young graduate and was asked by my manager then to fill out a sales commission report. The moment I first learnt how VLOOKUPs worked, I felt like I had stumbled upon some sort of magic or alchemy.
VLOOKUP stands for “Vertical Lookup” and is one of the most popular functions in Google Sheets. It allows you to search for a value in the leftmost column of a table and retrieve the value in another column. VLOOKUP is a great tool for finding and retrieving data from large tables of information.
It can look for the exact match of your data input (when you add the suffix FALSE) or the closest match (when you add the suffix TRUE)
For example, let’s say you have a table of customer data in Google Sheets:
To use VLOOKUP to find the email address of a customer, you can use the following formula:
=VLOOKUP(“Michael”, A2:E7, 4, FALSE)
The formula will search for the value “Michael” in the leftmost column of the table (column A) and return the value in the fourth column (column D), which is the email address.
What is XLOOKUP?
XLOOKUP is like VLOOKUP turned into a Super-Sayan.
For the readers who are not familiar with Dragonball, it is the newest lookup function in Google Sheets and it’s a great improvement over VLOOKUP. It allows you to search for a value in any column of a table and return the value in another column. The great advantage of XLOOKUP is that it can lookup values both vertically and horizontally (a big improvement compared to VLOOKUP) ans is especially useful if you need to search for values in multiple columns or if you need to search for multiple values at the same time.
While VLOOKUP uses a single table array followed by a column index number, XLOOKUP uses a lookup array and a return array.
Let’s take again the example of a table of customer orders in Google Sheets:
To use XLOOKUP to find the order quantity of Michael, you can use the following formula:
=XLOOKUP(“Michael”, A2:A6, E2:E6)
The formula will search for the value “Michael” in the first column of the table (column A) and return the value in the return array we’ve entered (E2:E6).
To learn more about the XLOOKUP functions and get the details needed to master it, we suggest you check out this video here.
What is INDEXMATCH?
To use another Dragon Ball analogy, INDEXMATCH is like the fusion between Goku and Vegeta (sorry, had to do this one). It is a combination of two functions: INDEX and MATCH. It is similar to VLOOKUP, but it offers more flexibility and is often more efficient. INDEXMATCH allows you to search for values in any column of a table and return the value in another column.
In our example:
To find the email address of a customer, you can use the following formula:
=INDEX(D2:D7, MATCH(“Michael”, A2:A7, 0))
The formula will search for the value “Michael” in the first column of the table (column A) and return the value in the fourth column (column D), which is the email address.
You can find a detailed video explanation on the use of INDEX, MATCH and INDEXMATCH function here
When to Use VLOOKUP, XLOOKUP, and INDEXMATCH?
So, as a recap, here are the definitions of the different functions that we have resumed above:
VLOOKUP: Use VLOOKUP when you need to search for a value in the leftmost column of a table and return a value from another column.
XLOOKUP: Use XLOOKUP when you need to search for a value in multiple columns of a table and return a value from another column.
INDEXMATCH: Use INDEXMATCH when you need to search for a value in any column of a table and return a value from another column.
For the easiest and most organized datasets, a VLOOKUP function is often enough, however in those cases where data is more complex to manage or less structured, it would be better to use XLOOKUP or INDEXMATCH as they offer dynamic column referencing. For example, I used to have a guy in my team who was a VLOOKUP aficionado as the spreadsheets he was dealing with were mostly organized and data was structured in a consistent way. However, my team that was dealing with more complex datasets (mostly coming from back office transactions) was making the most out of both XLOOKUP abd INDEXMATCH in order to retrieve data correctly and efficiently. So, the best option is to learn how to master these two as you’ll be equipped with a couple of greta tools to face even the more complex spreadsheet datasets. Check out the videos we have linked here (for XLOOKUPs) and here (for INDEXMATCH).
.png)