Google Spreadsheet - VLookup Values From Another Workbook

Question

I have a huge year of data and I cannot add it in 1 spreadsheet because of the limitation on the number of cells per workbook/spreadsheet so I separated the huge data into multiple files by month (January, February, March etc).

In my master file/spreadsheet I needed to use a formula to lookup some values from the master spreadsheet and the problem is that the tables to look into are now in multiple monthly spreadsheets. What is the best formula to look up values in multiple spreadsheets?

After googling for a while I don't have much options, I don't even know what the formula is for using Vlookup to find values in a separate spreadsheet instead of another tab. I tried importrange and it seem to still use the limit even though it's in a different spreadsheet I get error when trying to use it because the data is too large.

Answer 1

So you can use IMPORTRANGE to get the columns needed for the vlookup.

=VLOOKUP(D42,IMPORTRANGE("https://docs.google.com/spreadsheets/d/[sheet_id]","Sheet1!D:ZZ"),1,0)

It's not clear if you added the permission needed to access the other workbook when you use IMPORTRANGE

like this

You can also use IMPORTRANGE in the data parameter QUERY(data, query, [headers])

QUERY is awesome when you know your way around SQL. Google Visualization API Query Language