Merging Data from Different Lists

Locked lesson.

Upgrade

About this lesson

XLOOKUP and VLOOKUP enable you to merge data to add greater context to the data.

Exercise files

Download this lesson’s exercise file.

Email Data - Lookups.xlsx
17.1 KB

Quick reference

XLOOKUP AND VLOOKUP

Vlookup

=VLOOKUP(lookup_value, table_array, column index, [range lookup])

  • Lookup value: this is the “what” you are looking for – the value from the first data set that you will use to find a similar value in the second data set to return a related value
  • Table array: the “where” you will look to find the “what”.  The second data set, with the lookup value in the left-most column of the range and the required return value to the right of that
  • Column index: the “return value”, the number of the column in the table array that contains the value you need to be returned.
  • Range lookup: Exact or approximate match.  The squared brackets indicate the argument is optional and the function will work without you adding an argument here.  Excel uses a default approximate match if you don’t specify. We used exact match in the video – Excel will return a #N/A if you use an exact match and it cannot find what you are looking for.
  • Limitation: Vlookup is programmed to look for a value in the left column of the table array and can return a value to the right.  If your return value is to the left of your search value, you will have some adjustment to do in your table before you can use a vlookup.  There are, of course many ways to accomplish this, but this is not the place to explain other options – the XLOOKUP solves the problem.
  • Error messages:
    • #N/A - Excel cannot find the value
    • #Ref - you supplied a 2 column table array and want a value from column 3

Xlookup

Note that XLOOKUP is only available in Microsoft 365

