Mastering Google Sheets: VLOOKUP, XLOOKUP and INDEXMATCH functions

Google Sheets
/
February 14, 2023

Introduction:

Got a complex dataset in Google Sheets and need to find the right way to use it? If so, you may need to use the VLOOKUP, XLOOKUP, and INDEXMATCH functions. If you are an experienced data analyst you might have already heard or used them, but often also the most experienced Google Sheets users still struggle with them. These functions can help you find and retrieve data from tables or ranges of cells in Google Sheets. To help you understand the differences between these functions and when to use each one, we’ve put together a quick guide on VLOOKUP, XLOOKUP, and INDEXMATCH as well as two YouTube video links that will help you master the XLOOKUP and INDEXMATCH functions .

Did you know?

Dokin boosts your spreadsheets game by allowing you to sync live data from company apps into Sheets, Slides and Docs.

Try for free

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).

Enjoyed this article?

Check out our blog or our help center for more.
Dokin, the data platform of modern business teams
Try for free

Jacopo Proietti

Co-founder @Dokin
Jacopo, a co-founder of Dokin, boasts 8 years in finance, having worked as a finance manager at Ogury and head of Financial Planning and Control at BlaBlaCar.
His passion for data integration led to the creation of Dokin, a game-changer for modern business teams. With customizable templates and built-in data connections, Dokin allows modern CMOs and CFOs to streamlines data reporting across Google Workspace applications.

More Stories

Productivity tips
Salesforce Integrations to go for in 2024
Google Sheets
Productivity tips
Google Sheets tip of the week - checkboxes
Google Sheets
Productivity tips
Why data visualization is important