The VLOOKUP function in Excel is your go-to tool for finding data in vertically organized tables. Think of it like this: you have a list of items, and you need to quickly pull out specific information about them. VLOOKUP makes that easy by searching down a column and returning data from a column to the right.
Let’s say you’ve got a big inventory spreadsheet and need to find the price for a specific item – VLOOKUP can handle it, even if the price list is on a different sheet. It might seem a bit complicated at first, but once you get the hang of it, you’ll be using it all the time. I’ll show you how it works.
How VLOOKUP works
Before we jump into examples, let’s look at the nuts and bolts of the VLOOKUP function:
The VLOOKUP function uses this format:
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
Here’s what each part means:
- lookup_value: This is the value you’re searching for. VLOOKUP looks for this in the first column of your data table.
- table_array: This is the range of cells containing your data. It’s the table where VLOOKUP will search. It can be on the same sheet, another sheet, or even another workbook.
- col_index_num: This is the column number (starting from the left) within your table_array that has the info you want to pull out.
- [range_lookup]: This is optional, and it’s a switch for choosing between an exact or approximate match. Use
FALSEfor exact matches, orTRUEfor approximate matches.
Using VLOOKUP
Okay, let’s put VLOOKUP into action with some examples.
Simple VLOOKUP
First, you need a table to use with VLOOKUP. Here’s a basic example of an inventory list:
Now, let’s set up a separate area where we can pull data from this inventory.
Imagine you want to find the phone number for “Ena”. Go to the cell where you want that phone number to show up, and put in this formula:
=VLOOKUP(B13, A2:E10, 5, FALSE)
Here’s what that formula is saying:
B13is where the lookup value “Ena” is found.A2:E10is the range of your main table.5means we want the data from the fifth column (phone numbers).FALSEtells excel that we want an exact match
You can easily select the table range with your mouse when entering the formula, and the range will be added automatically.
Keep in mind: VLOOKUP only looks to the right of the first column, where the lookup_value is located. In this example, “Ena” must be in the first column within the range A2:E10.
VLOOKUP’s Search Direction
The VLOOKUP function can only search in the first column of your table and bring back data from columns that are to the right. It cannot search left from that first column.
VLOOKUP for Exact Matches
When you use VLOOKUP, you choose if you want an exact or approximate match with that [range_lookup] argument. If you want an exact match, set that argument to FALSE or 0.
If you can’t find the value exactly, you’ll get a #N/A error. For example, if we search for “Japan” in a list that doesn’t have “Japan”, we will get an error.
You can use either FALSE or 0 to get the same exact match result, both work fine.
VLOOKUP for Approximate Matches
Sometimes you need an approximate match rather than an exact one. For that, set [range_lookup] to TRUE or don’t include it at all, since TRUE is the default.
If you are assigning grades based on test score ranges, that’s a perfect situation for using approximate match. If a test score isn’t an exact match, VLOOKUP will go for the next largest score that’s still smaller than your score.
For example, an 89 score will return the grade for the 80 score.
VLOOKUP with Duplicates
If your first column has duplicate values, VLOOKUP will only return the first match it finds. So, if you’re looking for “Mia” and there are two Mias, you’ll get the info for the first Mia on the list.
Using Wildcards in VLOOKUP
You can use wildcard characters for partial matches with VLOOKUP, which is really useful when you’re not sure of the complete lookup value. Just add an asterisk * into your lookup value and use & to connect it with cell reference.
=VLOOKUP($B$13&"*", $A$2:$E$10, 3, FALSE)
If B13 has “Vin”, this will search for all the values starting with “Vin”.
Using VLOOKUP with MATCH for Flexible Lookups
You can make your lookups more flexible by combining VLOOKUP with other functions like MATCH. This lets you search based on both row and column conditions.
=VLOOKUP(B13, A2:E10, MATCH(A14, A1:E1, 0), 0)
In this case, we look for the first name “Mayra” and then use
MATCH to get the correct column number for the column “City”.
VLOOKUP from another Worksheet
VLOOKUP can easily get data from a different sheet in the same workbook. To reference another sheet, put the sheet name in front of your range, followed by an exclamation mark !.
=VLOOKUP(A2, ItemPrices!$A$2:$C$8, 2, FALSE)
Here, we are looking for value in cell A2 in the current sheet and then search for the matching value in the range $A$2:$C$8 in the ItemPrices sheet.
The lookup table on the ItemPrices sheet might look like this:
VLOOKUP then fetches the prices from the
ItemPrices sheet.
VLOOKUP from another Workbook
You can even get data from another workbook with VLOOKUP. Include the workbook name in square brackets, followed by the sheet name, and then the range.
=VLOOKUP(A2, [Item.xls]ItemPrices!$A$2:$B$8, 2, FALSE)
When setting up this type of formula, its best to have both workbooks open. Then select the
table_array from the other workbook to add the correct path automatically.
If you close the source workbook, Excel will add the complete file path in the formula, but the VLOOKUP function will still work correctly.
Using Excel’s Ribbon to access VLOOKUP
Instead of typing the formula, you can use the Excel ribbon. First, select the cell in which you want the formula to be applied. Next, navigate to the “Formulas” tab, and click on “Lookup & Reference”. Finally, select VLOOKUP from the drop down.
In the “Function Arguments” window that pops up, fill in the arguments with the correct values and ranges. Click OK to use the function.
For example, when searching for the name “Sherill” to get the corresponding state, your dialog box should look similar to the one below.
