### Explanation of the VLOOKUP function

The image below shows the 4 arguments that the VLOOKUP function is asking for, each color coded to help you follow along:

- lookup_value (blue)
- table_array (red)
- col_index_num (green)
- range_lookup (yellow)

**Video – How to use a VLOOKUP in Microsoft Excel**

**VLOOKUP explanation continued**

#### 1. lookup_value (boxed in blue)

In our example the VLOOKUP function is referencing the **lookup_value** in cell H3 (boxed in blue). The value in cell H3 is “Billy” and this is the value that our VLOOKUP function will be searching for in our **table_array**.

As an alternative you could write “Billy” (has to be in quotations because it is text) directly into the VLOOKUP function and it would work the exact same. To do this you would replace H3 with “Billy”.

**Note:** It is easier to make changes to your **lookup_value** when your VLOOKUP function references the value in a cell because you can easily change your **lookup_value** and Microsoft Excel will automatically calculate the new result.

#### 2. table_array (boxed in red)

In our example the **table_array** is B3:F22 (boxed in red) because this is our data range we want to search in. When using a VLOOKUP function you can either manually type your data range or select your data range using your mouse.

**IMPORTANT:** **your lookup_value must exist in the left-most (the 1st column) in your data range. **This means that your **table_array** always has to start with the column that includes the data for the **lookup_value** you are searching for.

#### 3. col_index_num (numbered in Green 1 through 5)

This argument tells the VLOOKUP function which column to return the value from. In our example it’s 4 meaning the result will come from the 4th column in our **table_array** which is the “Quantity Harvested” column. You always start counting from left to right with the left-most column being column 1. If we wanted to bring back the “Quantity Sold” instead, our **col_index_num** would need to be 5.

#### 4. range_lookup (underlined in yellow)

You have 2 choices depending on how you plan to use a VLOOKUP function:

- TRUE (or you can type 1) – will search for the
**closest match**to your**lookup_value** - FALSE (or you can type 0) – will search for an
**exact match**to your**lookup_value**

Those 2 choices are manually typed by you into your VLOOKUP function.

**Note:** Always use the FALSE argument if you want to find an exact match against your **lookup_value**.

**So what exactly is the VLOOKUP function doing?**

**Step 1**– the VLOOKUP function searches for your**lookup_value**in the left-most column from your**table_array**(and only within the left-most column) starting with the 1st row and working its way down each row until it finds either:- an exact match if you used FALSE (or 0) as the
**range_lookup**argument - the closest match if you used TRUE (or 1) as the
**range_lookup**argument

- an exact match if you used FALSE (or 0) as the
**Step 2 –**the VLOOKUP function will return the value in the**col_index_num**you provided. Once it finds your**lookup_value**it will stop on that row within your**table_array**and then count as many columns to the right based on the number you provided in your**col_index_num**to return the result.

**Excel will generate an #N/A error if it doesn’t find your** **lookup_value.** If we searched for “Bill” because Billy also goes by Bill, we would get an **#N/A** error because we were searching for an exact match.

In our example, the result is 400:

- The function starts in cell B3 and says “Do you equal Billy?”. Since B3 = “Roger”, the answer is no.
- Go down to cell B4.. “do you equal Billy?”. The answer is no.
- Until the function gets to row 8 where the value does equal Billy.
- The function stops on row 8 and counts 4 columns to the right (starting with the left-most column as 1)
- Return the value 400

#### Go back to **Excel Tutorials**.