A Simple Guide How To Use VLOOKUP In Excel 2010

In layman’s terms, the VLOOKUP asks: ‘is the data from cell X in column Y?’

The VLOOKUP, the V stands for vertical, is one of the simplest formulas in Excel but can cut down your time dramatically when comparing large chunks of data. You can use VLOOKUP to match two values and return a value of data in a different column but the same row.

Understanding VLOOKUP

First I am going to look at each value concerning the VLOOKUP and discuss what they are, why you need to implement them and for what reason.

Lookup_value: Firstly you need to start with the lookup_value as this is essentially the X we are looking for in the Y column from the example above. Lookup_value can be a value or a reference. If lookup_value is smaller than the smallest value in the first column of table_array or a keyword you are searching does not appear in the table array, VLOOKUP will return a #N/A error value.

Table_array: Next is the table_array, which is the Y column from the above example. Now that we have outlined what data we would like to lookup (lookup_value) we need to look for it in the table_array (column Y). The values in the first column of table_array are the values searched by lookup_value. These values can be text, numbers, or logical values. It is important to select the entire column so you can lookup all of the column and its data with the lookup_value.

Col_index_num: The Col_index_num is the column number in the table_array from which the matching value must be returned. For example, a col_index_num of 1 returns the value in the first column in table_array. On the other hand, a col_index_num of 2 returns the value in the second column in table_array, and so on.

Range_lookup: The range_lookup is a logical value that specifies whether you want VLOOKUP to find an exact match or an approximate match. The exact match is known as the FALSE value, whereas the approximate match is known as the TRUE value. If FALSE, VLOOKUP will only find an exact match. If an exact match is not found the error value #N/A is returned. If TRUE or omitted, an exact or approximate match is returned. If an exact match is not found, the next largest value that is less than lookup_value is returned.

How to use VLOOKUP in Excel

The VLOOKUP can be applied in two different ways. Either by directly entering the formula into the Excel cell or by locating it under the formulas tab on the header menu:

1. First I will show you how to enter the VLOOKUP from the formula bar then I will demonstrate how to enter it directly into the cell. Once you have clicked on VLOOKUP from the formula tab, you will be presented with the below box:

The next step is to input all of the data into each corresponding field as mentioned at the start of the article. For example, the data you want to find will be added to the lookup_value, and then the column you want to search should be added to the table_array field. Then the Col_index_num is where you add what column number you want to lookup and lastly, the range_lookup will either be TRUE or FALSE depending on your query.

2. To enter a VLOOKUP formula directly into the cell, you need to follow the above process however you will implement each value into the formula directly using comma separations. I have put together a step by step picture to show how you would input each individual value and at what time:

If you’ve written the formula properly, the value you were looking for will be returned in the lookup result cell. After you’ve written all of your parameters, the formula performs a vertical lookup.

You can download a Vlookup example spreadsheet I have created to have a play with VLOOKUP. Regardless of the method you use, executing a VLOOKUP can be a quick way to find specific data in a large number of statistics and is an essential tool within Excel.

Want to know more about VLOOKUP? Get in touch – we’d love to help.