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:
- 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.
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:
...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:
...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.
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 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.