# Excel VLOOKUP Function

Home ➜ Excel Functions ➜ **Excel VLOOKUP Function (Example + Sample File)**

**What is EXCEL VLOOKUP FUNCTION**

The Excel VLOOKUP Function is listed under Microsoft Excel's Lookup Functions category. **It lookups for a value in the first column of a table and returns the value from the same row of the matched value using the index number.** In simple words, it performs a vertical lookup.

**How to use it**

To learn how to use the VLOOKUP function in Excel, you need to understand its syntax and arguments:

**Syntax**

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

**Arguments**

**lookup_value:**A value that you want to search in a column. You can refer to a cell that has the lookup value or you can directly enter that value into the function.**table_array:**A range of cells, a named range from which you want to look up the value.**col_index_num:**A number represents the column number from which you want to retrieve the value.**range_lookup:**Use false or 0 to make an exact match and true or 1 for an appropriate match. The default is True.

**Notes**

- If VLOOKUP can not find the value you are looking for, it will return an #N/A.
- VLOOKUP is only able to give you the value which is on the right side of the lookup value. If you want to look upon the right side, you can use INDEX and MATCH for that.
- If you are using an exact match then it will only match the value which is first in the column.
- You can also use wildcard characters with VLOOKUP.
- You can use TRUE or 1 if you want an appropriate match and FALSE or 0 for an exact match.
- If you are using an appropriate match (True):
- It will return the next smallest value from the list if there is no exact match.

- If the value which you are looking for is smaller than the smallest value in the list, VLOOKUP will return #N/A.
- If there is an exact value exists which you are looking for, it will give you that exact value.
- Make sure you have sorted the list in ascending order.

**Example**

To master the VLOOKUP function we need to try it out in an example, so make sure to check out the below one:

### 1. Using VLOOKUP for Categories

In the below example, we have a list of students with marks they have scored, and in the remarks column, we want to a grade according to their marks.

In the above marks list, we want to add remarks as per the below category range.

In this, we have two options to use.

**FIRST** is to create a nesting formula with IF which is a little bit time-consuming, and the **SECOND** option is to create a formula with VLOOKUP with an appropriate match.

And, the formula will be:

=VLOOKUP(B2,$E$2:$G$5,3,TRUE)

**...how it works**

I am using the “MIN MARKS” column to match the lookup value and I am getting value in return from the “Remarks” column.

I have already mentioned that when you use TRUE and there is no exact match lookup value then it will return the next smallest value from the lookup value.

For example, when we are looking for a value 77 from the category table, 65 is the next smallest value after 77.

That is why we got “Good” in remarks.

### 2. Handling Errors in VLOOKUP Function

One of the most common problems which come when you are using VLOOKUP is that you’ll get #N/A whenever there is no match is found by it.

But the solution to this problem is simple and easy. Let me show with an easy example.

In the below example, we have a list of names and their age and in cell E6, we are using the VLOOKUP function to look up a name from the list.

Whenever I type a name that is not on the list I am getting #N/A.

But what I want here is to show a meaningful message instead of the error.

The formula will be:

=IFNA(VLOOKUP(D6,Sheet3!$A$1:$B$14,2,0),"Not Found”)

**...how it works**

IFNA can test a value for #N/A and if there is an error you can specify a value instead of the error.

**Hey Listen,**

You can some Excel Basics Tutorials from this page and here we have Top 100 Excel Tricks and Tips for you get better at Excel in no time.

## Related functions

This tutorial is the part of our Excel Functions with Examples (Function Guide) and below are some of the related functions:

- EXCEL ADDRESS Function
- EXCEL AREAS Function
- EXCEL CHOOSE Function
- EXCEL COLUMN Function
- EXCEL COLUMNS Function
- EXCEL FORMULATEXT Function
- EXCEL HLOOKUP Function
- EXCEL HYPERLINK Function
- EXCEL INDEX Function
- EXCEL INDIRECT Function
- EXCEL LOOKUP Function
- EXCEL MATCH Function
- EXCEL OFFSET Function
- EXCEL ROW Function
- EXCEL ROWS Function
- EXCEL TRANSPOSE Function

**About the Author**

**Puneet Gogia**

Puneet is using Excel since his college days. He helped thousands of people to understand the power of the spreadsheets and learn Microsoft Excel. You can ﬁnd him online, tweeting about Excel, on a running track, or sometimes hiking up a mountain.

COPYRIGHT © 2020 • EXCEL CHAMPS