If you are working with datasets stored in different workbooks or worksheets, often the headache would be finding a way of merging information from multiple sheets based on a specified key column.

You may frequently come across a situation where some part of the dataset is stored in a sheet and some other related part of the data is in another sheet.

You may want to join the data across those multiple sheets together in a single sheet to do further analysis.

Combining the data that spans across multiple sheets into a single sheet usually means you need to lookup data based on a common column to match it with the related data.

This is known as a data lookup. You can lookup data across multiple tables, sheets, or spreadsheets. You can even lookup images inside the cell in Google Sheets.

For example, say you have the customer names and their email ID stored in a sheet. Another sheet contains the email ids and quantity they ordered of a specific product from your organization.

You want to combine this information into a single table containing customer names, email ids, and the quantity they ordered.

To perform this task inside Google Sheets, you will need to lookup the data from another sheet based on a specific column that is common in both tables (the email ID).

Throughout this article, you will learn about seven different ways to lookup data inside Google Sheets. Download the example workbook to follow along.

The Datasets

The examples throughout this post use the same two data tables.

1. There is a table that stores the Email ID and Customer Name.
2. And another table that stores the Email ID and Order Count.

Each data table consists of ten customers, and the screenshot above will give you a better idea of its structure.

Lookup Data Using the VLOOKUP Function in Google Sheets

The VLOOKUP function is by far the most popular function available in Google Sheets.

The VLOOKUP function is used to lookup data from one table to another. The function got its name from an abbreviation of Vertical Lookup, and as the name suggests, this function looks for a match vertically from the top of a table to the bottom.

The VLOOKUP function is an integral part of Google Sheets. The function searches for a key or unique value from the first column of a specified range and returns a value situated in another column for the same row.

You might find this function hard to deal with initially. However, once you go through it, you will agree that it is not that hard to understand.

Syntax for the VLOOKUP Function

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

• search_key – It is a mandatory argument that specifies the value you would like to search for in a given column. It is also known as the lookup value or a unique value. Ex. You would like to search “Lalit,” 12, or value present inside cell A2.
• range – The range inside which you want the search operation to happen. Usually, the range is more than two columns, and a first column is where the system searches for unique value. It is a mandatory argument
• index – The column number from which you want to return a value after the lookup inside the range. This argument is also mandatory.

Note that the index for the column starts from 1. If you specify an index value less than 1, the formula will give a #VALUE! Error.

On the other hand, if you specify the index value that falls beyond the column numbers from the given range, you will get the #REF! Error.

• [is_sorted] – It is an optional argument that specifies if the first column from the range (i.e., the column where search operation happens) is sorted or not. TRUE means sorted; FALSE means not sorted.
  • If you ignore this argument (as it is optional) then by default TRUE will be considered and in that case, the column where searching happens needs to be sorted in ascending order either A to Z or smallest to largest.
  • This argument decides whether the match will be exact or partial. If provided with value TRUE, this argument will search for an approximate match. If not found, it will return the value that has a closest match with the search_key.
  • If provided with FALSE, the formula will search for the exact match and there is no need for the column to be sorted in ascending order. If no exact match found, the formula will return a #N/A Error.

Example

Even though these arguments look a bit confusing, the actual working of the formula is pretty simple. Let’s see how you can use the VLOOKUP function to lookup values from different sheets.

The problem you are facing right now is that you have two databases, as discussed above. Database 1 has customer email id and their names, whereas Database 2 has customer email ids and order count.

You now want to pull the order count in Database 1 based on customer email ids so that the entire data will be in one place for further analysis. You can use the VLOOKUP function to get this task done.

= VLOOKUP ( A4, 'Database 2'!$A$1:$B$11, 2, FALSE )

The above formula will search for the email in table 2 and return the corresponding order count.

• As you want to search the values based on the customer email ids, cell A2 from Database 1 is the search_key argument inside the formula.
• The range is from where you want to pull values based on the unique value. In this example, the range should be A1:B11 from sheet Database 2. This range will not change. Hence it would be best if you fix it by hitting the F4 button from your keyboard.

Note that keeping or removing the header cells inside the VLOOKUP formula doesn’t change or alter the output.

• Now, you want to fetch the Orders Count column from Database 2. It is the second column in the range, and hence you should provide value for the index parameter as 2.
• Finally, you want to lookup the values wherever the exact match for email ids appears. Therefore, type FALSE as a value for the is_sorted argument.

