in this video i'm going to demonstrate how to get x lookup to return multiple columns
right now if i start my regular xlookup function right here i'm going to point to my lookup value
stock numbers which is basically common key column between these two tables
comma then as a lookup array we're going to select that column of stock numbers
i'm going to press f4 to make it absolute reference comma and finally you usually do
return array which is the column of results so if you just wanted to get prices for example
you would have selected this f4 and at this point if you just close parentheses and hit enter that
should get you price for this now if you wanted to get price cost brand multiple columns something
like that one option you have here is when you get the return array you can simply just select
multiple columns like this so if i select all of these four columns as a return array i'm going to
do f4 if i hit enter see i'll get the results from all those four columns now if you didn't want all
four columns maybe you wanted just three you could for example do something like this then i'm gonna
get these two columns only so again if i do f4 to make it absolute reference i hit enter you'll see
i got brand and price then we can grab this and drag it down and that should get us the results
now for this one we get n a because this stock number is not available in this list so this would
be if you need a bunch of columns that are right next to each other but what if you wanted to maybe
instead of just getting this two columns you wanted to get maybe brand and then costs or maybe
size and then you want to skip price so what do you do here you can use choose function and for
choose function as an index you're going to do an array of as many things as you're going to
need returns so in this particular case let's say i'm going to do price as the first column
and size as the second column so what i would do i would open square brackets and i'll do one
and two because i'm going to need two results comma then after comma as value 1 i'm going to
choose the first column i want the results from so let's say i want prices to be listed first in my
results so i'll select this f4 comma and then i'm going to choose my second column which is let's
say it's going to be size so i'm going to select this range of sizes f4 now i do need to close
parentheses on this to close that choose function so at this point if i hit enter i'm going to get
price and then size now if you wanted another column here you can basically just continue doing
so so i can just go here and do comma three and then after this third range i'm gonna do a comma
and basically just select whatever column it is i want my results from let's say the third column
is going to be cost so i'll select this f4 hit enter now we're going to get those three columns
in that particular order i can drag it down and as you can see we'll get our results like you would
expect finally the last thing i want to show you is how to deal with no matches like this n a how
would you deal with this so what you could do if you just need empty blanks in case there's
no return you could do a comma after this and then the next argument in our xlookup is if not found
so here i can just do double quotes to basically return a blank and in this case if i drag it down
i'm going to get a blank now what if you wanted some custom values here
for example let's say for price i want to return 0 if it's not found
for size i want to return size 12 when it's not found and then for cost i'm gonna return zero
if it's not found so to do something like this you would go back here and when you get to this
if not found argument instead of just doing one thing i can just do another set of curly brackets
here for an array and then i can comma separate those results so the first one i want it to be 0
comma the second one i want it to be 12 and the third one i want to be zero again so i'll do this
hit enter and again it's just going to work when it works but when it doesn't
find a match that's what we get 0 12 0. now in case one of your columns is text it's not
numbers then well let's actually just grab the second one for now and replace it with this brand
f4 so at this time if i drag it down it's just going to return brands and then 12 when there's
no match but let's say i want it to be other so what we do we go back and instead of this 12
we just type other in quotes so basically the only difference here is that text would
go in quotes numbers would be just numbers so this point if i drag it down it's going to be
see when it's not found zero other zero so that's how you get multiple columns returned
now obviously if your data is not right next to your other table then you could do this from a
different tab so just to do that really quickly here so you have an example of that i'm going to
delete this erase this and let's try to get those values now from this other tab located here so
it's going to be the same thing essentially so what we'll do here we'll do x lookup
our lookup value is going to be this comma our lookup array i'll go to other tab and select
the column i'm targeting so that's going to be stock numbers i'm just going to select the
whole column because i have a nice clean table so that's usually easier to deal with f4 comma then i
need return array again because i need multiple columns if they're next to each other you could
just select like so if they're not then you go with that choose function and we basically list
our columns in curly brackets however many you're gonna have so if you're gonna have three columns
returned you do one comma two comma three if you have four you do comma four i'm going to close
curly bracket comma and then i'm gonna keep selecting all my other columns
so i'll do something like price f4 comma i'll do brand f4 comma and then cost f4
those would be my three columns i'm going to close that choose function so at this point if you just
want the regular handling for errors you can close parentheses again and just drag this down
and that should just work fine if you wanted some sort of custom handling again you would go back to
this formula do a comma and pass that handling however you want this to be so in brackets i'll
do again for example 0 comma other encodes comma 0 like so hit enter and drag this down
and that should do it thanks for watching please subscribe and i'll see you in the next video