After almost melting my brain I finally got this working 🙂
In this tutorial we’ll have a look at the process to follow when creating a multi-workbook vlookup formula in Google Sheets (and hopefully keeping ourselves “woooosssaaahh” in the process).
Multi-workbook vlookup’s in Excel are fairly simple because Excel does all the heavy lifting in connecting the various workbooks on our behalf (Google Sheets not so much).
The key difference in referencing different workbooks in Excel and Google Sheets is that Excel is a Desktop application and Google Sheets is a Web application. This means that Excel runs on your local machine and Google Sheets runs within your internet browser. Why this is important to understand is that Google Sheets utilizes a workbook’s “url” to access information within the workbook.
A “url” is the unique ID for every workbook that you create. Just like Excel documents (in the same folder) cannot have the same name, Google Sheet’s Workbooks cannot have the same “url”/unique ID.
The url of a Google Workbook will look something like this:
Your workbook’s “url” can be found here:
For the example in this tutorial we will use 3 separate workbooks:
- Master_Workbook : Used to consolidate the information from the other two workbooks;
- Workbook_1_Performance : Holds the performance scores we want to import in the Master_Workbook (Workbook1_Sheet1); and
- Workbook_2_Potential : Holds the potential scores we want to import in the Master_Workbook (Workbook2_Sheet1).
(Please refer back to the image above if you feel slightly lost in the explanation below.)
Due to the use of url’s (as opposed to file names), the way we use vlookup’s in Google Sheets is slightly different to Excel.
We need to use a special formula inside of our vlookup formula. This formula is called “=importrange“.
The importrange formula takes two input variables / parameters. It takes a url and the range of the data you wish to import / vlookup.
Take note of the double quotation marks around the url and data range. If you do not include the ” ” marks the formula will not work.
In order to vlookup the performance scores from Workbook1_Sheet1 into Master_Workbook, we have to add the following formula in Master_Workbook, cell B2:
Taking the formula apart:
- =vlookup - we start off with the all too familiar vlookup formula;
- =vlookup(A:A, - we reference the whole column A in our Master_Workbook as our vlookup reference column, i.o.w. the common values in all of our workbooks;
- =vlookup(A:A,IMPORTRANGE( - here we introduce the importrange formula. The importrange formula will form the second part of our vlookup formula. In excel this portion would be the name and range of another Excel workbook;
- =vlookup(A:A,IMPORTRANGE("https://docs.google.com/spreadsheets/d/1w2g7DbhzsrO5tiMeI1V6Y/edit#gid=0" - the firs portion of our importrange formula contains the unique ID / url of the workbook that holds the data we need - in this case Workbook1_Sheet1;
- =vlookup(A:A,IMPORTRANGE("https://docs.google.com/spreadsheets/d/1w2g7DbhzsrO5tiMeI1V6Y/edit#gid=0","A1:B17") - the second portion of the importrange formula includes the range / cell that contain the data we want to extract - in this case Workbook1_Sheet1 cells A1 to B17.
- =vlookup(A:A,IMPORTRANGE("https://docs.google.com/spreadsheets/d/1w2g7DbhzsrO5tiMeI1V6Y/edit#gid=0","A1:B17"),2,false) - The last portion is the standard column index and exact match parameters used in vlookup formulas. The 2 refers to the second column in Workbook1_Sheet1 - It is the column that contains the performance scores we wish to import.
And that's that 🙂
For the potential scores we will follow the same formula above, but our url will be different as we'll be referencing Workbook2_Sheet1 with it's unique ID (url)
If you experience any difficulties with this tutorial or just want to reach out, please feel free to leave a comment in the comment section below.
Until next time...