The formula that does this task is as shown above, and you can directly copy and paste it in your spreadsheet provided that you have the same data structure as I have in the demo spreadsheet.

You need to drag the formula from C2 across rows to get the Orders Count for each customer. You can do it by taking your mouse cursor to the blue square in cell C2 and double click to expand the formula down the rows.

You can also select the range C2:C11 and then hit Ctrl + D as a shortcut to populate the formula down the cells. Or you can simply copy and paste it down the rows.

After you drag the formula down, there will be a few #N/A error values. These are errors because the formula cannot find those emails inside the Database 2 sheet. And rightly so, those customers are not at all on the second list.

Also, for customers with duplicate orders placed, the formula only returns the first value found.

Notes

• Note 1: While searching for an exact match, if two or more values match with the search_key, the function will return the one appearing first in the column.
• Note 2: The VLOOKUP function can only lookup the data from its right. And you cannot lookup values from the left of the search_key column. It is one of the main drawbacks of the function.
• Note 3: The Google Sheets VLOOKUP function fails when you want to lookup case-sensitive data. The function can not differentiate between upper and lower case text strings. For the VLOOKUP function, the string “lalit,” “Lalit,” and “LALIT” are identical.

Lookup Data Using the HLOOKUP Function in Google Sheets

The HLOOKUP function, on the other hand, is not as popular and widely used as VLOOKUP is. However, the two functions work almost identically.

HLOOKUP can be used instead of VLOOKUP when dealing with a transposed dataset.

The H inside the function name stands for Horizontal. This function looks the values up horizontally left to right in a row. The arguments for this function are also the same as those of the VLOOKUP function with minor alterations.

The function is designed explicitly to lookup values horizontally. The function searches for a key or unique value from the first row of a specified range or table and returns a value situated in another row for the same column.

Syntax for the HLOOKUP Function

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

• search_key – It is a mandatory argument and specifies the unique or key-value based on which you want to perform the lookup.
• range – It is a mandatory argument that specifies a horizontal range of cells or table from which you want to return values based on the first argument provided.
• index – Specifies a row number from which you want to pull the data into another table. It is a mandatory argument.
• is_sorted – A logical argument that specifies whether the first row from the range is sorted (TRUE or keep blank) or not (FALSE). If the row is sorted, the formula will return the approximate match, and if it is not sorted, the formula will generate an exact match. It is an optional argument.

Example

Suppose inside the Database 2 sheet you have horizontal data stored as shown above. You want to lookup and pull the Orders Count based on Email inside the Database 1 sheet.

Follow the steps below to lookup values using the HLOOKUP function inside Google Sheets.

= HLOOKUP ( A20, 'Database 2'!$B$27:$K$28, 2, FALSE )

For the search_key argument, input cell A20 as the value for which you would like to look up results.

The range is from the Database 2 sheet, and it is the horizontally stored table. The value for this argument should be B27:K28.

This range is not going to change, and hence it is good to fix it. Use the keyboard button F4 to fix this range. The formula will search the key-value in the first row of each column to lookup the data.

Since you want to pull the Orders Count, which is stored on the second row of each column, use value 2 for the index argument.

Finally, you want the exact match to be found and hence add FALSE for the is_sorted argument.

The formula that does this task is as shown above. Feel free to copy and use it when you are working with the demo spreadsheet.

You have to drag the formula down across cells C20:C29 to apply the formula on all cells, and you get all Orders Count based on the Email.

Again, the #N/A error is for customers whose orders count is not present in the second list.

In case of duplicate orders placed, the first match found is picked up by the HLOOKUP formula. Same as the VLOOKUP function.

Notes

• Note 1: The rules for the is_sorted argument are the same as that of the VLOOKUP function. If the value for the argument is TRUE, then the first row from the range must be sorted in ascending order.
• Note 2: The HLOOKUP function can only return rows below the first row of the range argument.
• Note 3: The HLOOKUP function is also case-insensitive. It can’t differentiate between upper and lower case text. The text “lalit,” “Lalit,” and “LALIT.” are the same for the function.

Lookup Data Using the INDEX MATCH Functions in Google Sheets

To lookup the data inside Google Sheets, various functions can be used. But none are close to the efficiency of the INDEX and MATCH function.

These two functions when used individually, don’t produce too many valuable results. However, if used together, they can do wonders and generate powerful lookup results even more advanced than the conventional VLOOKUP function.

The Google Sheets INDEX MATCH is a combination of two functions that, if used in a specific sequence, can generate outputs similar to those generated by the VLOOKUP or HLOOKUP functions.

