

One of the most popular formulas in Excel is the VLOOKUP formula. Many lookup approaches are based on the VLOOKUP formula. Mastering it is crucial for any of the following chapters and methods. Unfortunately, VLOOKUP is not as easy to use as a SUM or COUNT. In this article, you learn how to use VLOOKUP, what to keep in mind and some more advices about VLOOKUP.
Contents
VLOOKUP searches for a value in the leftmost column and return a value from the same row
The description text in Excel summarizes it quite well:
Looks for a value in the leftmost column of a table, and then returns a value in the same row from a column you specify.
That means you got a table with data and you are searching for an item in the leftmost column. Once you’ve found the item, VLOOKUP will look into the same row and return a value in another column on the right hand side.
At this point it’s already important to note one of the major restrictions: VLOOKUP can only return values on the right-hand side of the search column. There is no exception to this rule. We will later come back to this restriction.
Structure of the VLOOKUP formula in Excel
The VLOOKUP has four parts, of which three need to be defined. The fourth part is usually just “FALSÉ”, so we can omit this part for now. Let’s have a look at VLOOKUP step by step.
Example for VLOOKUP
Let’s fill the definitions above with a simplified example. You want to search for “ghi” and get the number from column 3 returned.
The complete formula looks like this: =VLOOKUP(“ghi”,A:C,3,FALSE)
Excel too slow? Speed it up. Get the book now!
Tired of waiting for Excel? Use the 30 best methods described in this book to speed up Excel calculations!
Learn more or get it on Amazon!
Example for the VLOOKUP formula
The picture on the right-hand side shows a simple usage of the VLOOKUP. The image shows a simple usage of the VLOOKUP. It searches for the value from cell B8 (“Audi A3) within the table above and returns the price from column C.
The complete formula is =VLOOKUP(B8,B3:C4,2,FALSE)
HLOOKUP works the same way as the VLOOKUP with the only difference, that the ranges are transposed: Instead of in a column, the HLOOKUP searches in a row (HLOOKUP = horizontal lookup; VLOOKUP = vertical lookup).
Oftentimes, the return value is located on the left side of your search value. Unfortunately, Excel doesn’t allow such case: You must use a workaround. There are basically four options.
There are basically three major (alleged) error messages, which actually tell you quite well how to improve your formula. You should check your formula, if you got one of the following return values:
If the above hints don’t help or there is another error message (such as “#DIV/0!”), try searching for the value manually. Maybe there is already an error in your data.
Hold on a second. Was this information helpful so far?
Why don't you subscribe to our monthly, free Excel newsletter?
Subscribe now! Your welcome gift: Our big 45 pages keyboard shortcuts package. In case the sign-up form above doesn't work, please use this page. Sorry for the inconvenience.
Of course, I'm also on other networks:
Twitter: Follow @professorexcel
Facebook:
VLOOKUP is powerful and probably one of the most used formulas besides sum and count. To improve the usage, it’s recommended trying the following tips and tricks:
'Professor Excel Tools': Add more than 60 amazing features to Excel!
Professor's Error Helper for Excel: Solve any error in Excel.
Necessary cookies are absolutely essential for the website to function properly. This category only includes cookies that ensures basic functionalities and security features of the website. These cookies do not store any personal information.
Any cookies that may not be particularly necessary for the website to function and is used specifically to collect user personal data via analytics, ads, other embedded contents are termed as non-necessary cookies. It is mandatory to procure user consent prior to running these cookies on your website.
Comments 4
Jogaji Thakor
Exelent work to provide us amazing information
Sudipta Ray
I need the advanced vlookup formula. By which I can find out multiple result.
Henrik Schiffner
What should your result be? The number of how often your search value is within your data? Or a sum?
Ramit basu
v look up is not work in attendence sheet