Index & Match: The Left Join of Google Sheets
I’ve been called a Google Fanboy, but that is only because Google is particularly great. I especially love Google Sheets, a part of the Google Drive ecosystem. Now I know Excel is a”more powerful” spreadsheet programthan Google Sheets, but I find that itisa pain in the butt to use and ugly. So I usually prefer to use Google Sheets.
I’m constantly working with CSV files that contain export data from one system or another and I’ve come to love this quick and easy function pair in Google Sheets that allows me to emulate the functionality of a SQL left join.
The basic idea is the same, I have data in one place that I want to combine with specificdata from another place. When I stumbled upon this gem my actualuse case wasneeding to dynamicallypull postauthor data from one spreadsheet into another without having to combine the actual spreadsheets.
So here is how the index & match function pair works. Let’s start off in our primary sheet.
|2||12345||This is some post content||03/12/16||1|
|3||12346||Oh look, this is some more post content||03/13/16||2|
|4||12347||The contains the final bit of post content||03/14/16||2|
This sheet contains90% of the information Ineed and this function will help meget the last bit of data Ineed, the authors name.First I will need to identify the column ofdata that Iwant from myexternal sheet. We’ll call thissheetvalues-to-display.
Again, I want the authors name so the column I need to get data from iscolumn B.So far our functionlook’s like:
Now Ineed toidentifytheUUID or what data Iwill be using to tie these two records together. In thisexample this would be the Post_Author_IDcolumn from my primary sheetandtheAuthor_ID column from the values-to-match.So my match function within my index function will look like this:
I’ve now defined what data I want to display in my primary sheet (values-to-display!B:B) andwhere I want to get the data from, and thatI want to get it by matching the data saved in D2 in my primary sheetwith the data from thevalues-to-display sheetin column B.
If I placed this function in the right-most column of my primary sheet it would now look like this:
|2||12345||This is some post content||03/12/16||1||Bob Jones|
|3||12346||Oh look, this is some more post content||03/13/16||2||Mary Doe|
|4||12347||The contains the final bit of post content||03/14/16||2||Mary Doe|
So that’sit, that’sthe basics of using the index and match functions to get dynamic external sheet data and emulate a SQL left join in Google Sheets.