Today I will be showing how you can use the **VLOOKUP** function of Excel.

VLOOKUP Function of Excel (Quick View)

**Table of Contents**hide

**Download Practice Workbook**

**Excel VLOOKUP Function (Syntax & Argument)**

**Summary**

The **VLOOKUP** function looks for a given value in the leftmost column of a given table, and then returns a value in the same row from a specified column.

It is available from Excel 2003.

**Syntax**

The **Syntax** of the **VLOOKUP** function is:

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

**Argument**

Argument |
Required or Optional |
Value |

lookup_value | Required | The value which it looks for in the leftmost column of the given table. Can be a single value or an array of values. |

table_array | Required | The table in which it looks for the lookup_value in the leftmost column. |

col_index_num | Required | The number of the column in the table from which a value is to be returned. |

[range_lookup] |
Optional |
Tells whether an exact or partial match of the lookup_value is required. 0 for an exact match, 1 for a partial match. Default is 1 (partial match). |

**Note:**

- The
**lookup_value**can be a single value or an array of values. If you enter an array of values, the function will look for each of the values in the leftmost column and return the same row’s values from the specified column. - The function will look for an approximate match if the
**[range_lookup]**argument is set to**1**. In that case, it will always look for the lower nearest value of the**lookup_value**, not the upper nearest one. - If the
**col_index_number**is a fraction in place of an integer, Excel itself will convert it into the lower integer. But it will raise**#VALUE!**error if**the col_index_number**is zero or negative.

**Return Value**

Returns the value of the same row from the specified column of the given table, where the value in the leftmost column matches the **lookup_value**.

**Explanation**

**1. Non-Array Formula**

When the **lookup_value** is a single value, it searches for the value in the leftmost column of the given **table_array**.

If it finds one, then it moves to the specified number of columns right given as **col_index_num** in the same row.

After moving to the specified number of columns right, it returns the value from the destination cell.

In the following figure, the formula is:

`=VLOOKUP("Shane Lee",B5:D9,3,0)`

It searches for “**Shane Lee”** in the leftmost column **B** of the **table_array** **B5:D9**.

Then it finds one in cell **B7**. Then it moves to column **3 (col_index_num)** of the table, in the same row. That is cell **D7**.

And then it returns the value from that cell, in this case, it is the salary of Shane Lee, $22000.00

**2. Array Formula**

Again, when the **lookup_value** is an array of values in place of a single value, the function searches for each of the** lookup_values** in the leftmost column of the **table_array** one by one.

In the following figure, the formula is:

`=VLOOKUP(F5:F6,B5:D9,3,0)`

It first searches for **F5 **(Shane Lee) in the table and returns his salary, $22000.

Then it searches for** F6** (Alfred Moyes) in the table and returns his salary, $22000.

**Note:** You have to press **Ctrl + Shift + Enter** to enter an **Array Formula** unless you are in **Office 365**.

**3. In Case of Approximate Match**

In the case of an approximate match (when the** [range_lookup]** argument is zero), the function always looks for the lowest nearest match.

In the following figure, the formula is:

`=VLOOKUP(168,B4:D8,2,1)`

Here, the **[range_lookup]** argument is 1, so it searches for an approximate match.

The** lookup_value** is 168.

It settles in 135, the lower nearest value to 168, and returns the corresponding Employee Name, Natalia Austin.

But the upper nearest value 169 is far closer, still, it does not go there.

**Excel VLOOKUP Function: 2 Examples**

**1. Finding out the Holder of Maximum Value from a Data Set**

Let us have a look at this data set.

We have the employee IDs, employee names and their salaries of a company named Saturn Group in columns **B, C** and **D** respectively.

Now we shall try to find out the holder of the maximum ID using the **VLOOKUP** function.

The formula will be:

`=VLOOKUP(MAX(B4:B20),B4:D20,2,0)`

See, we have found the Employee with the maximum ID, Angela Mills with an ID of 372.

**Explanation of the Formula**

**MAX(B4:B20)**returns the maximum value between B4 to B20 (Employee IDs). In this case, it is 372. So the formula becomes:**VLOOKUP(372,B4:D20,2,0)**- Then it searches for an exact match of the
**lookup_value**372 in the leftmost column**B**of the**table_array****B4:D20**. It finds one in cell**B19**. - Finally, it moves to column
**2**(**col_index_num**) of the same row, to cell**C19.**And returns what it gets there. Here it is Angela Mills, the employee with the maximum ID.

