in this video we're going to create a formula to join two tables in google sheets
using a common key so in this particular example you can see i got this owners table
where basically we have a list of names and then we have vin numbers for these vehicles
and then if i go to this car info we have vin numbers for some of those vehicles and then some
other information and we want to use that common key here and here to basically join these tables
so for that i'm going to start by creating a new sheet down here i'm going to call it joined tables
and we're going to get started with our formula so i'm going to build this formula piece by piece and
try to explain you how it works but in the end of the day we're going to get just one formula
to get us all the results we need so the first thing i'm going to do i'm going to start with an
equal sign and i'll do an array curly bracket here like so and i'll go to my first main table owners
this one and i'm going to go ahead and select the range of data here now you could select
a particular range of data like this in my case i want to just send this all the way down and keep
updating with new rows so therefore i'm going to remove this row 13 out of here and close my array
brackets and if i hit enter this should simply just copy that over here that's basically my data
from owners tab does not include headers now i'm going to leave this like that then i'm going to
one more time go grab this key column from my original data and bring it over here and
you'll see why i need this later so for now i'll just do this array bracket again go to
owners simply just select only this data remove the end row again go all the way down close the
bracket hit enter and that should simply just make another copy of our key column from our first
sheet which is basically our main table to which we'll be joining this car info so now
using this key we want to be able to go and grab some of this information from this car info tab
and for that i'm going to go here and i'll use a vlookup function so for my
search key i'm going to use this data from this column i'm going to drop the end reference to
send this all the way down comma then for a range i'm gonna go to that car info
in this i'm gonna create another array that will be this curly bracket and in that array i'm gonna
add two components the first part is gonna be the key so in this case that's going to be vin numbers
so it is the first column in this data i'm going to go ahead and select it if it was column number
4 you could have selected that column so it should be that key column i'm going to drop
the end reference like so and do a comma and comma is gonna add a column to this array now
if you're in a different time zone it's possible that you may have to do this instead of comma here
but for me i'm gonna do a comma to add a column and what i'll do i'll just highlight the whole
data including that column in there i'm gonna drop the end reference like so so basically
i'm creating this range of first column being that key and then the whole table i'm going to close
that curly bracket comma and then here we need after this comma we're going to need column
numbers which is going to be this indexes and for indexes i'm going to again do a curly bracket now
i'm running out of space here so i'm going to move to this formula bar over here and continue
so i need to basically list all the columns i'm gonna want and column index in vlookups goes
with numbers like one two three four five like that however we need to keep in mind that
we just did this extra column in the beginning that's going to be this key column all over again
so 1 is really going to be this virtual column in front of our data so our data
here starts from column number two so therefore instead of being like one two three
four five it's really going to be two three four five six remove that comma here so it's
just one offset because of this extra column in front so i got this so that's gonna be my two
then three then four then five and six we will replace this with something else in a second
but for now that's all i'm going to do comma and then we're going to continue with the type of
match which is going to be false which is not sorted or not a range lookup however we want to
call this i'm going to close parentheses and finally since i'm going to have all this array
situation happening in this i'm going to do ctrl shift enter or command shift enter to add a ray
formula bracket around this whole thing so at this point if i hit enter so you can see what happens
it will recalculate and you can see how we bring over all of our matches here i'm going to zoom out
for a second here so you can see so we bring over our matches from that second table over
here and if i scroll down see in cases that there is no match we have nas so there was no
match for this key in our second table so that's that now i'm going to add a row on top of this
so i can explain to something here so that formula was over here and in this formula we did this
two three four five six so we want to somehow dynamically build that without actually typing
that two three four five six so that if we have let's say 25 columns we don't have to type that
whole thing so what we can do we can use sequence function so i'll do it separately here to explain
you how this is gonna work and then for sequence function we're gonna give rows to be just one row
and for columns we're going to say 5 and for now if i just go ahead and hit enter see it
gives me 1 2 3 4 5. now if you remember here what i really had was two three four five six
so for that reason i'm going to go back to this and here after i do comma there's also the start
so i'm going to start from 2 instead of starting from 1 which is our default and if i hit enter now
it's two three four five six like that now finally i want this to generate as many as necessary for
the list of columns we have so right now i just hard coded to be five but if we had more columns
that would be the number of columns you have in your data which we can get by taking our data here
and this is our data in that car info tab so i'll just copy that range here hit escape we
can also select it from the other tab and here instead of five i'm gonna do
columns open parentheses and in parenthesis i'll paste that same range that refers to my data
in here in this data and what that's going to do it's going to calculate how many columns i have
in this data so if i select this much it's going to calculate one two three four five if i select
more or less it will calculate the number of columns therefore this will generate the correct
amount of columns so now at this point i should be able to grab this sequence i'm going to copy
delete that from here go back to my vlookup formula and instead of doing this
i'm going to remove those and paste that sequence right here and hit enter and again we should get
exactly the same results as we did before now next here i'm linking to this e column now that
e column if you remember is that copy that i did of our key column from our owners tab so i'll just
copy that same range from here without including array brackets you could include them too if you
wanted to but it's not necessary so i'll go back to this formula and instead of this i'll remove
that and paste that particular formula there which is going to be the same thing basically
so it took a second for it to update but we're going to get the same results
now i'm going to go ahead and delete this because we don't need it anymore
and now i have one formula here that basically returns all the matches from
second table and i have another formula here that gives me all the columns for my first master table
now before i combine these two together i want to address one more thing so if i scroll down
see over here we get all these n a's so i'm going to scroll up and we're going to take care of that
so what i'm going to do here i'll go back and i'll just add a quick if statement to say
if this owner's key column is blank just leave it blank so i'll just copy this before that vlookup
go back to the beginning and i'll say if open parenthesis paste that range if that equals double
quotes meaning blank comma then i want it to be blank so i'll just keep it like that comma if it's
not blank we want to do this match this vlookup so i'll do this go to the end here close parentheses
hit enter and that should return essentially the same thing except that now if i scroll down
you'll see we don't get those extra nas we will still get an ace here in the middle
when there is no match which you could handle with if error or something like that if you
don't want to have that it's really up to you i'm going to leave them in but if you don't like
those you could go here and put this whole thing from here through here inside of this function
if error and that would remove those and just leave them blank so you could do this and close
parentheses in my case i'm not gonna do this change so i want my nas to be nas
i'll just leave it as is i'll hit escape so now i got this second formula that gives me this second
table i got my first formula that gives me the first table now i'm going to combine them together
so what i'll do i'll grab this without that equal sign cut that out of here remove that equal sign
two so there is space for this to expand go back to this first formula and after i got this first
range i'm gonna go after that and do a comma and again depending on which
settings you have in your spreadsheet this may be this instead you can watch my syntax video
if you want to learn more about which one you should use but in this case for me it's comma and
then i'm going to paste that whole other formula right after that so with this if i hit enter
we should now have one formula in this corner that returns this entire thing so everything from the
first table and all the matches from the second table so at this point you may also want to add
headers on top of these because this doesn't include headers from here or here so if you
wanted to add those headers let's first of all just delete this row one we don't need it anymore
i just wanted to do that so i can explain you that formula with sequence how it works but now i don't
need this anymore so i'll go back and what i want to do here i want to add to the beginning of this
those headers and the way i'll do that because if i go here and start clicking it will probably just
not work so what i'll do i'll just grab everything after this curly bracket and
cut it for now so i can paste it back in just a second after that curly bracket i'm gonna move
to my first master owners table and just select headers from here then i'm gonna go here and do
a comma and after comma i'll go to my second table and select all the headers from this table like so
and then now i need to add the rest of the data below headers
so for that i'm gonna do a semicolon and i'm gonna paste whatever else i had right after this
ctrl v command v for paste or right click paste whatever you prefer at this point if i hit enter
it will recalculate and as you can see i got all the column names from first table and from second
table all joined together so now you could even go further and maybe choose certain columns from this
instead of having all of it you could do that by using query function so we can go after equal sign
and use our query function and use this whole thing that we just did as our data array and after
this i'm going to do a comma and as a statement we'll use a select statement and in this select
statement basically we want to refer to these columns by their numbers so for example let's say
i want to have this id as the first column that would be column number four in this range of data
so col4 uppercase c is important here and then i can comma separate all the other columns i want so
let's say after that i want first and last names which would be 1 and 2. this has to be upper case
and then maybe i also want to add this and this so that would be one two three four five six seven so
six and seven so comma after this after we select all the columns we need and now at this point
because i have headers on top i did include them here we can also do one header row is the first
one close parenthesis and hit enter and that should now give me those particular columns
that i choose from that joint statement and now i could even filter this further maybe
for example maybe we want to just show bmw cars so i can go back to my statement and add
a where close and this is this column over here so that would be column six
and i'll make it equal in single quotes because it's text to bmw we
should be able to filter that to as you can see bmw after joining those together
that should do it for this video thanks for watching please subscribe
and i'll see in the next one