• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

Excelypedia

The Excel Encyclopedia of The Internet

  • Tutorials
  • Functions
  • Formulas
  • Templates

VLOOKUP in Excel

April 9, 2021 by Andrew Childress
Vlookups in Microsoft Excel

A VLOOKUP is a quick way to reference data from a vertical table. Let's learn how to create a VLOOKUP in Microsoft Excel.

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.

VLOOKUP in Excel

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(

How to create a VLOOKUP in Excel

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,

How to use Excel VLOOKUP

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

Make a vlookup in Excel

 

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.

E1 product

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.

Category: Excel FormulasTag: Excel, How To, Instructions, Tutorial

About Andrew Childress

Previous Post: « How to find SEM in Excel Sem in Excel
Next Post: Carriage Return in Excel Add carriage return in Excel »

Sidebar

Recent Posts

  • Format Cell Alignment in Excel
  • Default Width in Excel
  • Translate in Excel
  • Switch Windows in Excel
  • EXACT in Excel

Categories

  • Basics
  • Examples
  • Excel Formulas
  • Excel Functions
  • Excel Shortcuts
  • Excel Templates
  • Excel Tutorials
  • Uncategorized

Excelypedia

Master Excel to add a highly valuable asset to your employability portfolio. Learn how to analyze data in Excel.

  • Facebook
  • Twitter
  • Instagram
  • LinkedIn
  • YouTube

Learn

  • Functions
  • Formulas
  • Shortcuts

Legal

  • Privacy Policy
  • Cookie Policy
  • Terms & Conditions

Help

  • FAQ
  • Contact Us

Copyright © 2023 · Excelypedia · All Rights Reserved

Back to top