okay so in this video we'll be using vlookup function to look up a column on a right and
return values that are on the left so again this is not going to be a video covering basic vlookup
functionality if you don't know how B lookup works in general you should go ahead and watch
the first part but this will be about how to get vlookup to check a value in this column and find
it and then return something from column on the left so as you may know vlookup the way
it works you provide we look up a table array and inside of that table array it's going to
only search that value you're looking for only in the first column and then return something
from the right that's the way if the lookup works now in order for us to be able to search a column
on the right and then return something from the left we'll have to actually create a new
table in our array that's going to return this column on the left and this column somewhere on
the right to that column and the way we can do that is by using a function called array
formula so and it's going to be very easy to do this and it's amazing that it works so well
so if I use a rate formula function I'm just going to use it separately to show you what it
actually does what you can get out of it right so I'm going to first provide this first column
that I want right and then I'm going to hit comma and then I'm going to provide the second
column which would be the next one to the right I'm simply going to close my parentheses that's
my array formula hit enter and then wrong number of arguments Oh silly me I forgot
curly branches here or curly brackets whatever calling it there we are so now that formula right
here which is this is a first range on the right and this is the second range on the left that will
return a new range as you can see with this being the first column and this being the second column
now we can use that in our advantage to create a vlookup function so I'm going to start equals
vlookup so the search key is going to be this the part number are searching for comma now this
is our range or table array whatever we're calling it so that's going to be our array
formula and I'm going to open that curly bracket didn't forget it this time apparently highlight
the first column that I need this is where I'm searching I'm going to make sure I press f4 I
need to lock that lock that range comma and then I'm going to select the second range which is
going to be the second column in our new table I'm going to press f4 again to lock that close
curly brackets and close the parentheses for my array formula this array formula if you remember
that's the same array formula I head down there that returns that this table at the bottom and
then after that I'm going to hit comma now the next one is our index the column index so in
this new table that I've created the name of the product is the second column so two and finally
is this data sorted nope so 0 or false close the parentheses hit enter now again it's not
connected so I'm going to delete this one I was just showing you what's going on but there it is
now what we did we search through this we found it and we return the value now because I locked
my ranges should be able to just copy this down so this one eight five seven and apparently we
have a few copies here it should return only the first one because this is vlookup right after all
so there it is that's that now the same way we could have returned any other column so I can
replace this B column which is what I'm currently returning and decide that why don't we return the
coast now you have to make sure that the range matches so if this is from the third row to
the fifteenth then this needs to be the same so I'm going to go ahead and f4 hit enter and now
we're getting the coast now that's it hopefully you liked this video and I'll see you next time