Skip to Content

How to Lookup Data in Google Sheets (7 Methods with Examples)

Posted on Last updated:

Looking up data is one of the most common tasks people perform in Google Sheets. Whether you need to find a specific value, validate data entered in a cell, or return a value from a separate table, Google Sheets provides several powerful lookup functions to meet your needs.

In this beginner’s guide, we will cover seven remarkable ways to lookup data in Google Sheets using examples that even a beginner with no prior experience will understand.

The key to effectively looking up data in Google Sheets is understanding the differences between each lookup function and knowing when to use each one. For example, VLOOKUP is ideal for simple vertical lookups, while INDEX and MATCH can handle more complex lateral and vertical lookups. The FILTER function allows you to filter data dynamically, while IMPORTRANGE imports data from other Sheets.

By the end of this article, you will have a solid foundation on the most common ways to lookup data in Google Sheets. You’ll be equipped with the knowledge to choose the suitable lookup method for each situation. 

Whether you need to search a product list, analyze survey responses, or import data from another spreadsheet, this guide has all the necessary information you need and more. Let’s dive in and learn how to lookup data in Google Sheets like a pro. 

Access Sample Sheet

Interested in following along with today’s tutorial? Click the link below to copy our sample sheet.

Click Here to Copy This Sheet

Example 1: Lookup Data in Google Sheets Using Vlookup

The VLOOKUP function is one of the most helpful lookup functions in Google Sheets. Its popularity stems from its simplicity and versatility for basic vertical lookup tasks.

At its core, VLOOKUP allows you to search down the first column of a table for a lookup value and then return the corresponding result from another column. It’s a simple way to connect matching data across columns.

The VLOOKUP function may seem tricky at first glance. However, taking the time to walk through a detailed example will demonstrate it’s not too difficult to grasp. With a simple table structure and a clear understanding of each argument, the logic of VLOOKUP will become clear. 

Understanding the VLOOKUP Syntax

Before we walk through a detailed example, let’s quickly cover the syntax for the VLOOKUP function:

VLOOKUP ( search_key, range, index, [is_sorted] )

  • search_key: The value you want to search for in the first column. This can be text, a number, or a cell reference.
  • range:  The table range with columns to search and return data from.
  • index: The column number in the range to return data from. The index starts at 1.
  • [is_sorted]: An optional boolean for sorted data.

Note that the index starts counting from 1. So, if you specify an index less than 1, you’ll get an error.

Let’s walk through a VLOOKUP example using sample data for a simple product price list. 

Here is what our sample data in Google Sheet will look like: 

Our objective with this example is to lookup the price for a fruit name entered in cell D2 using the VLOOKUP function in Google Sheets.

Now that we know exactly what we want to achieve, here are the steps to take.

Step 1: Choose an Empty Cell 

To begin, we need to select a blank cell in our spreadsheet where our lookup results will be displayed. In this example, let’s use cell E2 for this purpose.

Step 2: Enter VLOOKUP Formula

Now, let’s enter our VLOOKUP formula. To do that, navigate to the formula bar and type in the following formula: 

=VLOOKUP(D2,A1:B11,2,FALSE)

Essentially, this VLOOKUP formula searches column A for the value in cell D2 and returns the price from column B.

The critical components of the VLOOKUP formula are:

  • D2:  The lookup value we want to search for. This can be a cell reference or text value.
  • A1: B11: The table array with columns to search (A) and return data from (B).
  • 2: The column number in the array to return (2=column B).
  • FALSE: Specifies an exact match is required.

Step 3: Press Enter 

After setting up the VLOOKUP formula, as explained in the previous step, simply press the Enter key on your keyboard. This action will instruct Google Sheets to produce the lookup result in the cell you’ve selected.

If you did everything right, you should have something like this: 

From the image above, you can see that we have been able to use the VLOOKUP formula to lookup the price of bananas. Now, that’s one way to lookup data in Google Sheets. That wasn’t too hard, was it?

The simplicity of VLOOKUP makes it a great starting point for beginners learning to lookup data in Google Sheets. With some sample product data, anyone can easily understand how to search vertically and retrieve matching information.

Example 2: Lookup Data in Google Sheets with HLOOKUP Function

While VLOOKUP is ideal for vertical lookups, the HLOOKUP function allows you to perform horizontal lookups in Google Sheets.