You can use it as an alternative to these two functions with more possibilities to lookup data as this function performs better than VLOOKUP or HLOOKUP.

Syntax for the MATCH Function

The MATCH function is simple enough to understand.

The Google Sheet MATCH function searches for a key or unique value throughout a range or array of cells and then returns that value’s relative position or index. It has the syntax as shown below.

MATCH ( search_key, range, [search_type] )

• search_key – is a mandatory argument that specifies the unique value you want to look for. It can be a text, a cell containing a value, or even a formula that returns a string or a number.
• range – specifies the one-dimensional array within which you search for the key value. It is a mandatory argument.
• [search_type] – is an optional argument that allows the function to decide how to search for the value. If ignored, the default value is set to 1.
  • If search_type is 1, the function assumes that the range provided is sorted in ascending order and returns the largest value less than or equal to the search_key.
  • if search_type is 0, the function searches for the exact match for the search_key.
  • if search_type is -1, the function assumes that the range provided is sorted in descending order and then returns the smallest value less than or equal to the search_key.

MATCH Example

= MATCH ( "rajan@pzk.com", $A$2:$A$11, 0 )

Here’s an example where you would like to find the relative position of the email ID for customer Rajan Arora inside your list. The formula that finds the row index containing the email ID is as above.

• Since you want to search the relative position of the email ID for Rajan Arora, this will be the search_key inside the MATCH function.
• The range within which you want to search the value is A1:A11 from the second list.
• Finally, you want to find the exact match and hence the zero as a value for the search_type argument.

Since the first appearance of the email ID is on the sixth row, the formula will return 6 as an output.

📝 Note: This email ID is duplicated and stored on the sixth and eighth rows. The function finds the first appearance on the sixth row and returnsthis position. It doesn’t check the subsequent rows after the first match is found.

Syntax for the INDEX Function

Unlike the MATCH function, the INDEX function returns the value rather than the position reference.

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

• reference – is a mandatory argument that specifies the range within which you want to look for the value.
• [row] – an optional argument that specifies the row index or row number you want to offset from the range’s start. If omitted, the default value is 0.
• [column] – an optional argument that specifies the column index or column number you want to offset from left to right. The default value is 0.

INDEX Example

Suppose you want the value for Orders Count associated with Rajan Arora from the second list. You can get it using the INDEX function as follow.

• Inside the cell D6, The INDEX function first takes the reference from which it want to lookup the values. Since, we want the Orders Count, it is in column B, more specifically in range B2:B11.
• We want the Orders Count for Rajan Arora. The first appearance for his email id is in sixth row. Therefore, we use 6 as row argument. You are actually telling the formula to offset si rows from start of the range.
• The column argument is 1 as that is the only column present inside the cell reference.

You complete the formula by adding closing parentheses and Bingo! The function returns 18 as a value from the cell reference.

The INDEX and MATCH Function To Lookup Values

Since you know how the INDEX and MATCH functions work individually, you can use them together to lookup the Orders Count from Database 2 for customers from Database 1 based on their Emails.

First, you will use the MATCH function to find the relative position of the email ID from Database 1 into Database 2. The formula to do this task is as shown below.

= MATCH (A35, 'Database 2'!$A$2:$A$11, 0 )

• Firstly, the search_key is inside Database 1 sheet in cell A35.
• For second argument, the range is from Database 2 sheet spanning across A2:A11. You should fix this range as it is not going to change.
• Finally, the value 0 is used for the search_type and allows the function to look for the exact value match based on the email ID.

This function will return the relative position of the Email stored in cell A35 within the Database 2 sheet. It is in the 4th row there.

=INDEX('Database 2'!$A$2:$B$11, MATCH(A35, 'Database 2'!$A$2:$A$11,0),2)

Now, you will use this MATCH formula inside the INDEX function as the row argument.

• For the first argument, give the entire range A2:B11 from the sheet Database 2 as a reference. Make sure to fix this range as it is not going to change.
• For the row argument, put the MATCH formula discussed above MATCH(A35, 'Database 2!'$A$2:$A$11, 0)
• And finally, you want to lookup the Orders Count which is a second column in the range. Therefore, put 2 as a value for the column argument.

Congratulations! You have successfully looked up the Orders Count for each Customer based on their Emails using a powerful combination of INDEX and MATCH functions.

Now drag this formula down across the rows C35:C44 to return the values for each Customer in the list.

