Lookup & Reference Function - VLOOKUP

This function is useful for comparing two lists or combining data from two locations into one data set.

The video shows the VLOOKUP function in use. The final written calculation is below: ​ =VLOOKUP(H3,C:D,2,FALSE) ​ This function is made up of 4 arguments. In this example, H3 contains the lookup value, or the item we want to look for that appears in the first column of our array.

The array is the next argument we identify (C:D). This relates to the data where our lookup value can be found, as well as any data we may wish to 'pull through' into our second data set. In our example, we only want to look for the customer name, but it may be the case that you want to pull through data from several columns. Make sure you include all of these in the array. ​ When deciding what your array should be, you will always need to ensure that the first column of your array reference contains the value you are looking for. ​ The third argument of your VLOOKUP function is the column index number. This is where you indicate which column of your array you are looking to pull through into your second data set. ​ Usually, columns are labelled with letters, but in a VLOOKUP they will be labelled with numbers instead. ​ Remember, the first column of your array (where the lookup value can be found) will always be column index number 1. ​ The rest of the columns that appear in the array will then be column index number 2, and so on. In our example, we have only used 2 columns in our array so C will be column index number 1 and D will be column index number 2 (hence why we have used 2 in our function). ​ If our array was G:P, column G would be labelled 1, H would be 2, I would be 3 and so on, right up to P which would be column index number 10. ​ Finally, to complete our VLOOKUP function, we are going to include a logical value or a range lookup. This will either be TRUE if you are looking for a close enough match against your lookup value, or FALSE if you are looking for an exact match. ​ 99.9% of the time you will be looking for an exact match so FALSE will be the logical value you will include here (as we have done with our example). ​ Once all four arguments are in place, you will be able to automatically pull through data from one location to another, either across the same spreadsheet or even between different workbooks.