As the name suggests, HLOOKUP searches horizontally across the first row of a table for the lookup value and then returns data from the matched column.

HLOOKUP is perfect when your data structure is transposed or you simply need to look up values from left to right.

Understanding the Syntax of the HLOOKUP Formula

Before we get any further, let’s understand the syntax for the HLOOKUP function. 

The arguments are similar to VLOOKUP:

HLOOKUP(search_key, range, index, [is_sorted])

  • search_key – The lookup value to match in the first row
  • range – The table range to search across and return data from
  • index – The row number to return data from
  • [is_sorted] – Optional sort order argument

Now that you understand the HLOOKUP function syntax. Let’s go over an example using sample data for fruit prices. 

Here is what our sample data looks like in Google Sheets. 

Note: Our objective with this example is to look up a fruit’s price by searching horizontally across the row of fruit names using HLOOKUP in Google Sheets.

With our sample data nicely put together, let’s jump into detail on how to lookup up data in Google Sheets using the HLOOKUP function. 

Step 1: Choose A Blank Cell

Let’s start by selecting a blank cell in our spreadsheet where we want the result of our HLOOKUP to appear. In this case, we’ll use cell B6 for our example. Remember, this empty cell is where the HLOOKUP output will be displayed.

Step 2: Type In the HLOOKUP Formula

With the cell where you want your HLOOKUP result generated selected, navigate to the formula bar and type in the following formula: 

=HLOOKUP(“Banana”,A1:E2,2,FALSE)

The critical components of the formula are: 

  • “Banana”: The lookup value to search for in row 1. We used the text “Banana” but it could be a cell reference.
  • A1:E2: The table range to search across (row 1) and return data from (row 2).
  • 2: The row number (2) to return data from.
  • FALSE:  Requires an exact match in row 1.

Step 3: Press Enter

Once you’ve input the HLOOKUP formula as demonstrated, the next step is to execute it on your data to obtain the result you’re looking for. To accomplish this, simply press the Enter key on your keyboard. 

This straightforward action commands Google Sheets to produce the HLOOKUP result in your chosen cell.

Here is what ours looks like: 

From the screenshot above, you can see that we have been able to lookup up the price for Banana using the HLOOKUP function. From everything we have covered so far, we are sure you agree that the entire process is straightforward. 

While beginners often default to VLOOKUP, it’s good to be aware of HLOOKUP for horizontal data sets. The lookup logic remains the same – just across rows rather than down columns.

Important Notes on HLOOKUP

  1. The is_sorted argument works the same as VLOOKUP. If you set is_sorted to TRUE, the search row must be sorted A-Z.
  1. HLOOKUP can only return rows below the search row. This means It cannot lookup values above.
  1. HLOOKUP is case-insensitive. “Apple,” “apple”, and “APPLE” are treated as matching values.

To summarize:

  • Set is_sorted to TRUE only if your search row is sorted. Otherwise, use FALSE.
  • Ensure your return row is below the search row in your table range.
  • HLOOKUP doesn’t care about upper/lower case when searching. But be consistent in your data.

These notes shed light on some unique behaviors of HLOOKUP to be aware of. Understanding these details will help you use HLOOKUP effectively and avoid unexpected results.

Example 3: Lookup Data In Google Sheets with INDEX MATCH Function

While VLOOKUP and HLOOKUP cover basic vertical and horizontal lookups, the INDEX and MATCH combination unlocks more advanced lookup capabilities in Google Sheets.

INDEX returns a value from a chosen row and column in a table. MATCH searches for the lookup value and returns its position. Together, they can lookup values from any point in a table dynamically.

Here are some of the critical strengths of INDEX MATCH:

  • Lookup values from any column, left to right or up and down
  • Handle complex lookups beyond basic data sets
  • Avoid VLOOKUP limitations with column insertions/deletions
  • Reference any table range, named ranges, or sheet names

Here is precisely what the INDEX MATCH function does:

  1. MATCH finds the lookup value position
  2. INDEX returns the data from that matched position

It takes some hands-on experience to grasp, but the flexibility of INDEX MATCH is worth the learning curve for power users. While VLOOKUP is simpler, INDEX MATCH is more versatile and scalable.

Before we explore a practical example that shines a light on the MATCH function, let’s quickly understand the syntax of this function. 

