How to Use VLOOKUP in Excel Online

VLOOKUP is one of the most powerful features in Excel, but it's also kind of scary at first glance. So what does VLOOKUP do? Like the name implies, it allows you to quickly look up any value that lives somewhere else in your spreadsheet.

Here, we'll guide you through the process of how to use the VLOOKUP function in Excel Online. We've created a demo worksheet that you can practice with. Open the worksheet, and click Save to OneDrive to edit and practice with the data.

VLOOKUP Example

VLOOKUP helps you look up a corresponding value for a cell from within your existing database. It works like any other function or formula in Excel, like SUM or AVERAGE. The VLOOKUP formula relies on four different arguments to present a filtered result; that is, you give it four inputs, and it gives you one output.

Here's a practical example of when you might use VLOOKUP.

Let's say we run an electronics distribution company called All Tech Ltd. We use Excel for inventory management and invoicing, so all of our inventory lives in a spreadsheet, and we also create our invoices in a spreadsheet. Using VLOOKUP, we can automate the process of filling in updated prices for an item on our invoices.

We're creating an invoice for Mike, who bought a laptop and a smartwatch. Instead of copying and pasting the prices from our inventory list to our invoice, we can use the VLOOKUP function to automatically add the price of the laptop and the smartwatch to the invoice cost field.

What Is the VLOOKUP Function?

The VLOOKUP function is made up of four arguments (or parameters). These parameters will help you search for and call up the value corresponding to the cell you're filling in. You can think of them like advanced filters in Gmail search.

Lookup Value: This is your search term—it's what you're looking up. For our example, this would be the Laptop field.

Table Array: This is the range of cells you want to include in your search—everywhere you've stored your data. It can be in the same worksheet as the cell you're filling in or a different one. (To select this range, you can simply highlight the cells you want to include.)

Column Index Number: This is the number of the column that has the value you want to look up. In our example, the price list is the third column in the table, so we would write 3.

Range Lookup: This argument asks you if you want an exact match or if you're OK with the closest estimate. If you're looking for an exact match, enter FALSE. If you're OK with the closest number, enter TRUE.

How to Use VLOOKUP in Excel Online

Here's a brief overview of how to use VLOOKUP in Excel Online. We'll dive deeper into the process below.

Step 1: Create your database or table. Step 2: Create a second table where you want to look up the values from the first table. Step 3: Select the cell where you want to enter the looked-up value and enter =vlookup. Step 4: Select the VLOOKUP function, and then enter the four arguments one after the other: the lookup value, the table array, the column index number, and the range lookup. Step 5: Once your VLOOKUP function is complete, press Enter / return. The corresponding value will automatically populate.

For our in-depth tutorial, we'll be creating a small, dummy invoice for an order placed by our customer Mike. The data for the invoice will come from our price list.

Mike has placed an order for one laptop, two phones, and five speakers. We need to generate an invoice and find the total amount due for the client.

Create a database

The first step is to create a database. You can follow along below, or use our demo worksheet to practice yourself.

When you're making the database, make sure to keep the lookup value in the first column. For our example, we'll be looking up the price based on the item name, so we'll keep the item names in the first column. If you wanted to look up the price based on the item code, you'd keep that as the first column.

Create an invoice template

Next, we'll create an invoice template. We'll keep it simple here, but remember that you can create a full, branded invoice in a separate worksheet.

For our example, we are creating an invoice with the following columns: Item, Quantity, Price, and Total. We'll also include the customer name and date at the top and the grand total at the bottom.

Use the VLOOKUP function

Now that the database and the invoice template are ready, let's get to work.

Mike has placed an order for a laptop, two phones, and five speakers. So, in the first column, create three entries: one each for Laptop, Phone, and Speakers. In the quantity column, enter 1, 2, and 5, respectively.

Then select the first cell in the Price column, the one that corresponds to the Laptop item.

Type =vlookup, and from the dropdown, click on the VLOOKUP option. Excel will activate the VLOOKUP function and it will show all the arguments inside the bracket.

Next, we'll be adding the data in the VLOOKUP function.

lookup_value: We're looking for the price of the laptop, so we'll select the cell corresponding to the Laptop text. (Note: Select the Laptop cell from the invoice template, not the price list). In the demo sheet, this is cell E6.

Once you've done that, type a comma to switch to the next argument.

table_array: Now, go to the database table (the price list) and select all the rows and columns that contain the data. (Note: You do not need to select the column titles).

Again, type a comma to move on to the next argument.

col_index_num: This is the column index number. We're looking for the price of an item, and in our database, the price is the third column from the left, so we'll enter 3 and then type a comma again.

range_lookup: Because we're looking for an exact match, we'll enter FALSE as our last input.

Once you've done that, close out the parentheses to finish the VLOOKUP function, and press Enter / return. Instantly, the cell will populate with the corresponding value based on your inputs. In our example, you'll see 1500, which is the price of the Laptop.

Complete the invoice

Now that you've successfully used the VLOOKUP function, you can build the rest of the invoice. You only need to write the VLOOKUP function once for the entire invoice: Select the VLOOKUP cell, and drag it down for two cells; instantly, the price of the phone and the speakers will be populated.

Then, you can enter a formula to multiply the quantity with the price and get the total for each item. To do this, select the first cell below the Total column heading and type =F6*G6.

Once you have the total, select the cell and drag it down two cells using the anchor at the bottom-right edge of the cell.

Now we just need the grand total of all three items. Select the cell corresponding to Grand Total, type in =SUM(H6:H8), and press Enter / return. You now have the total amount due from Mike, which is $4300.

The Scalability of VLOOKUP

In this example, we've generated a full invoice for multiple items using just one VLOOKUP function. And this process will only get faster with time. In fact, the real beauty of using a VLOOKUP function will only be visible when you create the next invoice.

Let's say you have another customer, John, who has placed an order for two monitors, one smartwatch, and three keyboards.

All you have to do is go to the Invoice table and change the three fields in the Item column and the corresponding quantities. Instantly, the prices of the items will update as will the total and the grand total.

The more items and invoices you have, the more time VLOOKUP will save you.

Now that we've covered the basics of VLOOKUP, spend some time exploring how it can fit with your business and workflow using our demo sheet. And if you run into problems, here's how to fix the most common errors in Excel.