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