You will see the #N/A values in some cells. Don’t worry! Those errors are because the system could not find those Emails and their relative Orders Count inside Database 2.

Notes

• Note 1: There are many advantages of using the INDEX MATCH function. The primary one is the ability to lookup values from the left side of the search column.
• Note 2: Another advantage of this function is its ability to differentiate between the text case. You heard it right! The INDEX and MATCH function can differentiate between upper and lower case text. With this ability, the function provided a more enhanced lookup method for those who have the case-sensitive data to lookup from.

Lookup Data Using the DGET Function in Google Sheets

Another powerful function to lookup data inside the Google Sheets is DGET.

This function belongs to the Database family and is the only lookup function from that family. All other functions coming from the Database family are aggregate functions.

This function is a valuable addition and a good replacement for the VLOOKUP function when it comes to looking up results.

The DGET function helps you return a single value based on the criteria in a SQL-like fashion. Note that this function can lookup values from multiple columns but can not return from multiple rows.

Syntax for the DGET Function

DGET ( database, field, criteria )

• database – a table or a tabular range or array with headers for each column. It is a mandatory argument.
• field – a column you would like to lookup values from. It can be a column index such as 1, 2, etc., or column name inside double quotes such as “Orders Count” or the cell reference containing the column header such as B1.
• criteria – a range of cells that contain criteria with a header. It usually has two rows (first row for header, second row for the criteria value). However, you can refer to a blank cell as a criterion as well.

Example

Now, consider this situation. You want to lookup the Orders Count for a customer with the email id “emily@rbc.com” inside cell B65 using the DGET function.

=DGET('Database 2'!$A$1:$B$11, "Orders Count",A64:A65)

The function above will take the required value from another list using the DGET function inside Google Sheets.

• Inside the function, the first argument is the database from where you want to search the values. In this case, you want to pull the values from range A1:B11 from sheet Database 2. Hence it is the first argument.
• Then comes the field which you want to lookup. You can refer to the field name or the cell reference for the column header. This example references “Orders Count” as that is the field you want to fetch. Remember to enclose it inside the double-quotes.
• Mention the criteria argument value as A64:A65 as you want to fetch the Orders Count for that particular email id. The heading value in cell A64 will need to match exactly the value used in cell A49.

You can copy the formula below while working on the demo sheet you download. Just change the values for criteria to get the Orders Count for another customer.

Notes

• Note1: The most significant thing you should note with this function is it can only lookup and return the value for the first criteria. It will not work like the VLOOKUP or INDEX MATCH function if you try to drag this formula down.
• Note2: If there are multiple matches for the same criteria inside the database, this function generates a #NUM! error. In such cases, you have to provide an additional condition to make this formula work.

Lookup Data Using the LOOKUP Function in Google Sheets

The Google Sheets LOOKUP function is a bit confusing for some users due to its different syntax and way of performing lookup both vertically and horizontally.

Besides, the confusing syntax of the function causes a few doubts. However, if you master this function, it is a handy alternative to the VLOOKUP and HLOOKUP.

The Google Sheets LOOKUP function searches for a unique or critical value inside a given search range, either vertically by rows or horizontally by columns.

It then returns a cell value based on the corresponding position of that unique value from a result range.

Syntax for the LOOKUP Function

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

• search_key – is a mandatory argument that specifies a key value that you are searching for.
• search_array | search_range – You can either use the search_array from where you could search the value. Or use the entire range containing the result array as well. If you use the first option from this argument, you will need to specify the result_array to get the result. However, if you use the result_range, you no longer need to specify the result_array as it is already a part of this argument. You can use either of these options.
• result_array – it is an array from which you want to return results from. This argument is not required when a range is specified as the second argument.

The biggest drawback with the LOOKUP function is, that it is unable to generate exact matches.

This function always returns an approximate match. If a seach_key is not present in the search_array|search_range, this function will find the closest match possible and then return the lookup value associated with that approximate match.

If duplicate values exist, this function always returns the result based on the last match found. All this is because it works on the sorted ranges (an ascending order sort will give the best results).

Example

= LOOKUP ( A71, 'Database 2'!A$2:B$11 )

You can see how this function returns the Orders Count in the example above.

• For the search_key, use the cell reference A71 as an argument.
• To make things simple, use a search_range which contains both the lookup values and the results values. In this example, it is the range A2:B11 from the Database 2 sheet. Make sure the data is sorted in ascending order based on column A (Emails).

