# How do you use Microsoft Excel VLOOKUP?

## What is Microsoft Excel VLOOKUP used for?

**VLOOKUP **is a powerful formula (or function) that allows you to lookup a value you want to find in another Excel range (table or list) and find additional information. As an example, in a grocery store guide, you would lookup the grocery item you want (example: fruit) and find the isle where the item was located.

## The Syntax of VLOOKUP is:

**VLOOKUP**(**lookup_value**,**table_array**,**col_index_num**,range_lookup)

lookup_value: this is the value to search for in the first column of the array.

table_array: this is two or more columns of data - the values in the first column are the values searched for by lookup_value.

col_index_num: the column number in the table_array from which the data will be returned from.

Range_lookup: a logical value that indicates whether an exact match is required.

## How do I use Microsoft Excel VLOOKUP?

Many people will look at the syntax and definitions of **VLOOKUP **and simply give up. However, it is a very simple formula to use. The steps below will demonstrate how to use **VLOOKUP **in Microsoft Excel:

## Creating Data Tables for use with VLOOKUP

The two tables below contain information about employees of a fictional company. **Table 1** has indicitive data about the employee, while **Table 2** has information about the region and office.

The common data in both tables is the **Name **of the employee and therefore this must be placed in the first column of the table. While in this example it is not relevant, it is always a good idea to sort the values in the first column in order **low to high** - this becomes important when you are not searching for an exact match.

## Creating a Table where I want the combined data to be listed

I’ve decided that I want a table that has the **Office **and **Salary **for a few employees. I therefore create a third table that lists the **Name **of the employees I need this information for and add two columns to retrieve the information from the other tables

## Filling in the combined data table using VLOOKUP

In the below **VLOOKUP **example we will create a **VLOOKUP **to find the Office for each employee:

- Click on cell
**D6**(this is where we will create the**VLOOKUP**formula) - Press the
**fx**button to open the formula wizard.

- Choose the
**vlookup**option.

- Press the
**OK**button.

Now we will ‘build’ the VLOOKUP

- In the
**Lookup_value**field enter the cell that you want to look at. In this case it is the first cell in the**Name**column (cell c6)

- In the
**Table_array**field navigate to the table where the relevant data is 0 in this case it’s office in**Table 2**(Note – I only include the main headings in the**Table_array**– I didn’t include the ‘Table 2 – Office Data’ summary heading)

- In the
**Col_index_num**field enter the column with the data you want to return. In this case the third column in**Table 2**– column 1 would be the Name column – as the**Office**column is the third column in the table we use 3 as the**Col_index_num**

- In the
**Range_Lookup**field choose**True**or**False**, in this case**False**. If you want an exact match (as we do in this case) then use**False**, if you want to find the nearest entry to the data you are looking up use True.

- Once you have filled all the boxes in, press OK. You will now see the cell populated with the
**Office**for**Andrew North**

- Copy this formula to the cells below.
- Repeat the above this time selecting table one, and column 4 (
**Salary**)

The table should now contain all of the information you want from the other tables.

## Looking up non-exact information with VLOOKUP

Often when you use a **VLOOKUP **you may not require an exact match. In the example below I have set a table up with Rating and Bonus – the ratings are listed from 1 to 5, but can actually have any decimal value 1 or above.

When I use **VLOOKUP **with this data I must set the **Range_lookup** to **TRUE **so that it knows it is not looking for an exact match. I have illustrated some results (including a few errors) in the example below.