Hi there - want to learn how to compare two lots of data super fast using the VLOOKUP
function in Excel? stick around and I'll show you the best
way to do it. perfect for reconciling accounts.
Hey I'm Sharyn from Excel at Work where we offer straightforward, non technical
assistance to help you become a confident, super productive user of
Microsoft Office. In this video I'm going to run through comparing two lists of
data to find missing information and data that has been entered incorrectly
and I'll cover the things that I constantly find people doing wrong when
it comes to creating VLOOKUPS. If you'd like a copy of the files I'm using to
follow along I've put a link below and if you personally have any super helpful
VLOOKUP tips that may be really helpful to others go ahead and share them, we'd
love to hear from you. Hi there - so here's a scenario that I'll
talk you through and this is something that I work with people a lot in doing
comparisons between information that we have in our system compared to maybe a
supplier's information or a customer's information especially when it comes to
payments and in this instance what we have here, is obviously the information is
make-believe, but what we have here is a list of information, our list that's come
out of or been generated out of our system, and then we've got the customer's
information here and that may have been copied and pasted out of information
that they've emailed to us or sent to us in some way, and what we're trying to do
is we were wanting to do a comparison on what they believe they've paid, there's the
invoice number there, the amount and the date and of course we have our
information here, there's the invoice number the amount and the date. We also
have some purchase order numbers here as well and of course that
can be something that we could look up or use as a lookup value however I just
want to point this out to you. When you're creating a lookup it's always
important that the lookup value that you're using, the match for match, what
I'm talking about here is we've got an invoice number here and of course the
customer's got an invoice number over here. Now they need to be unique they
can't be replicated down the column. In this instance you might
see that we have actually got a purchase order number
over here for invoices 2457 & 2458 and they're the same purchase order
number and it can happen on one purchase order number there can be multiple
things dispatched out and an invoice number obviously created with each
dispatch so in this instance that's not a good set up for us to use the P.O
number as the Vlookup however with the invoice that's okay and if you're ever
thinking how could I actually check that I don't know if you've even been taught
this I'm just clicking to the top of the list here I'm using Shift+Control+down
arrow to select to the bottom of the list now on the Home tab we have
something called Conditional Formatting, Highlight cell rules, Duplicate values
and if it comes up and shows you've got duplicate values, it's not a good column
to use to do your Vlookup so I'm just gonna cancel out of there and of course
if we just do a check now over on the invoice number and we're gonna have a
look on there and look at that nothing so that's all go so we can actually use
that no worries now if there were duplicates on their Excel would stop on
the first instance that it found and it may bring back the incorrect information.
So always deal with duplicates if you have to to get your vlookup working
properly. Right let's come back over to here and I'll just talk about the next
thing we have this invoice number here that we're going to be using as our
Vlookup so essentially we're going to say the Excel hold that in memory and I
want you to go and find that information over in this table here now here's a key
thing about building Vlookups. When you're actually working with Vlookups,
when you're looking down a column to find a match once you find a match Excel
can only look to the right of that column so it's okay in this instance
because we've got the purchase order number over here on this side we've got
the amount and the date over here but if we needed to bring back information
about the purchase order number we'd need to make sure that that invoice number
there that column was on the left side of this column otherwise you will
not be able to use your Vlookup to look up say the purchase order number and
bring that information through. Now that's ever the case if you're in a
situation where it's in an incorrect place, all you'd need to do is Cut it and
move it. So let's do that now. So I'm just going to move this so that
it's on the other side of the purcahse order number. So it's really easy all I
just need to do here is just do a cut and then bring it over to here and then
just go and see it cut cells and now I've placed that invoice number in a
really good place because I could actually do if they look up and bring
back the purchase order number the amount or the dates because they are all
placed on the right hand side of their invoice number okay let's come back to
over to our list and let's start building our very look at so if you've
ever been on training with me you'll know that I'm really keen on using this
little effects button up here when you're unseating functions so we're just
going to use that but if you have been taught otherwise and you go to the
formulas and you use it from the air or maybe another way that's absolutely fine
just as long as you end up and the same place as me now if you can't see vlookup
down here in the most recently used category of course you can just come up
here you can remove the brief description and just type in vlookup and
go and it'll find it for you and then all you have to do is go okay and you
will open up the function arguments box so let me just guide you through here
now the lookup value is the piece of data that you're using that is the match
in this instance it's going to be the invoice number remember that we're going
to be using that to look over here on the list from customer information so
coming down to the table array and you can actually see already excels holding
that in memory coming down to the table array is going to click into this box
here this is when we actually come over here and we say to excel this is where
you will find the lookup value now there's several ways that you can do
this if you had a lot of different information in a different table under
here you would have to actually Hakan in only highlight the area that you're
looking at so you would have to actually hook that in and if you did that you
would then need to obviously anchor it so that if you're copying it down you
don't have your formulas see let's come back over here you don't have your
formulas and creeping so if you copy that down without the dollar signs that
will go to a six and and D 27 and it'll actually creep and not do what you want
it to do if it's the
case you you will have to lock it in but look there is actually the ability to be
able to do this if you don't have a whole lot of data underneath here you
can also just do this straight across the top and you can see excels held in
memory list from customers tab the exclamation mark is telling you that
it's actually the tab name that it's coming from the sheet tab down here and
A to D now that means that if more informations put at the bottom of here
later on it will be included in the vlookup as well which is pretty cool
okay the next thing I want to talk about is just be very careful of this there's
so many things I see people doing wrong at this point the first thing that I'd
say that they do wrong is they click down here before they've actually worked
out what the column index number is I'll just explain what the current column
index number is at this point Excel is looking down the first column of the
area that you've highlighted now it's looking down the first column because
it's doing what's called a vertical lookup that's what vlookup stands for
vertical so it'll look for a match down the first column now once it finds a
match then you are actually asking it or telling it which column you want to
bring the information back from now if we are bringing it back from the amount
column this is column 1 this is column 2 this is column 3 and this is column 4 so
we're actually saying bring the information back from column 3 that's
what you're wanting so watch this if I actually haven't counted across 1 2 3
from the left we're counting across and I come down and click on to here you see
what happens it actually takes you straight back to the original sheet so
if you haven't actually counted prior to doing that what you need to do is click
back up into the table array box and do your counting please don't do this okay
so many people do this they come and click down here and then they put the
name of the sheet tab back in here and before they actually put the information
that is required in the box if you accidentally do that get rid of it
okay it's not meant to be in there and again I'll just say this is 1 2 3 it's
not ABC that's another thing that people get very very wrong 1 2 3 1 we want the
information back from the amount box so I'm just going to go and pop that and
now and now the last thing that you need to
do is tell Excel whether you want an exact match and what you're saying is I
need to have you go and find information for two two three four so if that's the
case surely you're actually wanting to have an exact match not a near enough
match or a closest match you want an exact match now there's two different
things that you can do if you want a near enough match you can type the word
true which means that if it couldn't find two two three four it might find
two two three three now it's not often that you want that there are definitely
situations that you can use that to your advantage but most of the time and
certainly when you're doing reconciliation like this you want an
exact match so you will type in here either the word false saying now I need
you to find two two three four that's what I'm looking for
or you can also use a zero so if you use zero all the word false that's actually
saying to Excel find me and exact the match so look at this as you've started
to populate the information up here Excel is also populating the information
across here so it's equals vlookup open bracket hold in memory c5 at two two
three four go across to the list from customers information look from A to D
across the information go to the third column once you've actually found the
information that two two three four and the first column go to the third column
across and you want an exact match clicking on OK and all really we've got
something that's called an in a happening now when you have an in a it
means not available and I'm just going to copy this down so that you can
actually see that you'll feel lookup is actually working it's going to do a
double click on the little button here so some of them use that has definitely
worked on and unfortunately we have a few missing but that's not a bad thing
because what happens is you can anything that's not available you know that the
customer isn't actually either paid you or they haven't got a copy of their
invoice and if you've got lots and lots of information or lots of na s of course
what you can do at this point which is really cool is that you can easily just
I just did a shift control down arrow to select everything you can easily go into
your filter on and of course you can filter out so that you're actually only
looking at the enas and then you can do a copy and paste and
maybe send that off to your client and just say well your customers they look
these are their the things that we're missing or you're missing really quickly
so if you have got hundreds of records you can easily go and find that
information I'm just going to clear that off in fact I'll tune filter off so
there's an option for you to go and find them but very very quickly we can see we
are this information isn't available back over on the other sheet so two two
three four two four six six and two five nine zero now I've always found it
really helpful to do it back the other way as well so let me just show you this
now before we do that I'll just show you we can actually can obviously come over
here and do a vlookup using their information back the other way but of
course if I want to do a vlookup on the amount see that that invoice numbers in
the wrong place so again I just have to do I'm just going to shift control down
arrow again I'm just doing a cat and I'll come over here and I'll place it I
could actually place at the beginning if I needed to do if we look up on the date
I'm stuck we'll just pop it into here and see it cut cells and of course now I
can do if we look up on anything that's to the right of it which is really neat
so if I come back over here now and again I'll just talk you through the
vlookup so all we're doing here is we're going to go vlookup a so my most
recently used which is great and then of course the lookup value is going to be
the invoice number the table array again there's nothing underneath here so I can
easily just go straight across and you need to go that far
really in fact I don't even need the purchase order so that's but that far is
fine one two three so it's the third column across and I want an exact match
so I can use zero or false whichever one is easiest for you and of course I can
just copy that down now and you can see that actually the only thing that
they've got different is they're two six four six pictures order nine two seven
zero one one now this happens a lot when you actually start to do this to a
backwards comparison watch what happens if I come back here 97 zero one nine two
seven zero one one they have mesquite
voice number two for 6x if I come back over here to sex for sex so you can
easily go back to them and you can notice the $63 on the seventh so if I go
back over here six extra dollars on the seventh chances are that they have
actually paid it but it's just been keyed in and correctly so you can go
back and work that out between the two of you so that it kind of gives you an
idea and of course you could send off that two two three four cuz they even
got a copy of that and the two five nine oh they haven't got a copy of that
either so you can see that just using the vlookup either way you can really
quickly work out what each of you have got and we and maybe there have been
things that have been must keyed so really hope this has been helpful for
you and for more super helpful tips on vlookup in other related topics please
check out the video links at the end of this video so I really hope this
instruction has been helpful and is going to save you time just remember to
check you are using a unique lookup value the lookup value is in the first
column of the table array and you are using column indexed numbers not the
column letters okay if this has been helpful let me know in
the comments below and if you use Excel and want to stay connected just hit that
like button and please make sure that you subscribe because we're sharing new
videos regularly and if you know someone who would benefit from this help please
be sure to share this video with them thanks for watching and have a fabulous
day