Understanding MATCH Syntax

The MATCH function in Google Sheets helps find the position of a lookup value within a range. 

Its syntax is:

MATCH(lookup_value, range, [match_type])

The arguments are:

  • lookup_value: The value you want to find the position of. It can be text, number, or cell reference.
  • range: The column or row where you want to search for the lookup value.
  • [match_type]: An optional argument for how to search:


1 = Ascending sorted range (default)
0 = Exact match
-1 = Descending sorted range

To summarize:

  • MATCH searches for the lookup value in the range provided
  • It returns the relative position or index number of that lookup value
  • Use match_type to specify if the range is sorted or an exact match is needed

Now that we know the syntax for the MATCH function. Let’s walk through an example of using MATCH to find the position of a lookup value in a table.

Here is the sample data we will use for this example: 

Our objective with this example is to use the MATCH function to find the position of Banana in the Fruit column. 

With our sample data neatly put together, let’s show you the steps to use the MATCH function, as per our sample data. 

Step 1: Choose An Empty Cell

First things first, let’s choose an empty cell in our spreadsheet. Ideally, this is where we want the match result to be generated. For this example, we will go with cell G2. 

Step 2: Type the INDEX MATCH Formula

Having decided where we want the match result generated, let’s quickly input the MATCH formula. 

To do that, navigate to the formula bar and type in the following formula: 

=MATCH(“Banana”, A1:A5, 0)

Here is a breakdown of the MATCH formula we used: 

  • “Banana”: Our lookup value
  • A1:A5: The column to search
  • 0: Require exact match

Step 3: Press Enter

After inputting the MATCH formula like we showed you in the previous step, let’s quickly execute the formula. To do that, you only need to press the Enter button on your keyboard. 

If you followed the steps precisely as we detailed so far, you should have something like this: 

By leveraging the MATCH function, we have been able to find where our lookup value is located in a range. 

This example builds the foundation for using MATCH with INDEX to lookup data based on position.

Understanding the INDEX Syntax

The INDEX function in Google Sheets returns a value from a specified range based on a row and column position. 

Here is what the syntax looks like:

INDEX(range, [row], [column])

The arguments are:

  • range: The table or range to lookup from. Required.
  • [row] – Optional row position to return data from. Starts at 1.
  • [column] – Optional column position to return data from. Starts at 1.

To summarize:

  • INDEX needs a range to lookup in. This is the required 1st argument.
  • The row and column arguments are optional to specify the exact cell.
  • Row and column positions start counting from 1.

Now that you understand the INDEX function syntax. Let’s show you some practical ways to maximize its power. 

For this example, we will use the same sample data as the one we used for the MATCH function.

Our objective for this example is to use the INDEX function to lookup the stock count for Banana. 

Here is what our sample data looks like: 

Now, let’s go over the steps together: 

Step 1: Choose an Empty Cell

As always, you need to choose an empty cell in your spreadsheet. This is where you want the INDEX result to be generated. For this particular example, we will go with cell G5. 

Step 2: Type the INDEX formula

After choosing an area in your spreadsheet where you want the INDEX result generated, navigate to the formula bar and type in the following formula: 

=INDEX(D1:D5,3,1)

Step 3: Press Enter

Having typed the INDEX formula, press the Enter button on your keyboard. The result should be generated in the selected cell. 

Here is what it looks like:

Using the INDEX function, we have been able to look up Banana’s stock count. Easy right? We guess so. 

Example 4: Using INDEX and MATCH Function To Lookup Values

By now, we are sure you understand how the INDEX and MATCH functions work individually. But do you know that you can put both of these functions together to look up value in your spreadsheet?

We will show you how to go about it in this section: 

To show you how to seamlessly combine the INDEX and MATCH functions to look up value in Googe Sheets, we will use the following sample data: 

Our objective with this example is to lookup the price of Banana using the INDEX MATCH function.

Here are the steps to take: 

Step 1: Choose an Empty Cell

Begin by selecting a blank cell in your spreadsheet, which will be used to display the result of the MATCH function. Essentially, we’re utilizing the MATCH function to determine the position of ‘Banana’ in column A. For this purpose, let’s use cell F2 to show the MATCH value.

Step 2: Type the MATCH formula

Head over to the formula bar and type the following formula: 

=MATCH(“Banana”,A1:A4,0)

