Use VLOOKUP or XLOOKUP to Compare Two Lists For Matches or What Is Missing

In this Microsoft Excel video tutorial I explain how to use VLOOKUP or XLOOKUP to compare two lists. Using this method you will be able to see matches and what is missing. Your two lists may be on the same worksheet, on different worksheets in the same workbook or in different workbooks.

Table of Contents:

00:00 - Introduction
00:18 - VLOOKUP to compare two lists on the same worksheet
01:51 - Use IF ISNA formula to customise VLOOKUP results
03:02 - XLOOKUP to compare two lists on the same worksheet
04:08 - VLOOKUP to compare two lists in different worksheets
05:14 - Use named reference for table array in VLOOKUP
07:04 - VLOOKUP to compare two lists in different workbooks
08:27 - Refer to a named reference in a different workbook
------------------------

[TheMunecaLoca]

Really helpful, after scrolling through tons of videos and websites for a basic Vlookup guide, yours has hit the nail on the head and I now get it! Thanks :)

[IvanCortinas_ES]

Thank you for telling all these possibilities. Very complete and practical Chester!!

[DerekElliott-qnkz]

Nice video-I was eagerly awaiting for the part where you showed us how to Xlookup comparing two lists for matches in different workbooks, using Xlookup (and the IF and ISNA functions), sadly you never had that part

[JLD]

Hi Chester, I have couple of questions ehich aren't to do with this video but the series of video's you did on creating an invoice system about 1 year ago.

I've got through video 2 and all is working except for cells turning red if invoice is not paid. I am still on office 2010.

Also I'm looking at creating either another sheet or separate workbook for quotes. I have a worksheet in the invoice workbook
with actual orders but would like to pull information from the saved excel quote into the order sheet. Would you be able to do a video on creating a macro to do this or have you already done one.

The video's have been a great help so far so thank you.

Jason.

[kganesh]

I understood Thank you so much, Sir Chester!!

but I try to "Refer to a named reference in a different workbook" and entered list name already but pressed F3 no show list.

[peemun]

bravo, simple and easy to follow, others were a bit complicated :)

[abroazhar]

hello.. i have one very hard query, if someone in the world can crack it.
I have a text in cell A1 "I have a cat." and in cell B1 "I have a dog."
now excel formula or VBA code compare both cells and highlight the specific difference.
in above case, dog and cat should be highlight in same cell.
Or
another example (one step ahead), in numbers.
If I have number like "123456789" in cell A1 and "128456789" in cell B1.
both looks similar... but in cell B1 there is one typo mistake at place 3.
Excel compare both cells, whether some formula or VBA code (or any other way in excel) and highlight the specific difference.
in above case... 3rd pace digit (in same cell) should be highlight, as in both cells it does not match.
.
Real life scenerio is ..
we have data in one column with Original Number (say some Social Security Number, or Contact No.), but in column B, we have same number but with little typo mistake... one digit difference, or two digit difference, or might be more...
So how to know comparing both columns.... what is actual difference..