Compare Lists with VLOOKUP, VSTACK and UNIQUE

Locked lesson.

Upgrade

Quick reference

Compare Lists with VLOOKUP, VSTACK, and UNIQUE

Learn how to compare lists using VLOOKUP and two new Excel functions, VSTACK and UNIQUE. 

When to use

We can use VSTACK and UNIQUE to combine multiple lists together and extract the unique values. VLOOKUP allows us to compare lists and add error handling to make the results more meaningful. 

Instructions

We are going to compare a list of Invitees with a list of Attendees to find out who was a no-show. 

We want our formulas to be dynamic so we need to start by formatting both datasets as Excel tables. 

  • Click in the Invitees table. 
  • Press CTRL+T. 
  • Click OK. 
  • Click in the Attendees table. 
  • Press CTRL+T. 
  • Click OK. 

Compare with VLOOKUP

We can compare two lists of data using the VLOOKUP function. VLOOKUP looks for a lookup value in a range of cells and returns a value from another column. 

In this example, we are looking up the Invitee name [lookup_value], in the Attendees table [table_array], and we are returning the Attendee name in column 1 [col_index_num] if there is an exact match [range_lookup].  

Wherever the formula returns an #NA error, those are the no-shows. 

We can make this easier to read by adding error handling to the VLOOKUP formula so the #NA errors are replaced with meaningful text. 

We can go a stage further and use Conditional Formatting to highlight all cells that contain the words 'No Show'. 

Compare with VSTACK and UNIQUE

VSTACK and UNIQUE are two of the newer functions in Excel. UNIQUE is available for users of Excel for Microsoft 365, Excel 2019, and Excel 2021. VSTACK is only available to users of Excel for Microsoft 365. 

The VSTACK function allows us to stack multiple arrays on top of each other. This is useful when we have two or more lists of data that we'd like to combine into one list. 

The UNIQUE function is a versatile function that returns a list of unique values from a list and ignores duplicates. 

In this example, we have two lists of employees who attended a two-day seminar. Some employees only attended one day, and some employees attended both days. 

We want to send an email to all attendees but we don't want to send out duplicates to people who attended on both days. 

To do this, we are going to combine both lists together and then extract a unique list of names. 

Hints & tips

  • VSTACK is currently being rolled out to Microsoft 365 users. 