Step 3: Press Enter

Now that the MATCH formula has been entered correctly. Go ahead and press the Enter button on your keyboard. Google Sheets will generate the MATCH position of Banana in column A. 

Here is what we got: 

Step 4: Combine INDEX and MATCH Function

Now that we know Banana’s position in column A. Let’s use the INDEX and MATCH functions to return the price of that matched row. 

First, we need to choose where the INDEX MATCH result will be. For this, we will go with cell G2. 

Step 5: Type the INDEX MATCH Formula

Now, let’s execute the INDEX MATCH formula like we showed you. To do that, head over to the formula bar and type the following formula: 

=INDEX(C1:C4,MATCH(“Banana”,A1:A4,0))

This formula looks up the price from column C, row 2, and returns the price of Banana. 

Step 6: Hit Enter

Once you’re done inputting the INDEX MATCH formula like we described above, hit the Enter button on your keyboard. 

This action should instantly generate the result of the INDEX MATCH function in the selected cell. 

Here is what ours looks like: 

By chaining MATCH and INDEX together, we can lookup data based on a search key, overcoming the limitations of VLOOKUP. This unlocks powerful and dynamic lookups in Google Sheets.

Key INDEX MATCH Features

The INDEX MATCH combination has some valuable capabilities to be aware of:

  • Flexible lookups: Unlike VLOOKUP, INDEX MATCH can lookup values to the left of the search column, not just to the right. This allows more flexibility in table structures.
  • Case-sensitive matching: INDEX MATCH differentiates between uppercase and lowercase text. This allows for more precise matching, unlike the case-insensitive lookups of VLOOKUP.

In summary:

  • INDEX MATCH can lookup left and right, not just right like VLOOKUP
  • It matches case-sensitive text, while VLOOKUP ignores case

These features give INDEX MATCH advanced lookup powers. But with added flexibility comes a bit more complexity. Taking time to learn this powerful combination pays off for heavy spreadsheet users.

Example 5: Lookup Data Using the DGET Function in Google Sheets

While VLOOKUP is the most popular lookup function, DGET offers a powerful alternative using database-style search logic.

DGET allows you to lookup values by specifying criteria across multiple columns. It searches your data range for rows matching the criteria and returns a single result.

The key features of DGET:

  • Lookup values using criteria across multiple columns
  • Retrieve a single match result rather than an array
  • Use database-style criteria logic like “column=value”

To use DGET:

  1. Set up your criteria to define the lookup match
  2. Specify the column to return data from
  3. Only one matching row can exist, or an error occurs

DGET requires a rigid table structure and takes some practice. However, the ability to lookup based on complex criteria makes it a valuable tool for specific use cases.

Understanding DGET Function Syntax

Before we go over an example showing you how to use the DGET function to lookup values in Google Sheets, it’s crucial we understand the syntax of this function. 

Here is what the syntax of the DEGET function looks like: 

DGET(database, field, criteria)

The arguments are:

  • Database: This refers to a set of data organized in a table format, complete with headers for each column. This parameter is essential and cannot be omitted.
  • Field: This is the specific column from which you want to extract data. You can identify the field either by its column number (like 1, 2, etc.) or by its name (enclosed in double quotes, such as “Orders Count”). Alternatively, you can use a cell reference that contains the column’s header (for example, B1).
  • Criteria: This consists of a cell range specifying the conditions that the data must meet. The criteria range typically includes a header in the first row and the actual criteria in the second row. You can also use a blank cell as a criterion if necessary.

Now that you have an understanding of the DGET function and its unique syntax. Let’s review a practical example together to show you how the DGET function works. 

With this example, our objective is to lookup the price of a specific fruit from the sample data by defining criteria across multiple columns using the DGET function in Google Sheets.

Here is the sample data we will use for this example: 

Step 1: Setup the Criteria to Lookup the Price of Banana

If you haven’t set up a criteria yet to look up the price of Banana in your spreadsheet, we suggest you do so right away. You can select any area of your spreadsheet to do that. 

We have set up our lookup criteria for this example in cell F1:H2. 

Step 2: Choose A Blank Cell

Once you have established your lookup criteria, select an empty cell within the area of the lookup criteria. This cell will be used to display the price of the Banana. In this tutorial, we’ll use cell H2 for this purpose.

