so when you do look ups sooner or later you will probably end up with a situation when you're
trying to use a vlookup or an xlookup or index and match or whatever other lookup functions you
like to use and you may end up in the situation that instead of just one key that you need to
match you have multiple conditions so to show you an actual example we have this table with from and
to destination for some freight and if we go to the shipping coast table you'll see it gives us
what it costs for different from and two destinations so just to see one example here
if we look at this one it's going from new york to vegas so if i go here and search for new york
there it is we should be able to match these two conditions and then bring over some information
based on that so that's what we're going to solve in this video now i'm going to show you
how to solve this problem whether you're in excel or google sheets in both cases
so i'll start by using power query to solve this problem and that would be the easiest way to solve
this problem that being said power query is only available if you are on a windows version of excel
so for those of you who are on google sheets or you're on a mac version of excel you don't have
that available to you so to solve this lookup problem with multiple conditions
we're gonna use some other trickery and i'll show you a function that will work for those cases
too so to solve this multiple criteria lookup problem using power query we need to simply just
convert these to actual tables in excel so i would go to this first one and quickly convert this to
a table i'm gonna go insert on top here click table and it has headers so okay good enough
i'm gonna go on top here and rename this table from table one i will just call it shipments
you can call it anything you like then i'm gonna go to the shipping cost table
right here do the same thing insert make a table out of this press okay and i'm gonna
name this table on top left this will be shipping cost so that's all i need to get started here
so now we're gonna connect both of these tables to our power query tool and all we
have to do for that just go to data and click on one of these tables click on this from table
range option and when that does the connection
it's going to load this so we're simply going to go close on load 2 and close on load 2
and we're simply going to create a connection to that table so i'm going to press ok
and i'm going to repeat the same exact steps for my first table so i'll click on this go to
from table range right here and then again close and load two
and only create connection same thing so now i got these two connections to both of those two tables
so i'm gonna grab the first table this is the table where i want my results
so i'm gonna find it here that connection i'm gonna right click and i'm gonna do merge
and here when we do merge we're gonna show which table we're merging this to and that's
gonna be the shipping cost table and usually if you only had one key column you would just say
this and whatever that key column is like this but now we have two key columns so what you do
in those cases you first click on the first one and then you press ctrl and you click on
the second one now if you have a third one you could have clicked then third one two
to do like three conditions i don't have that so i'm gonna uncheck that one so first one second
one and then i'll go to my shipping codes table origin is my first column control destination
is my second column so one one two two so we simply just match this two we're gonna do left
outer join this option everything else is gonna remain the same i'm going to press ok
and all i got to do now is simply just expand this so i'm going to open this and whatever other
columns i want from that second table i would highlight here assuming i just want shipping cost
that would only be this so i'm going to press that press ok and believe it or not we're done
so i'm going to go close and load and do close on load 2 and then here this time i'm not going
to create a connection i'm going to load it to a table to a new worksheet right here press ok and
it's going to create that new worksheet for us see with the name merge1 and we got all of this here
now this is pretty much done except that sometimes you may not like the fact that when it does this
merge the sorting order is not going to match your original data order we have here so see
we got like new york then philadelphia next here it's probably not gonna be the same order
so if you want it to be the same order i'm gonna show you how to do that too so let
me actually delete this right click delete and then i'm going to delete this connection as well
right click delete that merge and i'm going to repeat some steps i did so
again i'm going to find my first table shipment right click and do merge on this
and then shipments is my first table shipping cost is my second table click control click one
two click control click one two done with that press ok now here at this point even
though i have them merged my table results here are still in the original order they used to be
so what i'm gonna do here i'm just gonna go here on top and i'm gonna click add column and add an
index column right here and it's gonna go from one i guess or zero it doesn't really matter i'll go
with this and we'd simply add this column that just goes one two three four five six seven just
like this at this point i should be able to expand this and again choose whatever column i want
like so and it looks like because of that index column we already have our data sorted in the
right order but if it was not at this point you could still go here and do sort ascending
by that index column to make sure it is and then we can just delete that index column
right click and remove and that should be our result in the order we like at this point we
would go under home do close and load close and load two and i'm gonna simply load it to a table
new worksheet press ok and here we go we have our results we have all the matching shipping costs
and the new york philadelphia chicago houston if i go to my shipment table you'll see everything
is in the same order as we had before so as you can see very fast and easy done using power query
now let's assume we don't have power query available so what if we use mac version of
excel or we're in google sheets how do we solve this problem now for that i'm gonna reopen a
fresh version of this file and this is what i have i have my shipments here shipping cost here
so to solve this problem for google sheets mac or website version of excel we're going to use filter
function and for that we would do filter and open parentheses and this is going to ask us for an
array so the array is going to be the results column so i'm going to go to my shipping cost
table right here and select this d column right here so this entire column d is my results so
i'm going to go ahead and select that comma then i need this include which is basically a logical
array of trues and falses and the way i'm going to get that i'm going to use n function i'm going
to do n open parentheses and then as a value for this n will simply select this column a for origin
and i want to match that origin to the origin i had in my current row so i'll make it equal right
here equals i'll go back to shipment and click on my from destination which in this case is new
york now when you do this in excel it adds this name of the tab in front of the cell reference
this wouldn't have happened in google sheets so i'm gonna get rid of that because we don't want it
here it should just be the cell reference so this is my first array so i'm gonna close parentheses
on this one i'm gonna add a multiplication sign and i'm gonna do another n function right here so
this will be the second criteria that i'm trying to match so for our second criteria to match this
we're going to go to that same shipping cost table we're going to select this destination column
b the second column and we'll say that should be equal to we'll go to this
shipment and simply click on two right here and yet again i'm gonna remove that part where it
adds the name of the tab in front of it let's actually pay attention to this formula here
in the formula bar so it's easier to read this so there it is i'm gonna close parentheses for
this so right now i have the second n function and the first and function both multiplied together to
logical test arrays now if you had more conditions you can simply repeat this as many times as you
like you can do another multiplication another n function and do the next logical test for me
i just had those two so i don't need to keep doing this i simply need to close my filter function at
this point from right here and if i hit enter i should be getting the match see and at this point
all i have to do is to just drag this formula down so if i just double click in this corner
i'm gonna get all of the results as you can see that match those criterias
so let's just check a couple of those see like new york vegas we already checked that one
let's check this philadelphia phoenix so if i go and check philadelphia
phoenix as you can see it's right here
1124.33 if i go back here there we are we found our match it works just fine
now let's also test this in google sheets to verify that the same formula works so i'm gonna
copy the same exact formula i did here in excel we'll go to our sheets environment which is the
same spreadsheet as you can see and i'm simply going to just paste that same exact formula
with no difference whatsoever i'm going to hit enter and we got our match as you can see and
if i double click on this i'm going to get all of my results just like that no problem
and that should get us our lookup with multiple criterias right here
and it works just fine now one thing i'm going to mention is that in order for this to work
you need to make sure there is only one match for each one of these so if i go to this new york
vegas there shouldn't be two possible matches here in this table in order for this to work now
the first example i did with power query would actually handle those cases too so if you have
multiple matches it will just duplicate the lines for those and give you both here it wouldn't do
that it would basically just give you errors for those lines now if you do have multiple lines
you have a solution for that as well to simply just put those in multiple columns here
so what that means you could just take this whole thing and wrap it in a function called transpose
like so
and obviously if you don't type your function names right this is what's going to happen so
i'm gonna go back and fix this so it should be transpose like so and you'll see i'm gonna get the
same results if i drag this formula down like this except that let me show you what's gonna happen if
i have more than one new york vegas combination so if i go here to find that new york vegas here
there we are i'm gonna add another line right here and add that new combination
and let's just do a different number here let's just up it by a thousand dollars so
now i have two matches for this which means if i go here see it will spit out that second match
and this one is new york vegas two in the next column over if you have three matches
it would be three columns so again same thing you could have done in excel the same transpose
function this should work in excel google sheets and excel online and that's how you do lookups
with multiple criterias thanks for watching please subscribe and i'll see you in the next video