in this video we're going to go over two-dimensional lookups in Excel and
Google Sheets and basically we'll be using this example where we have this table and we
need to find a match for a combination of two things like Isabella Midwest so right now you
can see for that combination for Midwest Isabella it's really a blank if we change it to Jake here
for that combination Jake Midwest we should be looking at this 6829 amount so I'm going
to give you a couple of examples of solving this problem we'll use index match we'll use vlookup
so let's start with a vlookup so if I do a vlookup here point to this value as a lookup value comma
which is basically names right here and then as a table array I'm going to go ahead and select
this table and again that should be the table because rap has to be located in the
First Column which it is right now comma then depending on which column you want you would
do your column index number so for example for Midwest this would be column number two
comma and then this would be not a range lookup so false as a range lookup so it's not a range lookup
it's exact match we're going to do this hit enter and that should give us that amount except that if
I change this Midwest to a different region like West that's not gonna update because at the moment
we have this hard-coded column number two for our region column so to replace that hard-coded
number two we're going to use another function so I'm going to use x match you could also use match
if you don't have the most recent version of excel so as a lookup value for x match I'm gonna point
to this region comma and then we need a lookup array basically this will be the row or the
column in which we're going to find this Midwest now if I just select this which is this list of
regions close parenthesis and hit enter you'll see I get a 1. what this does it finds this Midwest in
this list and it says it was in position number one so that's what this one refers to now if I
change this to say South see this becomes three because now South is in position three now the
problem with that is that if I go back to Midwest this gives me position one but in my formula
here we have column number two as Midwest so what I'm going to do here I'm gonna go back and instead
of selecting just this list like this I'm going to select this to match the same table I had in
my vlookup so when I did a vlookup I selected my table starting from here through here so that's
the same thing I'm going to do on this and with this if I enter we'll get a 2 and at this point
I'm gonna grab this formula copy this without equal sign go to this amount and replace the two
with that x match function and then I can get rid of this let me just zoom out so you can see what
this formula looks like so this is my formula basically it's just a vlookup except that our
column number is x match now if you don't have x match you can use a match function like this here
then you would have to do comma zero to do that exact match type now with x match it's default
exact match so we don't have to do that comma zero and that's why I like it so
you don't accidentally forget the comma Zero part that being said if I do this
I hit enter see I get this amount so at this point with this formula this should work very
well when I try to for example switch this to like Northeast see I got this
amount for Jake now for that region like so so if we for example change this to Nia
then this number updates to match this cross combination of that 8449.61 and again the same
thing should work in Google Sheets exactly the same way it's going to be exactly the same formula
so if I copy this go back to Google Sheets and paste this right here I'm gonna hit enter so for
Midwest Isabella we got really nothing here see Midwest Isabella let's switch this to Jake and
there we go we got our Jake Midwest combination and that's the amount so this works just fine so
that's our vlookup x match now if you're gonna have a long list of items here and you want to
drag this formula down then you will have to make sure that this range is absolute so F4 here and F4
here like so and then if you have more rows you should be able to drag this formula down but for
me it's just a single line so it doesn't matter so that's our vlookup let's just put this aside
over here maybe here and let's do the same thing now using index and match and again I'm going to
use x match if you want to use match instead you will just do the same thing just do comma
zero here right I'm just going to use x match so let's see how that works so first of all let's
just look in this index function and see how this works so index function is basically an array in
which you can basically give two positions the row number and the column number so to give you
an example if I go ahead and select for example this entire thing like this comma then as a row
number if I do something like 5 comma and as a column number do something like two I hit enter
see I get this 8206 this amount and the reason for that is because in this highlighted area if I just
move one two three four five row five and then one two as the second column I end up in the cell now
remember that these are not row numbers in your spreadsheet right now they are row numbers because
I selected this entire thing starting from this first cell on the left however ever if this range
was selected like this then in this selection it would have been four one so see in this it's one
two three four to get to this cell and it's the First Column so that's how I would get to that
number I'm gonna undo this and get back to just selecting this entire range just know that it's
not really the row number in a spreadsheet it's the position in the array so now the problem with
this is that we have hard-coded five and two and we need to replace them with something that will
work dynamically because right now you know me changing any of this doesn't affect this formula
so the first thing we're going to replace is this five and the five is the position of
this person Jake and that's basically in this list that's one two three four
five if we start counting from this position so for that I'm going to use x match again
I'm going to point to Jake as the lookup value comma and then we're going to do lookup range
which is going to be this entire column A I'm going to close parentheses so again it's
the whole column because when I was selecting the range for my index function before let me just hit
enter here over here see I selected this entire range starting from this Row one so therefore my
column over here can start also from Row 1 and keep going like this so now it's going to give
me that position 5 for Jake and if we change this to say grace C changes to position three so three
over here right so I'm gonna go back to Jake here so now we can take this copy this formula without
equal sign go back to this formula and replace our hard-coded five with that x match function like so
so that's fine again for rap but that doesn't do anything with this one which is the column number
for region which is essentially going to be the same thing I did with vlookup when I was doing
this so I'm gonna do another x match this time I'm going to be looking for this region comma and I'm
going to be looking for that in this row again I just selected the whole row you could have also
selected like this much I'm just gonna go for the whole row because this way if I keep adding new
things to this table later I don't have to come back and update this so I'm gonna go parenthesis
close hit enter and that's going to give me column number two so that's excellent I'm gonna copy this
go back to this formula and replace this number two with my second x match which basically finds
the position so I can get rid of this and now we got this formula that finds our match now let's
first of all test this and make sure this works so if I go ahead and now change this for Jake to be
another region like this see it updates the number that's good now let's also try to update
the wrap so I'm going to do this see for this combination
that's 8449 but if I change this to say West then it's this because again our x match will
find the right column and update and this x match will find the right row and update for us
also if you're planning to add more data to this table you could also make sure you select
more columns and rows here so for example instead of going until line 10 here from one like this
I could have selected in this case like this this entire column so this way if I add more
sales reps here and add their numbers this will automatically just work out you could have also
gone through more columns like this in case you can add more regions it's up to you I'm just going
to leave it like this that's good enough for me hit enter and that works so this is our index and
match version and this is our vlookup and match version and finally if you want to be able to
drag this down because you have more rows where you're going to choose different region and wrap
combinations you then would have to make sure you take this range and do absolute so F4 take this
range and make it absolute so F4 and finally take this range and make it absolute as well
like so so all the ranges I made them Absolute pressing F4 key and then these two I'm gonna
leave them relative so as you drag if you have more rows with different wraps and regions it
should automatically pull them so just to give you an example of that if I for example
do something like that and we do something like Grace North East here and then Olivia
West if I drag this formula that should automatically get those for all these lines
and just to make sure this works in Excel we're going to copy this go back to excel and take this
vlookup and replace it with our index and match formula hit enter and there we go we got our
amount that will match this combination no problem whatsoever as you can see it works in
Excel just fine as well and just in case your Excel version doesn't have x match basically
just use match so remove this axis go here add a comma zero on this one and a comma zero on this
and that should get you the exact same result if you like my content make sure to like
and comment subscribe to the channel thanks for watching and I'll see in the next video