Step 3: Enter DGET Formula 

Now, let’s make the magic happen by applying our DGET formula. What you want to do is navigate to the formula bar and type in the following formula: 

=DGET(A1:C5,”Price”,F1:G2)

If you’re using your own data, you can simply adjust the range and lookup criteria to suit what you want. 

Step 3: Hit Enter

With the DGET formula entered like we showed you, go ahead and hit the enter button on your keyboard. Google Sheets should instantly generate the result in the selected cell. 

Here is what ours looks like:

Essentially, the DGET formula will lookup the required value from the sample data table using criteria to filter the results.

Within the function:

  • The first argument is the database range A1:C5, which contains the table to search.
  • The second argument, “Price,” references the column header to return data from.
  • The criteria range F1:F2 contains the header and criteria values to filter by.

These criteria will search for rows in A1:C5 where Fruit = Banana and Origin = Ecuador.

DGET will return the Price value from the one matching row meeting both criteria.

You can adjust the criteria values to lookup prices for other fruits. Just ensure the criteria headers match the database column headers, and update the values to filter by different conditions.

Key Things to Note About the DGET Function

  1. Returns one match – DGET will only return the first match for your criteria. Additionally,  Dragging the formula down does not return multiple matches like VLOOKUP.
  1. Requires unique match – If multiple rows match the criteria, DGET will produce a #NUM! Error. So, it’s important you add additional criteria to ensure only one match exists.

In summary:

  • DGET is designed for single-match lookup, not accumulating matches.
  • Criteria should be written to filter for one unambiguous match to avoid errors.
  • Additional criteria can help isolate a unique match if needed.

Understanding these behaviors will help you construct effective criteria and use DGET successfully.

Example 6: Lookup Data Using the LOOKUP Function in Google Sheets

The LOOKUP function in Google Sheets might seem a bit tricky at first, especially if you’re new to using spreadsheet functions. It’s different from other lookup functions like VLOOKUP and HLOOKUP, both in how you write it (its syntax) and how it works.

Here’s a more straightforward way to understand the LOOKUP function:

  • What it Does: The LOOKUP function helps you find specific information in your spreadsheet. It can search through your data either row by row (vertically) or column by column (horizontally).
  • How it Works: You give the function a value to look for, which we can call a “key” value. This is the piece of data you’re interested in finding. Then, you tell the function where to look for this key value, which is your search range. The function goes through this range to find the key value.
  • Getting Results: Once the LOOKUP function finds the key value, it doesn’t just stop there. It also returns a related piece of information (a cell value) from a different range you specify. This range is where the result you want is located, corresponding to the position of your key value.

Although initially, it can be confusing, once you get the hang of the LOOKUP function, it becomes a handy tool in your Google Sheets arsenal, offering an alternative way to find data compared to VLOOKUP and HLOOKUP.

Explaining the LOOKUP Function Syntax 

The LOOKUP function in Google Sheets helps you find specific things in your spreadsheet. It might look a bit complex at first, but it’s quite straightforward once you understand its parts:

How to Write It

The basic format is: 

LOOKUP(search_key, search_array|search_range, [result_array])

Breaking Down the Parts:

  • search_key: This is what you’re looking for. It’s a specific piece of information or value in your sheet.
  • search_array | search_range: Here, you have two choices:
  • search_array: A specific list where you want to look for your search_key. If you use this, you’ll also need to tell the function where to find the answer you want, which is the result_array.
  • search_range: A bigger area that includes where to look for your search_key and where to find the answer. If you use this, you don’t need a separate result_array.
  • result_array: This is a list of answers related to your search_array. You only need this if you’re using the search_array option.

Important Things to Know

  • Approximate Matches: The LOOKUP function doesn’t always find an exact match for your search_key. Instead, it finds the closest match. So, if the thing you’re looking for isn’t there, it will pick the next best thing.
  • Handling Duplicates: If duplicates exist, LOOKUP will use the last matching value it finds.
  • Sorting Matters: This function works best when your data is sorted in ascending order. It’s designed to work with data arranged this way.

Once you get used to how LOOKUP works, you’ll find it a handy tool for searching through your data in Google Sheets, especially when you don’t need exact matches or when working with sorted lists.

Let’s see an example demonstrating how the LOOKUP function works in Google Sheets.

