Cara menggunakan google sheets importrange

How to Vlookup Importrange in Google Sheets [Formula Examples]

By

-

With the help of the function Vlookup, we can import matching values from one Spreadsheet to another in Google Sheets. Let me detail how to Vlookup Importrange in Google Sheets. If you know the use of Vlookup and Importrange functions, then the use of Vlookup+Importrange is just child’s play.

Many Google Spreadsheets users have doubt whether to use Vlookup+Importrange or Query+Importrange to conditionally import data between two Google Sheets files. The answer is you can use both. But there is some difference and I’ll definitely explain that below.

So this is an advanced tutorial that covers not only the use of Vlookup in Importrange but also how to use Query in Importrange in Google Sheets.

I know you are not new to Vlookup or Importrange and that’s why you are here to learn how to use Vlookup in Importrange in Google Doc Sheets. Still, if you think it’s time to brush up your Vlookup/Importrange skill, do check my Google Sheets Functions guide.

I am explaining the below Vlookup and Importrange tips in this post.

1. How to use Vlookup with Importrange Function in Google Sheets.

2. How to Use Multiple Conditions in Vloookup in Importrange Data in Google Sheets.

Then the below Query use with Importrange.

1. How to Use Query with Importrange for Importing Matching Values.

Here we go!

How to Vlookup Importrange in Google Sheets

For this example, I am using two Google Sheets files. Even though the file names are not important, please note that for your reference.

File 1: Sales.

File 2: Analysis.

I am going to import data from the Google Sheets file named as “Sales” to another Google Sheets file which is named as “Analysis”.

That means I will use the Vlookup+Importrange combination formulas in the file “Analysis”.

Sample Data in the “Sales” File:

Formula Examples to the Use of Vlookup with Importrange Function in Google Sheets

I am in the Sheet “Analysis”. Let me show you from within this Sheet how to search the Sheet “Sales” and return values.

Basic Example to How to Vlookup Importrange in Google Sheets

In the file “Analysis” I have the search key (first name) “Justin” in cell A2. What I want is his sales amount that available in column F in the file “Sales”.

Steps:

1. First, Open the file “Sales” and grab the URL from the address bar.

2. Go to the file “Analysis” and use the Vlookup formula as below.

=VLOOKUP(A2,IMPORTRANGE("URL","Sheet1!A1:F"),6,FALSE)

3. Replace the URL with the URL that you have just copied in step 1. This formula would return the value 272160.00.

This formula is in line with the below Vlookup Syntax.

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

Here the search key is “Justin” in cell A2 in the “Analysis” file. The lookup range is in another file, which is “Sales”. So replace the range with the IMPORTRANGE formula that imports the data.

The index here is the column number 6 (column F) from which the Vlookup returns the value.

Why the formula returns an #REF! error?

If you see this error, that means you have not authorized IMPORTRANGE to import the data. Check my IMPORTRANGE function tutorial to see how to solve that.

You May Like: Different Error Types in Google Sheets and How to Correct It.

Vlookup Importrange with Multiple Search Keys in the Same Column

Note: I recommend you to check this tutorial, Vlookup Array use, before proceeding further – How to Use Vlookup to Return An Array Result in Google Sheets.

Vlookup+Importrange supports almost all the Advanced Vlookup flavors (search the term Vlookup in the search bar on the top of this page). But the formula may seem complex due to replacing of Vlookup range with Imported range. Sadly Google Sheets Importrange doesn’t support shortened URL.

Here I am going to use the Vlookup in Importrange with multiple search values. In the “Analysis” file I have the following search keys in the range A2: A4.

Justin
Andrew
Jonathan

Here is the Vlookup Importrange combination formula in Google Sheets with multiple search keys in the same column.

=ArrayFormula(IFERROR(VLOOKUP(A2:A,IMPORTRANGE("URL","Sheet1!A1:F"),6,FALSE)))

What is the difference in the use here?

This Vlookup is an Array Formula. That means we expect an array result from Vlookup. Since Vlookup is not an Array Formula, make it an array formula by wrapping it with the function ArrayFormula. Further, since the search keys are in A2: A4, use A2: A (infinite range to include future search keys). Include the IFERROR to avoid #N/A error.

Multiple Conditions/Criteria in Different Columns in Vlookup+Importrange Formula

Compared to the above two formulas, I know, this one is quite confusing to many users.

So I highly recommend you first refer this guide – How to Use VLOOKUP with Multiple Criteria in Google Sheets. Here in this tutorial, there is only a slight variation.

In normal use, the Vlookup search down the first column in a range. In multiple criteria Vlookup, the formula search downs two columns (you can include more columns though). Again we want to do it in an imported range.

Let’s see How to Vlookup first column + another column in Vlookup Importrange.

This time I want to search down the search keys in Colum A and Column B in the imported range, that means the first name and last name.

Formula:

=ArrayFormula(VLOOKUP(A2:A&B2:B,{IMPORTRANGE("URL","Sheet1!A2:A")&IMPORTRANGE("URL","Sheet1!B2:B"),IMPORTRANGE("URL","Sheet1!C2:F")},5,FALSE))

Needless to say, you must replace all the three URLs in the formula with the “Sales” URL.

The logic in this formula is like this:

1. Join/combine or you can say concatenate the search keys using the ampersand sign.

2. Join the related columns in the Vlookup range using the ampersand sign.

3. Change the column number since two columns are combined in the range, the column index number moves down from 6 to 5.

Didn’t get? See this new combined range returned by multiple Importrange formula and ampersand.

Query as Vlookup + Importrange Alternative in Google Sheets

In all the above three examples, you can use Query as an alternative to Vlookup. The formula would be simple but won’t return an array result.

Query with Importrange for Importing Matching Values

Example:

The search key in cell A2 = “Justin”

The Query can match this value in the Importrange data and return value from column 6.

=Query(IMPORTRANGE("URL","Sheet1!A2:F"),"Select Col6 where Col1='"&A2&"'")

If you have multiple search keys in Column A, then you must drag this formula down. There is no array formula option here.

Here is yet another Vlookup Importrange formula example in Google Sheets. This time see how to search ‘down’ two columns using Query.

This is similar is to the multiple conditions in Vlookup Importrange formula.

=Query(IMPORTRANGE("URL","Sheet1!A2:F"),"Select Col6 where Col1='"&A2&"' and Col2='"&B2&"'")

This is yet another drag and drops formula.

Conclusion

The Query in Importrange in Google Sheets scores over the Vlookup Importrange only at one point. That is the simplicity of the Query to use and understand. Leaving that aside, Vlookup Importrange is far better as it can populate an array result.

I think the latter can improve the performance of your Sheet if you have a large number of search keys to search.

Bagaimana cara menyalin data antar sheet dari sheet1 ke sheet2?

Bagaimana cara berbagi Google Spreadsheet?

Importrange untuk apa?

Bagaimana cara mengakses Google sheet?