Notes

• Note: This function doesn’t get the exact match values, it will search for approximate matches and return the result. For example, see that for “patt@cdk.com,” the Orders Count is returned as 35 as the Email has an approximate match with “emily@rbc.com.” These two ids have the letter “c” and “.com” in common.

Lookup Data Using the FILTER Function in Google Sheets

Another way of looking data up for a single key value is using the FILTER function.

The FILTER function first takes the entire data from where you want to extract the results. Then, it will filter results based on supplied conditions.

You can use this as a lookup for the Orders Count by filtering on a given email.

Syntax for the FILTER Function

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

• range – is a mandatory argument that specifies the range from which you want to filter the data.
• condition1 – is a mandatory argument and specifies a condition based on which the function should filter the data out.
• [condition2] – is an optional argument and specifies any subsequent conditions, if any.

Example

= FILTER ( $B$2:$B$11, $A$2:$A$11 = A18 )

• Use B2:B11 as the range argument. You should fix this range as it is not going to change.
• For the condition 1 argument, you want to search the value in cell A18 within the range A2:A11. Therefore, use A2:A11 = A18 as a first condition. You can fix the range as it is not going to change.

The formula will return the Orders Count as 35 for “emily@rbc.com”.

Notes

Interestingly, when you have duplicate values, the function will return all the results.

For example, if you want to lookup Orders Count for rajan@pzk.com, the formula will return two values 18, and 30, respectively. You just have to change the email ID in cell A18.

Lookup Data Using the QUERY Function in Google Sheets

The Google Sheets QUERY function is a function from Database Family like DGET and allows you to write SQL-Like code to transform your data.

The function can also be used to lookup data. You can use the QUERY function to filter your data on a single value similar to how the FILTER function works.

Unfortunately, you cannot use the QUERY function to lookup values across different sheets due to the limitations of the query structure.

This function can do so much more than filter data, so check out the complete guide to the QUERY function to learn all it can do!

Syntax for the QUERY Function

= QUERY ( data, query, [headers] )

• data – specifies the range of cells on which you want to apply the function.
• query – specifies the method or way to manipulate the data. The language used to develop clauses in this argument is Google Visualization API Query Language. The commands or clauses are similar to that of SQL queries, sometimes more straightforward than SQL.
• headers – is an optional argument and comes into the picture of showing how many title rows your data has.

Example

= QUERY ( $A$2:$B$11, "SELECT B WHERE A = '" &A24& "' LIMIT 1" )

Suppose you want to lookup the Orders Count for Email rozy@rbc.com using the QUERY function inside cell B24. The reference or critical value is stored in cell A24.

• For the data argument, you need the entire data from which you want to lookup values. In this case, it would be A2:B11 from the Database 2 sheet.
• You want to select the values from column B with condition from column A. To achieve this, use the SQL like query "SELECT B WHERE A = '" &A24& "' LIMIT 1". This will be the value for your query argument. Make sure the entire query starting from the SELECT clause is enclosed in double quotes. Here, you are asking the function to return values from column B based on a condition for column A that searches for the value from cell A24.

Notes

📝 Note: Similar to the FILTER function, if you lookup for a duplicate value, this function will return all the available matches.

Conclusions

Looking up data from another table is a frequent task. This article showed you seven different functions that can do this for you.

• The popular VLOOKUP function looks for data vertically and returns matches from the right hand side of the search range. It also returns the first match found in case there are duplicate values.
• The HLOOKUP function works similar to VLOOKUP. The only difference is the function looks for data horizontally rather than vertically.
• The INDEX and MATCH combination allows you to lookup data to the left or above. These two limitations coming from the VLOOKUP and HLOOKUP functions respectively can be eliminated if you use the INDEX and MATCH function.
• The DGET function is from the Database family and looks a value up for you based on a single lookup criterion value.
• The LOOKUP function helps to search values both vertically and horizontally. The drawback of this function is its inability to produce the exact matches.
• The FILTER and QUERY can filter the data for you based any conditions and this can be used in a similar fashion to a lookup.

Which function are you using to lookup data? Do you know any other methods I didn’t cover here? Let me know in the comment section!

Check Out These Essential Google Sheets Tutorials

• The Definitive Guide to the QUERY Function
• The Definitive Guide to Data Validation
• Everything You Need to Know About Filtering Data
• Everything You Need to Know About Sorting Data
• The Beginners Guide to Apps Scripts
• Everything You Need to Know About Smart Fill