In my years of building apps and templates in Google Sheets and Microsoft Excel, one thing has become clear:

lookup functions are absolutely essential to everything I create.

However, I’m not someone who excels at memorizing every formula syntax, so I often find myself turning to the internet for help.

The challenge? It’s hard to find a guide that covers all the use cases for looking up values in Google Sheets and Microsoft Excel in one place. So, I decided to create one! 💪

This guide is designed to be your comprehensive reference, focusing on just the most powerful and versatile lookup formulas. If you think we’ve missed any important use cases, feel free to let us know!

We’ve ensured that all the formulas in this guide work in both Microsoft Excel and Google Sheets. So you’re all set regardless of which spreadsheets tool you’re using! (Save this article for future reference)

Formula Overview

We’ll dive into 3 key formulas that handle the majority of lookup scenarios:

• XLOOKUP
• FILTER
• INDEX + MATCH

We’ll cover not only how to use each of these Excel formulas but also the optimal data structures for each, so you can choose the right one for your specific needs.

PS: We don’t cover fuzzy matching in this guide, as it would add unnecessary complexity and potentially overload you. If you’d love the topic to be covered, send us a message!

The formulas we introduced in this guide are not only efficient but also modern replacements for older formulas like VLOOKUP and HLOOKUP. You don’t need to learn every formula out there—just the ones that really get the job done.

Before we start, let’s talk about why I’ve moved away from using VLOOKUP in Google Sheets and Microsoft Excel.

Why I don’t use VLOOKUP in Google Sheets anymore?

While VLOOKUP remains a useful formula in Google Sheets and Excel, I find formulas like the combo of INDEX + MATCH, XLOOKUP, and FILTER offer greater better flexibility for the data I’m working with, and hence, applicable for most of the use cases.

On the other side, the wide-known formulas like VLOOKUP is limited to searching for values only in the left-most column of a range. As compared to INDEX MATCH which allows for lookups in any column, and XLOOKUP that eliminates the need for the lookup column to be first and offering additional features, handling missing matches, and allowing fuzzy matches.

Remember, you don’t need to learn every formula out there—just the ones that really get the job done.

XLOOKUP formula

XLOOKUP is perfect when you have:

• A key column (lookup column) on the left, and a return column on the right.
• A simple vertical or horizontal structure where you need to find a value based on a unique key.

XLOOKUP Syntax

=XLOOKUP(search_key, search_range, result_range, [if_not_found], 0, [search_mode])

Explanation of Arguments:

• search_key: The value you are looking for (e.g., product name, customer ID).
• search_range: The range where the search key is located (e.g., a column of product names).
• result_range: The range from which to return the value (e.g., the corresponding price or other data).
• [if_not_found] (optional): The value to return if no match is found. If omitted, it will return an error.
• 0: Specifies that you want an exact match. If you use this argument, XLOOKUP will only return a result if it finds an exact match.
• [search_mode](optional): Defines the direction of the search:
  • 1 to search from the first value to the last (default).

XLOOKUP Example

Let’s say you have a list of products and their prices:

If you want to find the price of the “Content Calendar”, you could use this formula:

=XLOOKUP("Content Calendar", A2:A5, B2:B5)

Explanation:

• “Content Calendar” is the search_key (the product you’re looking for).
• A2:A5 is the search_range (the column with the product names).
• B2:B5 is the result_range (the column with the prices).
• 0 (exact match) is implied by default, so we don’t need to include it explicitly.

This will return 10.00, the price of the “Content Calendar”.

⭐️ How to Lookup Values from Another Sheet in Google Sheets

When working with multiple sheets in Google Sheets, it’s often necessary to lookup values from another sheet. The process remains similar to the examples we’ve discussed, but the key difference is how you reference the data range across different sheets.

For instance, if the Product Prices table is located in sheet “Sheet2”, instead of referencing a single range within the same sheet, you’ll prefix the range with the sheet name followed by an exclamation mark. Here’s a simple example using the XLOOKUP formula:

