Excel VLOOKUP Function

HomeExcel FunctionsExcel 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

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.

Related functions

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

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 find him online, tweeting about Excel, on a running track, or sometimes hiking up a mountain.