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.
Comments