Index & Match: The Left Join of Google Sheets

Published on April 4, 2016

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.

1	Post_ID	Post_Content	Post_Date	Post_Author_ID
	A	B	C	D
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.

1	Author_ID	Author_Name	Age
	A	B	C
2	1	Bob Jones	34
3	2	Mary Doe	28
4	3	Tobias Yunkor	41

Again, I want the authors name so the column I need to get data from iscolumn B.So far our functionlook’s like:

=INDEX(values-to-display!B:B)

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:

=INDEX(values-to-display!B:B,match(D2,values-to-display!A:A,0))

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:

1	Post_ID	Post_Content	Post_Date	Post_Author_ID	Author_Name
	A	B	C	D	E
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.

Meet the Author

Ethan Thompson is a Divisional Marketing Manager for a global safety company. He has worked in the digital marketing field for 13 years and loves the challenges the ever-changing field brings. When he isn't exploring new digital marketing tactics at his desk, he's out exploring Western New York. During the warmer months, he can often be seen riding his bike around the Empire State and sampling the local beer selection.