Formula |
Output |
Explanation |

=VLOOKUP(MAX(B4:B20),B4:D20,2,0) | Angela Mills | First determines the maximum ID in the range B4 to B20. It is 372. Then searches for it in the leftmost column B of the table_array B4:D20. After finding one, it moves the 2nd column of the same row of the table. There it finds the name “Angela Mills”. |

**2. Finding out the Holders of Top n Values from a Data Set**

We try to determine the holders of any top n values from a data set using the **VLOOKUP** function.

Let’s find out the employees with the top 5 IDs from the same data set.

The formula will be:

`=VLOOKUP(LARGE(B4:B20,ROW(A1:A5)),B4:D20,2,0)`

**Array Formula**. So press

**Ctrl + Shift + Enter**unless you are in

**Office 365**.]

See, we have got the employees with the top 5 IDs.

**Explanation of the Formula**

**ROW(A1:A5)**returns an array of numbers from 1 to 5,**{1,2,3,4,5}**. See**ROW**function here.**LARGE(B4:B20,ROW(A1:A5))**becomes**LARGE(B4:B20,{1,2,3,4,5}).**It then returns the top 5 IDs from the cells**B4**to**B20.**These are:**{372,317,309,293,267}**.**VLOOKUP(LARGE(B4:B20,ROW(A1:A5)),B4:D20,2,0)**then becomes**VLOOKUP({372,317,309,293,267},B4:D20,2,0).**It is a combination of total 5 formulas.

**VLOOKUP(372,B4:D20,2,0)****VLOOKUP(317,B4:D20,2,0)****VLOOKUP(309,,B4:D20,2,0)****VLOOKUP(293,B4:D20,2,0)****VLOOKUP(267,B4:D20,2,0)**

**VLOOKUP(372,B4:D20,2,0)**searches for an exact match of**372**in the leftmost column**B**of the table**B4:D20**. After finding one, it moves to the 2nd column of the same row of the table, then returns the name of the employee. In this case, Angela Mills.- The rest of the formulas do the same. Thus we get the list of the employees with the top 5 salaries.

Formula |
Output |
Explanation |

=VLOOKUP(LARGE(B4:B20,ROW(A1:A5)),B4:D20,2,0) | Angela Mills
Ricky Ben Mathew Rilee Usman Malik Benjamin Stokes |
Searches for an exact match of each of the top 5 IDs in the leftmost column B of the table B4:D20, then moves to column 2 and returns the names with the top 5 maximum IDs. |

**Limitations of Excel VLOOKUP Function**

- The first limitation of the
**VLOOKUP**function is that you can not use it when the**lookup_value**is in a column right to the required value.

For example, in example 1, you can not use the **VLOOKUP** function if you are asked to find out the employee with the maximum salary. Because the salary is in a column right to the required value, employee name.

You can use the **XLOOKUP** or **INDEX-MATCH** function of Excel to come out of this limitation.

- If you have the
**lookup_value**more than once, the**VLOOKUP**function will only provide you with information about the first one it gets.

For example, in the data set of example 1, there are two employees named Mathew Rilee. Now if we want to get the salary of Mathew Rilee, we will only get the salary of the first one, $28000.

You can solve this problem using the **FILTER** function of Excel.

- In the case of an approximate match, the
**VLOOKUP**function always settles for the lower nearest value of the**lookup_value**, even when the upper nearest value is closer (See the 3rd point of the**Explanation**Section). - The
**VLOOKUP**function does not update automatically when you insert a new column. To get rid of this problem, you can use the**INDEX-MATCH**function of Excel.

**Common Errors with Excel VLOOKUP Function**

Error |
When They Show |

#N/A! | Shows when it does not find a match of the lookup_value in the leftmost column. |

#VALUE! | Shows when an argument of the function is of the wrong data type. For example, when the col_index_number is negative, or a text or the [range_lookup] argument is not 0 or 1. |

**Conclusion**

In this way, you can use the **VLOOKUP** function of Excel to extract out a value or an array of values matching another value from any data set. Do you have any other questions? Feel free to inform us.