=XLOOKUP("Content Calendar", 'Sheet2'!B:B, 'Sheet2'!C:C)

In this case, we’re looking up the value “Content Calendar” from Sheet2, searching within column B, and returning the corresponding value from column C. The reference to another sheet is made by adding ‘Sheet2’! before the column range, allowing you to lookup data from external sheets within the same workbook.

Let’s take this example further. Imagine you’re building a page where your sales team can enter the product name in Cell A2 in Sheet1. Based on the product entered, you want the corresponding price to appear.

To display the price for the entered product, modify the formula as follows:

=XLOOKUP(A2, 'Sheet2'!B:B, 'Sheet2'!C:C)

Note that we now change the “Content Calendar” value to A2. Now, when your sales team enters a product name in Cell A2, the sheet will automatically show the correct price from Sheet2.

💡 PRO TIPS #1 – Data Slicers for Efficient Search

Since XLOOKUP is case-sensitive, it’s crucial to ensure that the product name is entered correctly to get the perfect match.

To eliminate human error and avoid inconsistencies, one effective solution is to use a slicer. With a slicer, users can select the correct product name from a pre-defined list instead of typing it manually. This approach helps reduce mistakes and ensures consistency in your data. 

For more details on how to set up a slicer and generate a dropdown list, check out our (COMING SOON) guide on setting up data validation.

💡 PRO TIPS # 2 – ARRAYFORMULA + XLOOKUP for Bulk Lookup 

To make your XLOOKUP formula even more powerful, you can combine it with ARRAYFORMULA. This allows you to perform lookups across entire columns without needing to copy the formula down for each row.

With ARRAYFORMULA, you can apply XLOOKUP to an entire range of values at once, saving you time and effort.

Here’s an example of how to use ARRAYFORMULA with XLOOKUP:

=ARRAYFORMULA(XLOOKUP(A2:A10, 'Sheet2'!B:B, 'Sheet2'!C:C))

In this formula, ARRAYFORMULA processes all values in the range A2:A10, performs the lookup in Sheet2‘s column B, and returns the corresponding values from column C. This makes it much easier to handle large datasets without manually applying the formula to each row.

FILTER formula

FILTER is ideal when you need to return multiple rows of data that meet one or multiple criteria. It’s commonly used for extracting subsets of data, like all transactions for a particular customer or all sales within a given date range.

You can also apply multiple conditions to filter data based on various criteria. For instance, in your Sales Spreadsheets Tracker, you could filter sales by both customer name and date range simultaneously.

FILTER Syntax:

=FILTER(range, condition1, [condition2], ...)

Explanation of Arguments:

• range: The range of data you want to filter. This can include multiple columns and rows.
• condition1: The first condition that must be met for the rows to be included in the result. This can be any logical expression, such as =, >, <, etc.
• [condition2], [condition3], … (optional): Additional conditions you can apply to filter the data further. You can add multiple conditions if needed, and they will be combined with the AND logic (meaning all conditions must be true).

Example 1: Filter by Single Condition

Let’s say you have a Sales Data Sheet where:

• Column A contains Customer Names
• Column B contains Sale Dates
• Column C contains Sale Amounts

If you want to filter and show all sales data for the customer “John Doe,” use the following formula:

=FILTER('Sales Data'!A:C, 'Sales Data'!A:A = “John Doe”)

In this example:

• ‘Sales Data’!A:C is the range of data to filter. It includes three columns: customer names (column A), sale dates (column B), and sale amounts (column C).
• ‘Sales Data’!A:A = A2 is the condition. It filters the data to show only rows where the customer name in column A matches the value “John Doe”.

Example 2: Filter by Multiple Conditions

Now, let’s say you want to filter the data based on two conditions or rules: Customer Name and a date range. For example, you want to show all sales for “John Doe” that occurred on or after January 1, 2023.

