A VLOOKUP is a quick way to reference data from a vertical table. You’ll save time because you won’t have to manually look up and retype data. Let’s learn how to create a VLOOKUP in Microsoft Excel.
How to Create a VLOOKUP in Excel
You may have heard of VLOOKUPs, since they’re so commonly used in Excel. But they can seem a little daunting. Don’t worry – you’ll soon know how to create VLOOKUPs quickly.
Imagine that you have a list of products and prices. Rather than looking up data manually, you want to simply type in the product, and have Excel return the price.
There are a few basic rules of thumb to keep in mind with VLOOKUP. First, you need to have data in a vertical table. Also, the value you’re looking up has to be in the first column in the vertical table. Otherwise, your lookup formula won’t work properly.
Consider a layout like this. Columns A and B comprise the vertical table that your VLOOKUP will work with. Cells E1 and E2 will be your input and output. In other words, you want to type the product name into cell E1. Once you do, you want cell E2 to populate with the price of the product.
Go ahead and type a product name in cell E1. Then, click on cell E2 to select it.
Begin by typing =. This is how every single Excel formula begins, and VLOOKUPs are no different. Continue with VLOOKUP(. So far, your formula reads:
=VLOOKUP(
Now, Excel needs the lookup value. This is essentially your reference point for what you want to look up in the table. Keep in mind, it’s not your output. It tells Excel what to reference in the table itself.
In this case, the lookup value is the product name. Your lookup value is in cell E1, so click to select the cell, then add a comma.
Next, you need to select the table array. This is the range of data in your vertical table. Remember, the lookup value must be in the first column of your range. Also, VLOOKUP can only look to the right. If your return value (the price in this example) is to the left, you’ll need to rearrange your table.
Here, the data lies in the range A2:B4. Click and drag to select that range, then add a comma. Your formula now reads:
=VLOOKUP(E1,A2:B4,
The third item in the formula is your column index number. This tells Excel where to find your return value. Counting from the left, your first column in the range is column 1, the second is column 2, and so forth. In this case, the value we want – price – is in column 2, so type 2,
Finally, Excel needs to know whether you want an exact (FALSE) or approximate (TRUE) match. FALSE is most common, so go ahead and type FALSE).
Your complete VLOOKUP formula should read:
=VLOOKUP(E1,A2:B4,2,FALSE)
Hit Enter on your keyboard. Watch as Excel instantly populates cell E2 with the price of the product referenced in cell E1.
If you change the product in E1, the formula will automatically pull in the new price. This is an incredibly useful way to quickly reference data. You can use VLOOKUPs across multiple worksheets, or even multiple Excel workbooks. They create value by streamlining data analysis.
Potential Uses for VLOOKUP Formulas
Consider the following uses for VLOOKUP. You can use this function to analyze all kinds of data.
- To find someone’s address by typing in their name
- To find someone’s ID number by typing in their name
- To find a state by typing in the city
- To find an email by typing in a phone number
As you can see, the possibilities for VLOOKUP are endless.
Sem in Excel
»