Compare Two Lists Using the VLOOKUP Formula

hello and welcome to this video tutorial from computer guard calm in this video we're going to look at how to compare two lists using the vlookup function so a common requirement in excel is the ability to compare two lists to overlook for missing items effort matching items or to form some other kind of action in this example we have these two customer lists so both lists are exactly the same with the exception of I believe it's free people but there are free more people think it's free in this set first customers lists then in the second customers list see this goes to 89 this goes to red red blue blue blue blue 92 sellers free extra people yes so we want a way of comparing these lists so the size of them is irrelevant to be honest we're going to compare them and see which I say which items or which customers in this case are in customers 1 but nothing customers 2 so we're going to perform this analysis on the customers 1 sheet and we're going to begin by putting a vlookup function in column G since help build this up I want to write the function in column G so especially if you're new to vlookup or or you know bit out of practice with it's kind of fuzzy on vlookup will get the opportunity to write that but in a detail and to return a value determine if they're there or not when I'm going to expand on that and use conditional formatting to highlight the row a certain color if they are one of the customers not on the other list so this click in cell g2 and I'm going to type equals V lookup so a function that will look for something in a table and return information about it and the first question they have for us here just pressing a tab key there is what is it I want to look for and I'm going to choose the customers ID in this case because that's a unique value that I can use to check if Maria is in the other customer list as well and I'll put a comma so they then ask for the table a right where is the table I'd like to look for Maria in so this is my customers twos I'm going to click on customers too and I'm going to highlight the whole range using any technique you have for selecting a table range whether it be a range name where it be dragging like I am here at or shortcuts like control ie control shift end all of these could do a job I'm dragging for something a little bit more visual there and along the top I can see the reference customers to a two to f-89 now I'm going to press the f4 key the function key f4 on the keyboard there to make that reference absolute I'm going to fix it so that when we copy the formula down on the other sheet then this table does not move it remains a two is the first record f89 is the last record of the table come on we now have the column index number question which is what information about that customer would you like to return now really I don't want to return anything I just want to know if they're there but this is a mandatory question so we need to answer it so I'm going to ask it to bring back column 1 I'll bring back the ID if they bring back the ID comma range lookup what type of lookup an approximate one or an exact one it's definitely exact we want to know if they're there you have to meet that membership IB that customer ID closing bracket and enter so this is vlookup at a moment but I double click to copy that formula down then it is bringing back the ID if they are on the other list and bring about this ugly error message if they are not on the other list so as a scroll through already this is a way that I can identify the free members that are not on the other list it's quite visible and you know this may be for you this may be good enough for that that work we quietly live for that however we don't we would like to do something you know a little bit better looking than that maybe even a little bit more obvious and then that's pretty obvious to hash your name and what we're going to do is double click on cell g2 and add another function to it I'm going to put this function at the start of vlookup and it's called easier now what this function is going to do is to look for the presence of this hash nad error message if it sees it it will return the true answer if it doesn't it will return false so we're gonna get true if they're missing folks if they're not I'll just put a closing bracket off the vlookup there and press Enter and double click this down and here we go we've got these false is they're not there sorry they are there true that that person Elizabeth is missing and so on and so forth that's probably a little bit harder to read so you could say look better before in a way of identifying them but we're just trying to clean up the way that identifies them and our next step would be that on cell g2 trying to build this function of iteratively bit by a bit rather than just throwing it all out at you the next step would be at the start of this formula to add an if function and this if function can then be used to display whatever text you want so for example for equals if they ask for the logical test and what we have written the is an A and vlookup is our test so I'll click at the end and put a comma that will display true or false and indeed we can see it doing that it's in the other cells so value of true what would you like to do if it is true instead of the word true I might want to say the words not found between speech marks I could write the words not found above a number to on a UK keyboard comma value Athos what if they are not there I saw what if they are there I'm getting really confused what if they are in the other list well I don't want to do anything because I really care about now looking for those that I'm missing so for two speech marks two double inverted quotes there with nothing in the middle it is an empty string closing bracket that will make the cell blank so indeed when I press ENTER I get blank and when I double-click down I now get the words not found if they're not they're found and nothing if they are I can obviously use this information to filter my list or to run count if functions to find out know how many are missing and yeah there's a lot we could do now that we have this information I candied by an additional vlookups do something else where maybe that's what we look good that's one technique the little benefits that technique a lot of you might find it easier to use that one however I want to I'll keep that there but on to show an additional technique on to look at changing the color of the row so especially if your tables quite wired this is going to be slightly clearer than that and before I go into I'm just going to take a copy of part of their function I mentioned in this bit I don't need the if function cause if function was used to display different text you take different actions depending on the outcome from vlookup I want to use conditional formatting as an alternative to if in this case as I want to change the color not display certain words so the way I locate the missing records is the same will be using vlookup will be using is an A to kind of translate the answer from vlookup really because we know if it cannot find the records it will say hasha name going to select that part is an a to the second close in bracket take a copy however you like to take copy control C for me now come out out of escape I'm going to highlight the whole table I'm not going to include headings because I'm not I don't care the headings are there or not in fact I know that they are so kind of pointless I'll just drag it down to the bottom here again there's not that many records that's one heck of a surname maze and I own a hollow this whole list it's conditional formatting on the Home tab that I'm looking for now and I'm going for new rule because we need to put a formula new rule use a formula to determine which cells to format and I'm going to paste the formula that we already have now there's one last thing I need to do to this formula look quite right because of what we're doing them because a moment ago we wrote a formula in a single cell but now I've highlighted what have I highlighted at six columns and 91 rows of data so I am going to stick a dollar sign and I'll do this manually before column a is the first question the one called lookup value from vlookup because I wanted to look down the different rows at the 91 members but I don't want it to move off the ID column so I'm going to fix column a do not move from column a but yes number to the row number is relative and I'll click my format button below and choose a nice feel color I think that's a lovely fill color or have some bold nice and recognizable here click OK and ok again and that should or whatever got it's nothing happening whatever done wrong there it's just have another look the scroll sup I done something wrong here that scroll that up let's get a near conditional formatting manage rules well if I'd done let's edit that rule I've got speech marks around this function for some reason this take them out so you get a copy and paste and that's okay that again here we go apologies for a little mix-up there but at least we know if it happens to you look at that some lovely yellowy sort of gold and bold formatting as an alternative way of recognizing if those members are not there there we go there's Michael and indeed there's reach her at the end there so a nice visual way of seeing missing records and just like a use for the if function I could turn on my filter and do some further analysis by filtering using the color of a sales you can easily locate the missing items now as well if I needed to maybe export them somewhere else or it's clear that filter yeah I wanted to print this list I can actually manage and and kind of analyze work with this and with the information want to know and it's all kind of stemmed with a conditional formatting me functionally involved what kind of stemmed from the power of vlookup that is what we're looking identifying where if they were there or not thanks for watching I hope you find this tutorial useful please check out some of our other tips and tricks a computer Gaga calm

Channel: Computergaga

Views: 2,110,968

Rating: 4.8210025 out of 5

Keywords: Microsoft Excel (Software), vlookup formula, vlookup function, compare two lists, highlight missing items, conditional formatting, computergaga, vlookup formula between two worksheets, compare two lists in excel, compare two lists in excel for matches, compare two lists vlookup, excel for accounting, alan murray, excel for data analysis, compare two lists in excel for differences, compare two lists using the vlookup formula, conditional formatting compare two columns

Id: GdrOFBBEMQ8

Length: 12min 49sec (769 seconds)

Published: Tue Nov 04 2014