Suppose you have a list of products and their prices. You want to look up the price of a specific product. 

Here is the sample data we will use to demonstrate that. 

Our objective with this example is to find the price of Cherry. And since our data is sorted alphabetically by product name, we can go ahead and use the lookup function. 

Here are the steps to take. 

Step 1: Choose an Empty Cell

Let’s start by choosing an empty cell in our spreadsheet. For our example, we will select cell E2. This is where we want our lookup value to be. 

Step 2: Enter the Lookup Formula

After choosing where you want the lookup value, head to the formula bar and type in the following formula. 

=LOOKUP(“Cherry”, A2:A6, B2:B6)

  • “Cherry” is our search_key. This is what you’re looking for.
  • A2:A6 is our search_range. This is where the function looks for the “Cherry.”
  • B2:B6 is our result_array. This is where the function finds the price related to “Cherry.”

Step 3: See Result

With the Lookup formula typed in correctly, simply press the Enter button to see the result. If you did everything exactly as we explained above, you should get something like this: 

Notes

  • If you look up a product not in the list, like “Fig,” the LOOKUP function will return the price of the next closest product in the sorted list, which in this case would be “Elderberry” ($4.00).
  • Remember that LOOKUP works best when the data in the search range is sorted in ascending order. If the data isn’t sorted, you might not get the expected result.

Example 7: Lookup Data Using the FILTER Function in Google Sheets

The FILTER function in Google Sheets is a versatile tool that allows you to extract or “filter out” specific data from a larger dataset based on certain conditions you set. It’s particularly useful when you want to see only those rows of data that meet your specified criteria. Here’s how to understand and use the FILTER function:

Understanding the FILTER Function Syntax

The FILTER function syntax typically looks like this: 

FILTER(range, condition1, [condition2, …])

Breaking Down the Parts:

  • Range: This is the area of your sheet where you have the data you want to sift through.
  • Conditions: These are the rules or criteria you set to decide which data rows you want to see. You can have one or more conditions.

Now that you fully understand the FILTER function syntax. Let’s consider an example. 

Suppose we have a list of fruits with their respective prices and quantities, and we want to filter out fruits that cost less than $1.00. 

Here is how we will go about it: 

Step 1: Choose an Empty Cell

Like we have been doing with the other examples, we need to choose an empty cell in our spreadsheet where we want the filtered result to be generated. For this example, we will choose cell F2. 

Step 2: Type the Filter Formula

Now, let’s proceed to type the FILTER formula. To do that, navigate to the formula bar and type in the following formula: 

=FILTER(A2:C6, B2:B6 < 1)

  • A2:C6 is our data range.
  • B2:B6 < 1 is the condition. It means we are looking for rows where the price (in column B) is less than $1.00.

Step 3: Hit Enter 

After typing the Filter formula like we showed you, it’s time to see our filtered result. To get the result, all we need to do is press the Enter button on our keyboard. Google Sheets should instantly generate the result in the selected cell. 

Here is what we got: 

From the example we have covered, you can see how easy it is to lookup data in Google Sheets using the FILTER function. Compared to the other examples, we are sure you’ll agree that using the FILTER function to lookup data in Google Sheets is pretty straightforward. 

Final Thoughts

In closing, being able to efficiently lookup data in Google Sheets is an invaluable skill for any spreadsheet user. While functions like VLOOKUP, MATCH, INDEX, and FILTER may seem intimidating at first, taking the time to walk through hands-on examples makes the lookup logic for each one clear.

The key is matching the right lookup tool to your specific data structure and use case. VLOOKUP handles simple vertical lookups, MATCH and INDEX combine for advanced positional lookups, FILTER dynamically filters data, etc. Becoming fluent with Google Sheets’ versatile lookup functions allows you to retrieve insights from your data efficiently.

For beginners, start by getting comfortable with VLOOKUP for basic vertical lookups. As you build experience, incorporate more advanced functions like INDEX MATCH and FILTER into your Google Sheets toolkit. Don’t be afraid to experiment on sample data to develop lookup skills that apply to real-world scenarios.

The payoff is unlocking the ability to readily answer questions from your data with a simple lookup formula. So dive in, get hands-on with lookup functions, and soon, you’ll be able to extract just the information you need from large datasets quickly. With the power of lookups, you can get far more value out of your Google Sheets data.