=FILTER('Sales Data'!A:C, 'Sales Data'!A:A = “John Doe”, 'Sales Data'!B:B >= DATE(2023, 1, 1))

Explanation:

• ‘Sales Data’!A:C is the data range.
• ‘Sales Data’!A:A = A2 filters by customer name (same as in the first example).
• ‘Sales Data’!B:B >= DATE(2023, 1, 1) filters sales that occurred on or after January 1, 2023.

This formula returns all rows that match both conditions: the customer’s name and the date condition.

How FILTER Differs from XLOOKUP:

• FILTER returns multiple rows of data, while XLOOKUP returns only a single value.
• FILTER is useful when you want to dynamically display multiple rows based on conditions (e.g., multiple transactions for a customer). It’s perfect for data extraction based on several criteria.
• XLOOKUP, on the other hand, is focused on finding a single matching result and returning the associated value from another column.

INDEX + MATCH

INDEX + MATCH is indeed the best choice 👍 when working with pivoted or transposed tables. Using the combination of Index and Match formula, we can search for value based on column and row header.

In a pivoted table, data is typically organized such that:

• The row labels (e.g., products, customers, dates) are on one axis (usually on the rows).
• The column labels (e.g., months, regions) are on the other axis (usually on the columns).
• The data you want to retrieve (e.g., sales amounts, prices) is inside the table, with a complex relationship between rows and columns.

In pivoted tables, rows and columns may represent different variables, and INDEX + MATCH offers the flexibility to search for values across non-adjacent columns and rows, making it ideal for such structures.

INDEX + MATCH Example

Imagine you have a pivoted sales table with months in the columns and products in the rows. You want to find the sales amount for a specific product in a given month.

Pivoted Table Structure:

In this example:

• Product names are in the rows.
• Months are in the columns.
• Sales values are in the intersection of the rows and columns.

To find the sales amount for Product A in February, you can use the following INDEX + MATCH formula:

=INDEX(‘Sales Data’!B2:D4, MATCH(“A”, ‘Sales Data’!A2:A4, 0), MATCH(“February”, ‘Sales Data’!B1:D1, 0))

Explanation:

• 'Sales Data'!B2:D4: The range containing the sales data (excluding row and column headers).
• MATCH("A", 'Sales Data'!A2:A4, 0): This finds the row for Product A in the row labels (column A).
• MATCH("February", 'Sales Data'!B1:D1, 0): This finds the column for February in the column labels (row 1).
• INDEX then returns the value at the intersection of these two matches, which is 120

Why INDEX + MATCH Works Well in This Case:

• Flexibility: It allows you to search for values both in the rows and columns, making it perfect for pivoted or transposed data.
• Non-adjacency: In pivoted tables, the data is often not arranged in adjacent columns or rows, and INDEX + MATCH handles this easily, unlike VLOOKUP or XLOOKUP which require a specific order of columns.
• Dynamic Lookups: You can use MATCH to dynamically find both the row and column positions, allowing for a dynamic lookup in pivoted data without needing to manually reference columns.

Key learnings

XLOOKUP is typically used to return one result (the first match found), though it can return multiple values in some specific array-based scenarios. 

FILTER is the go-to function when you want to return multiple results (entire rows or ranges) based on one or more conditions.

INDEX + MATCH offers the most flexibility for lookups, especially in pivoted tables or when working with non-adjacent columns, providing control over complex lookups.

Conclusion

While each formula serves a specific purpose, understanding when and how to use them will make your work in Google Sheets & MS Excel more efficient and powerful. It’s not about how many formulas you’ve learned, but about how effectively you can solve your problems and streamline your workflow in spreadsheets.

If you enjoyed our Google Sheets and Microsoft Excel blog, share and save this article for future reference. 

IF you have strong desire to master ALL lookup functions, click here for all Microsoft Excel lookup formulas guide. Otherwise, click here for Google Sheets Lookup formulas reference.