Login to download
  • 00:04 So let's now do a similar thing, but this time we're going to use VLOOKUP instead.
  • 00:09 So once again, I have two tables, I have a list of invitees,
  • 00:13 I have a list of attendees along with their email address.
  • 00:17 And I want to see how many of these people were a No Show, so
  • 00:21 that I can send them an email.
  • 00:23 So the first thing I'm going to do here is put both of these into tables, CTRL+T.
  • 00:27 Yes, my table has headers.
  • 00:29 Let's give this table a name, so we're going to jump up to Table Design.
  • 00:33 And in Table Name, let's type in Invitees.
  • 00:38 I'm going to do exactly the same for the Attendees table.
  • 00:41 Ctrl+T, yes, my table has headers.
  • 00:45 And let's call this one Attendees.
  • 00:48 Now the reason why I like to put these things into tables is so
  • 00:52 that the formulas are dynamic.
  • 00:54 So if anything changes, if anything gets added into this dataset,
  • 00:58 the formula is going to automatically update, because the table will auto expand
  • 01:03 to accommodate any new information that you add.
  • 01:06 So let's perform our VLOOKUP.
  • 01:08 My lookup value, while I'm looking up George Mooney,
  • 01:11 the first one just here, table array, where am I looking up this name?
  • 01:16 Well, am looking up the Attendees table.
  • 01:18 So I'm going to select both of these columns,
  • 01:21 which column of information do I want to return from the Attendees table?
  • 01:27 Well, I'm looking for the name, so I want to return column number 1.
  • 01:31 Remember, VLOOKUP numbers columns from left to right.
  • 01:34 So the Attendees are in column number 1.
  • 01:37 Do I want to do an exact match of the Attendee name?
  • 01:41 Yes I do, so we want zero in here, let's close the bracket,
  • 01:46 and hit Enter, and check out what we get.
  • 01:49 Now wherever we have an N/A in here, that person was a No Show,
  • 01:53 because it can't find the invitees name in that attendees table.
  • 01:58 Now currently, all of these just say N/A, which isn't very self explanatory.
  • 02:03 So what I would probably do to help anybody out who's looking at this
  • 02:07 spreadsheet is, I would make it very clear that these people
  • 02:10 with N/A next to them are the No Shows.
  • 02:13 So let's jump up to cell C7, and let's edit our formula and
  • 02:18 just add in some IFNA a error handling in here.
  • 02:22 Now what IFNA will do is, it will look through the column four any occurrence of
  • 02:27 the N/A error and then it will change it to whatever you specify.
  • 02:31 So our value in the cell is generated by the VLOOKUP command.
  • 02:35 The second argument is what we want to output if it finds an N/A.
  • 02:40 So in quote marks, remember text has to go in quote marks we want it to say No Show.
  • 02:46 Let's close the bracket, hit Enter, and now that looks a lot better.
  • 02:50 And what we could do is go a stage further, and maybe highlight using
  • 02:54 Conditional Formatting, every time we have an occurrence of the word No Show.
  • 02:59 And we could highlight them in a different color so that they really stand out.
  • 03:03 So that is the second method you can use to compare two lists together.
  • 03:08 In this final example, we have a slightly different scenario.
  • 03:12 So I have two lists of attendees for, let's say,
  • 03:15 a seminar that ran over two days.
  • 03:17 Now I want to send an email to follow up to all of the attendees,
  • 03:21 but I don't want to send the email out twice to the same person.
  • 03:26 So if the person attended both days,
  • 03:29 I don't want them to appear twice in my list.
  • 03:32 So let's first take a look at how many people occur in both lists.
  • 03:36 So how many people attended both days?
  • 03:38 Let's select the first list, hold down CTRL and select the second.
  • 03:43 Now if we go into Conditional Formatting, I'm going to create a new rule.
  • 03:48 And we're going to say format only unique or duplicate values.
  • 03:52 I'm looking for duplicates,
  • 03:54 and let's just add formatting, let's just add an orange fill.
  • 03:58 So let's click on OK.
  • 04:00 So, I can see that all of these people highlighted in orange attended both days,
  • 04:05 they appear in both lists.
  • 04:07 So what I effectively want to do in column F is, I want to get a big long list of all
  • 04:11 of the attendees, but I don't want to have any duplicates in there.
  • 04:15 So I don't want to have George Mooney twice if he attended on both Day 1 and
  • 04:20 Day 2.
  • 04:21 So we going to do two things here using two different formulas.
  • 04:25 And it is worth mentioning that I'm going to to use a formula that was only
  • 04:28 released a couple of months ago in Excel.
  • 04:31 So if you have Excel for Microsoft 365, then you should have this function,
  • 04:35 if not then I'm going to show you a workaround that you can use.
  • 04:39 Now the function that I'm talking about is the VSTACK function.
  • 04:43 Now what VSTACK does is, it will take two arrays, two cell ranges, and
  • 04:48 it will just simply stack them on top of each other so
  • 04:51 you have one big long list as opposed to two separate lists.
  • 04:54 So array 1 is this array, array 2 is this array just here.
  • 05:00 So if I close the bracket and
  • 05:01 hit Enter,I'm going to get a big long list of everybody in both of these lists.
  • 05:06 So this list is going to contain duplicates.
  • 05:09 Now the work around for this if you don't have VSTACK is simply just to copy and
  • 05:14 paste these into one big long list.
  • 05:16 Once we have them, we want to remove the duplicates from this list so
  • 05:20 we only get the attendee showing once.
  • 05:22 So I'm going to edit my formula and
  • 05:24 we're going to use the UNIQUE formula to do this.
  • 05:27 I don't need any other arguments, so I'm just going to close the bracket,
  • 05:31 hit Enter, and it's going to remove all of those duplicates from that list.
  • 05:35 And I now just have a list of all of the individuals, and
  • 05:39 I can send emails out to them with no duplicates.

Lesson notes are only available for subscribers.

Gift this course