Excel VLOOKUP Function is one of the most useful excel function. You can use it when you want to look up for a value in a column. And, once you find that value in the column you can able to get the value from the right of the value that you have found.
In the above table, if you want to find 5 from column 1. You can do that by using VLOOKUP & once you find it you can able to get the values from the same row in the right direction. In above example, you can get 13 & 21 if you looking for the value 5.
In simple words, Excel VLOOKUP Function can find a value from the vertical direction (Column) & once you find the value you can get values from the horizontal direction(Row).
- lookup_value A value which 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 f the value.
- col_index_num A number represent the column number from which you want to retrieve the value. In above example, If you want to retrieve 13, column index number will be 2 & if you want 21 then column index number will be 3.
- range_lookup Use False or 0 to make an exact match & True or 1 for an appropriate match. The default is True.
In above example, I have used “Leg Guard” as a lookup value from the “Products” column. As I need value from the “Quantity” column I have used 3 as a column index because “Quantity” column is the 3rd column starting from the “Products” column.
And, in the end, I have used 0 in range lookup to get an exact match.
More Information on Excel VLOOKUP Function
- If VLOOKUP Function can not find the value you are looking for, it will return an #N/A.
- VLOOKUP only able to give you the value which is on the right side of the lookup value. If you want to look up on the right side, you can use INDEX & MATCH Functions for that.
- If you are using exact match then it will only match to the value which is first in the column. In below example, if you are looking for the amount of product-1 then it will return 1632, not 1843 because 1632 is upper on the list than 1843.
- You can also use Wildcard Characters with VLOOKUP.
- You can use TRUE or 1 if you want an appropriate match & FALSE or 0 for an exact match.
- If you are using appropriate match (True).
- It will return the next smallest value from the for the list if there is not exact matct.
- If the value which you are looking for is smallest than the smallest value in the list, VLOOKUP will return #N/A. For example, If you are looking for value 4 and the smallest value in the column is 5 then it will return #N/A.
- If there is 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.
Excel VLOOKUP Function is most widely used Excel Function which has so many different applications. Here I have explained you about the most common usage of VLOOKUP which you can easily understand and apply in your work.
1. Using VLOOKUP for Categories
In below example, I have a list of students with marks they have scored. And, in the remarks column, I want to a grade according to their marks.
In above marks list, I want to add remarks as per below category range. In this, I have two option to use. One is to create a nesting formula with IF Function 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 Does this Formula Works?
I am using “MIN MARKS” column to match the lookup value & I am getting value in return from “Remarks” column.
I have already mentioned that when you use True & there is no exact match of lookup value then it will return the next smallest value from the lookup value.
For example, when I am looking for the value 77 from the category table, 65 is the next smallest value after 77. That is why I 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 not match is found by it.
But the solution to this problem is simple & easy. Let me show with an easy example.
In below example, I have a list of names & their age. In cell E6, I am using VLOOKUP function to look up for a name from the list. Whenever I type a name which 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 Does this Formula Works?
Excel IFNA Function can test a value for #N/A & if there is an error you can specify a value instead of the error. By using this formula you can show a meaningful message to the user.
To learn more about Excel VLOOKUP Function you can check Microsoft’s Help Section. And, if you have a unique idea to use it, I would love to hear from you.