If the VLOOKUP function in Excel ever intimidated you, watch this video to understand its structure and learn how to do vlookup yourself.
In this video we will explore the Vlookup function. Vertical lookup is what you need when you have interrelated information in different Excel tables. Here is a quick example: you have a list of addresses and cities, and you can quickly pull the corresponding ZIP codes from the reference table.
Let's look at the structure of the formula to get a better understanding of what it needs to work. We can specify four arguments:
Let's take a quick look at the formula to read it: find this city in the first column of this range and return the corresponding Zip Code from the third column there when you find an exact match.
Now that you know the basics, let's try a different example and consider some more details.
Here we have the book IDs and authors and we need to look up the book title in another sheet. There is one prerequisite that will make your work a lot easier: select the lookup values starting with your key column and enter a name for the range into this box.
Now you are ready to enter the formula:
=VLOOKUP(A2, Books, 4, FALSE)
Since we need to get book titles for all the IDs we have here, begin with a reference to the cell with the key value: A2. Now you could select the range in your lookup table and make it absolute by adding dollar signs, or simply enter the name you created for it. Add a comma, and count the number of the column with the book names. Remember to enter False and close parentheses.
Copy the formula down to get the results for all the cells below. Notice how you get N/A error for those key records that are missing in the lookup table? You can replace it with anything, from a blank to "Not Found" text by wrapping the VLOOKUP formula into IFERROR function and adding the text you want to see in its place:
=IFERROR(VLOOKUP(A2, Books, 4, FALSE), "")
Now it says if there is an error as a result of the formula, then keep the cell empty.
Let's consider one more example for those cases when you are not looking for an exact match. It can be especially convenient when you work with numbers.
Say, I want to find a book with the rating as close to 5 as possible.
=VLOOKUP(5,E2:F999,2,TRUE)
Add my lookup rating "5", a reference to the range, the number of the column with the books, and enter TRUE for the last argument or skip it altogether.
Now the formula is looking for the next largest value.
If you are short of time, you can skip the formulas and get vlookup help from our Merge Tables Wizard add-in for Excel. Once you install it, you'll find it under Ablebits Data tab. Let me quickly go over the simple steps to follow:
It's a great assistant if you often update tables, and you can get it with a discount if you use the coupon code Preview.
I hope you have a better understanding of how VLOOKUP works in Excel. If you have any questions, please share them as comments, we'll do our best to help you.
6 responses to "Excel VLOOKUP explained - how to video"
lot of thanks for your worthy effort. i have learn many more from your tutorial since last few month.
I Question from my side -
is there any way to filter any protected excel sheet? how does a pivot table of protected sheet be refreshed?
Hello Imran,
Thank you very much for your feedback, happy to help!
You can filter a protected sheet only if you apply Auto Filter before protecting it and select the checkbox to "Use Auto Filter" under "Allow all users of the worksheet to" when locking.
The only way you can refresh a pivot table is unprotect the sheet, or do it automatically with the help of a VBA macro, please see the following forum thread in Microsoft Community:
http://answers.microsoft.com/en-us/msoffice/forum/msoffice_excel-mso_winother/how-to-refresh-pivot-table-on-protected-sheet/11396e2f-0639-481f-bbb2-71c5166694ed
Hi Irina,
I had always been struggled whenever worked with VLOOKUP but i am sure after reading this, no more struggle here after
thank you so much for very worthful blog
Hi Manu,
Thank you very much for your feedback, it is great to know our posts are helpful!
Hello Irina,
Is there any option in vlookup where I could replace multiple column value by a single column value.
Thanks..
Avinash
Hello Avinash,
Could you please send a sample spreadsheet to support@ablebits.com and describe the expected results in more detail? Please include a link to this blog post.
I'll do my best to assist you.