=XLOOKUP(lookup value, lookup array, return array, [if not found], [match mode], [search mode]

  • Lookup value: this is the “what” you are looking for – the value from the first data set that you will use to find a similar value in the second data set to return a related value
  • Lookup array: this is the “where”, you’ll notice the xlookup splits the table array into two separate arrays – the lookup and the return array.
  • Return array: this is the column that contains the return value. It must be the same height as the lookup array, otherwise you’ll get an error message (#REF)
  • If not found: the square brackets indicating an optional argument. This is used to specify a message to the user if the value is not found to replace the #N/A
  • Match mode: Exact or approximate match, also optional argument.  BUT the default is exact. Excel has a few more options where the approximate match is concerned, but you’ll have to refer to GoSkills free resource articles for more info.
  • Search mode: Not covered in the video – optional argument, and useful to consider when you work with really large amounts of data that slows down your computer. Again, GoSkills has a free resource article if you are interested to learn more.
  • Error messages: 
    • #VALUE - if the return array and the lookup array is not the same size
    • #N/A - value cannot be found
Login to download
  • 00:04 As a marketer contacts is key.
  • 00:07 When you have contacts around your relationship with a contact,
  • 00:11 you can provide more personalized,
  • 00:13 more relevant marketing content that's targeted at their needs.
  • 00:17 Personalized relevant marketing is the foundation for
  • 00:20 creating content that people really love.
  • 00:23 And, more than that personalized and relevant marketing is
  • 00:26 typically not the kind of marketing that annoys people into clicking on subscribe.
  • 00:31 So if you want to add contacts to your data, well VLOOKUP and
  • 00:35 XLOOKUP are tools that can help you do just that.
  • 00:38 With these functions you can merge data,
  • 00:40 which is handy when you're creating UTM tag links.
  • 00:44 You can categorize data, maybe to see if a prospect is worth reaching out to.
  • 00:48 And you can compare data, which is nice for comparing metrics month over month.
  • 00:52 And there's a lot more you can do with these two functions.
  • 00:55 In this example, I've got an email list that I imported from an external source.
  • 01:00 The data contains a country code, but it doesn't show the full country name,
  • 01:04 and I need the full country name to add to my marketing report.
  • 01:08 In another list, I have the country code and the full country name, so
  • 01:13 what I can do, is look for the country code CA in my country code list.
  • 01:17 And if I find it, I can see that it's for Canada, and I can type in Canada.
  • 01:24 Same with the US, I can look for US in my list.
  • 01:27 And when I find it, I know it's for the United States, and
  • 01:31 I can type in United States.
  • 01:33 And you can see that this will take a while, and if I make typing errors,
  • 01:36 I'm going to end up with data that I can't work with.
  • 01:40 Lucky for us, Excel has lookup functions to help us with this.
  • 01:44 What we did, is we use the country code from one list to look it up in another,
  • 01:49 and then we returned a related value.
  • 01:52 And lookup functions are very useful for marketers, as we often need to
  • 01:56 lookup related information from different sources, campaigns, and platforms.
  • 02:01 Now VLOOKUP is very popular but it has a few limitations, so due to its popularity,
  • 02:06 the Excel programmers created XLOOKUP which is the newer version of VLOOKUP,
  • 02:11 to get rid of some of those limitations.
  • 02:14 To make either one work, a VLOOKUP and an XLOOKUP needs something that it
  • 02:19 will look up, something it needs to find, like a country code in this case.
  • 02:24 And it needs a place where it will look for it, that's going to be this column.
  • 02:30 And it needs something to return something else related to the value.
  • 02:35 What it needs to find is a country code, where it needs to look for
  • 02:39 it is this column, and once it finds it in this column, we need to return something.
  • 02:45 You get two types of lookups an exact match or an approximate match.
  • 02:50 And in this example, we're going to use an exact match.
  • 02:54 So in other words, if I have CA, I want to return Canada.
  • 02:58 But if I can't find exactly CA, I don't want to return any value.
  • 03:04 So, let's try this out.
  • 03:06 Type in =VLOOKUP, and the lookup value is what it needs to find,
  • 03:13 and that is my first country code, CA.
  • 03:17 The table array means, where do we want to look for it, and
  • 03:21 this is my table array, over here.
  • 03:26 The VLOOKUP is going to look for
  • 03:28 the CA value in the first column of the table array.
  • 03:32 Next is a column index number, that's asking what do you want to be returned?
  • 03:37 And if it find CA in this column, do I want to return column one or column two?
  • 03:43 Not one, because I already have that as the country code, I want column two,
  • 03:47 so let's type in a two.
  • 03:50 And the range lookup, is an exact match or approximate match, and we said,
  • 03:54 we want an exact match.
  • 03:56 So, we're going to select false, complete the formula, and we get Canada.
  • 04:03 So VLOOKUP, what does it look for?
  • 04:06 Where does it look for it?
  • 04:08 And if it finds it, what does it return?
  • 04:11 And is it an exact or an approximate match?
  • 04:15 If I copy this formula down, the blue value needs to move down, but
  • 04:20 the red values need to stay fixed.
  • 04:23 And to lock those red values, I have to add dollar signs, the shortcut
  • 04:28 to do that is F4, or you can hit command T on a (Mac) and then hit Enter.
  • 04:34 And then we can double click the fill handle in the corner, and
  • 04:37 it's going to copy that all the way down, and that's how we use VLOOKUP.
  • 04:41 Now let's do this look up again, but let's try XLOOKUP this time,
  • 04:45 you'll see it's slightly different, but it's pretty powerful, it's pretty nice.
  • 04:49 So we type in =XLOOKUP, and the lookup value.
  • 04:54 Once again, we're looking for the country code, so let's click on that cell.
  • 04:57 Now, while VLOOKUP has a table array, and you would select both of these columns,
  • 05:04 the XLOOKUP splits the table array into a lookup array, and a return array.
  • 05:10 So the lookup array, where should we look to find D4, for it is going to be this
  • 05:14 column, and you do not need to highlight anything else, just this one column.
  • 05:19 And what does it return?
  • 05:21 Now, you highlight the second column.
  • 05:25 The other arguments are in square brackets, so they are all optional.
  • 05:30 One of them is for an exact or approximate match, but
  • 05:33 the XLOOKUP is programmed, so the default is an exact match.
  • 05:38 In other words, if I don't put anything else in that argument,
  • 05:41 the default is an exact match.
  • 05:43 So I can close the parentheses right here, and my XLOOKUP is done.
  • 05:48 And there's Canada.
  • 05:50 Now just like with VLOOKUP, we do need to lock some references if we're going to
  • 05:55 copy this formula and use it in the other cells.
  • 05:58 The blue value doesn't need to be locked, but the red value does.
  • 06:01 So we can lock the cell references once again f4 on Windows, or
  • 06:06 Command T on the Mac, the purple value is also locked with dollar signs.
  • 06:11 And then we can double click the fill handle,
  • 06:13 copy that formula all the way down, and there is the power of lookups right there.
  • 06:18 XLOOKUP and VLOOKUP make finding, categorizing, and
  • 06:22 merging data a piece of cake.
  • 06:25 How you choose to harness that power as a marketer is entirely up to you

Lesson notes are only available for subscribers.

Gift this course