VLOOK UP

The VLOOKUP function in Excel is a tool for looking up a piece of information in a table or data set and extracting some corresponding data/information.  In simple terms, the VLOOKUP function says the following to Excel: “Look for this piece of information (e.g., bananas), in this data set (a table), and tell me some corresponding information about it (e.g., the price of bananas)”.

VLOOKUP Formula

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

To translate this to simple English, the formula is saying, “Look for this piece of information, in the following area, and give me some corresponding data from another column”.

The VLOOKUP function uses the following arguments:

1. Lookup_value (required argument) – Lookup_value specifies the value that we want to look up in the first column of a table.
2. Table_array (required argument) – The table array is the data array that is to be searched. The VLOOKUP function searches in the left-most column of this array.
3. Col_index_num (required argument) – This is an integer, specifying the column number of the supplied table_array, that you want to return a value from.
4. Range_lookup (optional argument) – This defines what this function should return in the event that it does not find an exact match to the lookup_value. The argument can be set to TRUE or FALSE, which means:
   • TRUE – Approximate match, that is, if an exact match is not found, use the closest match below the lookup_value.
   • FALSE – Exact match, that is, if an exact match not found, then it will return an error.

How to use VLOOKUP in Excel

Step 1: Organize the data

The first step to effectively using the VLOOKUP function is to make sure your data is well organized and suitable for using the function.

VLOOKUP works in a left to right order, so you need to ensure that the information you want to look up is to the left of the corresponding data you want to extract.

For example:

In the above VLOOKUP example, you will see that the “good table” can easily run the function to look up “Bananas” and return their price since Bananas are located in the leftmost column.  In the “bad table” example you’ll see there is an error message, as the columns are not in the right order.

Step 2: Tell the function what to lookup

In this step, we tell Excel what to look for.  We start by typing the formula “=VLOOKUP(“ and then select the cell that contains the information we want to lookup. In this case, it’s the cell that contains “Bananas”.

Step 3: Tell the function where to look

In this step, we select the table where the data is located, and tell Excel to search in the leftmost column for the information we selected in the previous step.

For example, in this case, we highlight the whole table from column A to column C.  Excel will look for the information we told it to look up in column A.

Step 4: Tell Excel what column to output the data from

In this step, we need to tell Excel which column contains the data that we want to have as an output from the VLOOKUP.  To do this, Excel needs a number that corresponds to the column number in the table.

In our example, the output data is located in the 3^rd column of the table, so we enter the number “3” in the formula.

Step 5: Exact or approximate match

This final step is to tell Excel if you’re looking for an exact or approximate match by entering “True” or “False” in the formula.

In our VLOOKUP example, we want an exact match (“Bananas”), so we type “FALSE” in the formula. If we instead used “TRUE” as a parameter, we would get an approximate match.

An approximate match would be useful when looking up an exact figure that might not be contained in the table, for example, if the number 2.9585. In this case, Excel will look for the number closest to 2.9585, even if that specific number is not contained in the dataset.  This will help prevent errors in the VLOOKUP formula.