News & Updates: Data in Practice: VLOOKUP – Merge Like Magic

The first time I taught myself how to use VLOOKUP, it was like I had unlocked a magic trick. I had two large lists of data, one shorter than the other, and needed to get information from the longer list into a new column corresponding to the same name in the shorter list. After some digging, Excel had a solution. It is where VLOOKUP (and a more dynamic set of functions – INDEX and MATCH) can be a lifesaver.

Laying the Groundwork – Unique Identifiers

VLOOKUP can be used whenever you have two sets of data with a shared unique value common to both. Here’s a brief example using sample information from a hypothetical document review (sample data can be accessed here):

This sample contains the unique value of the Bates Number. That is, in each data set, there will only be one line of information that is associated with that value – a unique identifier. That sets us up to be able to “query” one data set for the unique piece of information, and return a value from a different column.

Merging Data with VLOOKUP

Let’s say you want the notes the partner took in “List 2” to join the more robust metadata in “List 1,” an export from the database. You will set up a VLOOKUP function in a new column (“Notes”) in List 1.

Let’s break this function down, using the component parts we covered in the prior post.

Here’s the full function, put into the cell in which we want responsive information: 

=VLOOKUP([@[Bates Number]],Table1114[#All],3,FALSE) 

1. First, we indicate in the cell that we are typing a function with an equal sign: “=” 
2. Next, we tell Excel the function that we want to use: “VLOOKUP” 

3. The next piece of information we include is the information in the current table (List 1) that Excel will be looking for in the other table (List 2). Because this information is formatted as a table, this is listed as “[@[BatesNumber]]” – telling us that the relevant cell value is the same row in the column “BatesNumber”. (If it is not formatted as a table, in this example the cell reference would be “A4”.) 
4. Next, Excel needs to know the “table_array” to look at. That is, what information are we comparing to see if a value is present? I selected the information in List 2, which was formatted as a table, so it appears as “Table1114[#All].” That is, Excel is looking at the entire table. This could also be written as “G2:I5.” 
5. Excel also needs to know what piece of information to return. This is referenced based on the position of the column, or “col_index_num” (column index number). The match to the lookup value must be in the column farthest to the left (column 1) in the “table array”. So, the Bates Number in List 2 is in column G to the far left of our list. Counting up from the left, simply write which number column you want to return information from. Here, there are three columns in List 2, and we want to return information from the right-most column: thus, “3”.
6. Finally, the last element of the VLOOKUP function is a TRUE or FALSE. You want “FALSE” – which means that there will be an exact match of the lookup value, here, Bates numbers. “TRUE” will return results which do not match exactly.

Reviewing the Results

Let’s take a look at the output.

To begin, Excel tells us whenever there is NOT a match, with the value “#N/A”. This unsightly output can be remedied with an easy “If” function we will get to soon.

Then, two additional pieces of information are revealed. First, we know if there is a corresponding value in the two lists, because the cell will be populated with a value, not an error. So, we know that DEF01423, -0056- and -1472 show up on both lists. Second, we know what was written in that important notes column, because it’s been imported into List 1. Now, that “Super helpful” document will be linked with the sender and the date!

This is just scratching the surface. Next time, we will dive a little deeper into the application of VLOOKUP, before pivoting to “Index” and “Match.”