A scary 7-letter word that most Excel users like you would've heard about. It's well-known that VLOOKUP is a powerful function in Microsoft Excel, but it sounds complicated as well.
In this 5-minute Excel guide, we are going to briefly talk about:
- When to use VLOOKUP
- What is VLOOKUP
- How to use VLOOKUP
When to Use VLOOKUP
Consider these common scenarios:
- Go to Booking.com and search for the rates of a particular hotel.
- Keying in "Billy" into my mobile to find Billy's mobile number.
- Typing "Gardens by the Bay" in Google to get the address of this beautiful tourist attraction in Singapore.
Each of these scenarios can be thought of as a 3-step W-W-W process:
- What information do you have?
- Where to search for your information?
- What information do you want?
What is VLOOKUP
VLOOKUP is a lookup function in Microsoft Excel. (And if you're not aware, an Excel function is simply a formula predefined in Excel for users like you and me to use, such as SUM and AVERAGE.)
VLOOKUP asks you for something you have, goes somewhere to search, and gives you the related information you want.
Mastering VLOOKUP can be the single Excel function that has the greatest impact to transform your life.
And in the corporate world, knowing VLOOKUP is usually the separating line between the intermediate Excel users and the general masses.
How to Use VLOOKUP
Since I'm a coffee addict, how about let's meet up in a cafe. And while we're here, you decided to treat me to a cup of aromatic coffee.
Looking that the menu, I decided that I wanted an small Americano. How much will that be?
Manually, I need to:
- Decide that I want to order an Americano.
- The place to search for information is the menu. I looked for "Americano" in the first column, and once I located the row, I decided that I wanted a small cup - listed in the second column.
- Looking over at the second column of the menu, I found that a small one costs $4.00.
VLOOKUP in Excel:
Step 1: Data preparation. We put my order in a new cell, Cell G2. In Cell G3, we prepare a box for Excel to find the price for us.
Step 2: Highlighting Cell G3, we click on the fx button to call out the function helper located left of the formula bar.
Step 3: Type vlookup in the search and press Go, then select VLOOKUP from Select a function and press OK.
Step 4: In our 3-step W-W-W process above, we ask ourselves:
- What information do we have? We have the name of the coffee in Cell G2, and so we enter G2 in Lookup_value.
- Where to search for our information? The information is contained in the coffee prices and so we highlight the entire table A1:E5 for the Table_array. Press the F5 key once to lock the table (Excel will automatically insert the $ signs).
- What information do we want? The price of small size coffee is listed in the second column of the coffee prices (column 1 being the names of coffee sold). So we enter 2 in Col_index_num to get information of the second column.
- In the last field, under Range_lookup, just put the word FALSE for an exact match. 99% of the time in VLOOKUP, we will be doing exact match.
- Check that the Formula result is working. In our case, the Formula result is 4 which means that a small Americano costs $4.00.
Now our formula should be dynamic. Change the Cell G2 to other coffee orders and you should get the cost of it in small size in Cell G3.
Practices for You
- Try and see what happens if you search for Espresso in Cell G2.
- Try to change the VLOOKUP formula to look up the prices for medium, large and extra large coffee!
As this is a short guide, I've only included the most important elements of VLOOKUP.
- Make sure that what information we have is located in the first column of the Table_array for where to search for our information. We can often select a subsection of a table to achieve this. If all else fails, check out INDEX-MATCH, a more powerful version of VLOOKUP.
- If Excel cannot find what you want e.g. when you search for Espresso in Cell G2, you will see #N/A which represents Not Available. We can use this feature of VLOOKUP to check if something is in a list.
- If you are sure the information you gave is in the table of information, but you get #N/A, first check that you have locked down the table with $ signs using F4.
- If it still happens and the information you gave is a number, it may be that there is a format mismatch. Check out this article from Dave Bruns.
- For further reading regarding VLOOKUP exact vs approximate match, check out this Jeff Lenning's article.
Share this article with your friends and colleagues if you think this will help them! And signup for the latest Excel articles sent to your email.