Compare two lists to find missing values using VLOOKUP in Excel - Excel Tips and Tricks - video Dailymotion

RobertBarone

Discover how you can compare two lists to find missing values using VLOOKUP in Excel.   Essentially using VLOOKUP to compare two columns for matches, or in other words, compare  two lists to find missing values using VLOOKUP in excel multiple. Some people like to use VLOOKUP to find missing data in two columns, like I'm demonstrating in this video. Also, they like to compare two columns in excel using VLOOKUP and return a third value. But in a nutshell, I'm using the VLOOKUP to compare two lists. And compare two columns and find missing values in excel.   Ok let answer this question, how to do a VLOOKUP in Excel to find missing data? Or how do you compare two lists in Excel to see if they match? Well.... you came to the right place, and this video explain just that.    How to compare two lists to find missing values WITHOUT FORMULA in excel - Excel Tips and Trick  https://youtube.com/shorts/pJtB8dbbimw?si=iL8qnDJ_WVAhKhAX   Compare two lists to find missing values using XLOOKUP in Excel - Excel Tips and Tricks  https://youtube.com/shorts/nOwMXkZJ5HU?si=N80igNW7Vr-xCK5x   Compare two lists to find missing values using VLOOKUP in Excel - Excel Tips and Tricks  https://youtube.com/shorts/1XGIPzsvS_Y?si=cu3ajNdT3lXFX3LB   How to compare two lists in Excel using Conditional Formatting - Excel Tips and Tricks  https://youtube.com/shorts/GX-BYEgcnRA?si=16oLe6kcxZExM4Df   How to compare two lists to find missing values in excel - Excel Tips and Tricks  https://youtube.com/shorts/dl75Lz_jaPs?si=-UTAvmCRdTl6ZZgm   Excel Tips and Tricks - Compare Two Lists In Excel And Highlight  https://youtube.com/shorts/xJoy-nboV6A?feature=share   Summarize Duplicates in Excel - Excel Tips and Tricks  https://youtube.com/shorts/-IYUTWVmVTU?si=aN-hfbyQD0QEPcgm   Find difference quickly in Excel Comparing 2 List - Excel Tips and Tricks  https://youtube.com/shorts/8_-lN-yKf04?si=itTI-rcCKOaG7zVc    How to do a VLOOKUP in Excel to find missing data?, How do you compare two lists in Excel to see if they match?,  vlookup to compare two columns for matches,compare two lists to find missing values using vlookup in excel multiple,vlookup to find missing data in 2 columns,compare two columns in excel using vlookup and return a third value,vlookup to compare two lists,compare two columns and find missing values in excel,vlookup to find matches in two sheets,how to compare two excel sheets using vlookup step by step,

Transcript

00:00I recently posted an Excel tip on how to compare two lists to find missing values in column

00:05B that is in column A. But recently, someone had posted a comment saying, what's wrong

00:10with using VLOOKUP and XLOOKUP along with the if statement? Well, I'm here to demonstrate

00:16this. We'll start off with VLOOKUP first here. The first thing I have to do is to highlight

00:20the registered name like this, and then go to conditional formatting here. From here,

00:26you're going to click on new rule. And in here, the rule type, you're going to change

00:30it to formula. And the formula you'll be using is equal VLOOKUP. And the first argument on

00:37the formula would be the first name on that list. So it's going to be A2. And the second

00:43argument would be the whole of column B like this. And then the third argument, since this

00:49table only has got one column, so we're going to say one here, and comma, zero to be exact.

00:56So what this does is that it looks for any names that's in here, but not in here. And

01:02if the name is not there, it's going to return N A. So basically, a true and false statement

01:09here. So we're going to surround this VLOOKUP with is N A like this. And then once that's

01:15done, hit enter, and click on done. And you can see that the conditional formatting has taken

01:21effect here. So like for example, Old Shepherd is not in this list here. But Claire Riley

01:27Groom, she is in this list here. Let me clear the thing again, so that I can demonstrate the

01:33next function. The next one we're going to demonstrate will be the XLOOKUP here. So highlight

01:40the name again, like before. Again, go to conditional formatting and new rule. It will come up here.

01:46The rule type would be formula, like before. And now this time around, we're going to use

01:52XLOOKUP. And the first argument in XLOOKUP will be the same thing as VLOOKUP, A2, comma, B,

02:03B, and we're going to say B, colon B again, comma, and blank if it's not matching, and zero to be

02:12exact. But the thing with XLOOKUP is that if the name doesn't exist, it will return a blank,

02:19like we have stipulated here. Not like VLOOKUP, which returns a pound N A. So to mitigate that,

02:27we're going to have to write a length condition to check for length, like this. So basically,

02:33this thing will return the length. And if the length is less than or equal to zero, which means

02:39the name here, the name exists here, but doesn't exist here, we're going to color it red. So we're

02:45going to hit enter and click done. And there you go. That's how you use VLOOKUP and XLOOKUP to compare

02:52two columns.

Compare two lists to find missing values using VLOOKUP in Excel - Excel Tips and Tricks

Category

Transcript

Be the first to comment

Recommended