Do you want to check for a particular value in your Excel spreadsheet and look through specific information about it at the same time? Running a VLOOKUP saves a lot of effort in that case. It's one of the best ways to run a vertical query in Microsoft Excel.
Let's take a look at how to use VLOOKUP in an Excel spreadsheet.
What Is VLOOKUP and How Does It Work?
Using a VLOOKUP to check your spreadsheet is just like searching for an item on Google or making a database query to return specific items if they're there.
In essence, VLOOKUP works by looking through a set of items in a column and giving you a result based on the information you choose to see about that column. For instance, you can decide to look for the price of an item by searching its model name.
Although VLOOKUP is limited to the vertical orientation, it's an essential tool that makes other Excel tasks easier. These tasks could include calculating the standard deviation in Excel or even working out weighted averages in Excel.
VLOOKUP supports a flexible approximate matching search or a more stringent exact matching query feature. In essence, the exact match looks for the precise value of data in an Excel column. But the approximate matching feature gives you results based on only some matching words or characters in your search term.
How to Write a VLOOKUP Formula
The VLOOKUP formula typically requires four arguments and looks like this:
=VLOOKUP(lookup_value, table_array, column_index_number, range_lookup)
The lookup value is the target item you want to check out, and this must fall on the first column of your spreadsheet. The table array is the parent data you wish to search.
When performing a VLOOKUP, you also want to return your result based on a particular value. The position of the column containing that value is the column index number. However, the range lookup takes in TRUE for an approximate matching or FALSE for an exact match.
How to Use VLOOKUP
Now that you know the architecture of a VLOOKUP formula. Let's see how it works in practice with the following Excel VLOOKUP examples and steps.
Assume you have a spreadsheet containing the following columns: Name of products, reviews, and price. You then want Excel to return the number of reviews for a particular product.
Using this VLOOKUP example, let's see how to do that with the following steps.
- Type the name of the product you want to lookup in any cell below or beside your parent data (ensure that you avoid typos).
- Next, select an empty cell next to the new cell you just created that now contains the product you want to lookup.
- In that new cell, type =VLOOKUP. Excel typically recommends a completion. When you see that, hit Tab on your keyboard to continue with the VLOOKUP formula.
Then highlight the cell you just created that contains the target product name. Doing this appends its cell position to the VLOOKUP formula. Type a comma after the highlighted cell in the VLOOKUP formula. For example, if your target item or product is on cell E6, the formula becomes =VLOOKUP(E6, for now.
- Next, highlight the entire parent data (table array) to drop it in your formula as well. If your table array is between A2 and C9, your formula becomes =VLOOKUP(E6,A2:C9, with a comma at the end.
- Type the column number of the value you want to view the target item on. Columns are typically associated with letters in Excel, but in this case you need to count its number (A is 1, B is 2, and so on). Type a comma after it.
- Type FALSE if you want an exact match of the item you entered. Otherwise, type TRUE to view it by the most available approximate match for it.
- Your final formula should look like this: =VLOOKUP(E6,A2:C9,2,FALSE), depending on the parameters you entered.
Then hit Enter to get your result.
Note: If you have to assign column names to the cells bearing the results, ensure that you use paraphrased words different from those in the parent table.
How to Do a VLOOKUP for Multiple Items
You can also look up multiple values in a column with VLOOKUP. This can come in handy when you need to perform operations like plotting Excel graphs or charts on the resulting data. Let's see how to do this with the following steps:
- Type all the items you want to look up in separate cells (the selected product names in this case).
- Select the cell next to the first product and type =VLOOKUP(.
Next, highlight the first item (lookup value) from the ones you just typed to add it to the VLOOKUP formula. Type a comma after that.
- Highlight the entire parent data (table array) to add its range to the formula as well. Then ensure that you hit the F4 key on your keyboard to make the result absolute, so it doesn't change when you copy the formula. Separate this from the next argument by a comma. Your formula should change to something like this: =VLOOKUP(E6,$A$2:$C$13).
- Type in the column number of the column you want to look up data in. For example, if the number of reviews is on the second column, type 2. Then put a comma after that.
Then type FALSE to get exact matches.
- Your final formula should look something like this: =VLOOKUP(E6,$A$2:$C$13,2,FALSE), depending on your chosen parameters.
- Close the parenthesis and hit Enter.
Once the result appears against the first lookup value, drag the results cell down to fill the formula in for all the other products as well.
How to Create a Relationship Between Excel Sheets With VLOOKUP
You can also relate tables in different sheets using VLOOKUP. This is helpful when you have a parent sheet (otherwise known as the Excel lookup table) and its subset on another sheet, and you want to draw information from the parent spreadsheet into the subset sheet.
However, for this VLOOKUP example, assume that a subset of the example data we used earlier is on another Excel sheet. That subset then only has some selected products and their prices, but not the number of reviews.
The aim is to get the reviews for these selected products from the parent spreadsheet and paste them against each product in the subset sheet. Let's see how you can achieve this with the following steps:
- Create a new Excel column in the subset spreadsheet, ensure that you use a paraphrased column name. For example, you can name it "Number of reviews" instead of "Reviews".
- Next, place your cursor in the first cell (against the first product) under the new column and type =VLOOKUP(.
Select the first product from the subset data to append it to the VLOOKUP formula and put a comma after it.
- Go back to the parent spreadsheet and highlight the whole data table. Then ensure that you hit key F4 to make the result applicable to other selected products. You'll now see the lookup formula in the formula bar. Type a comma after pressing F4.
- While still in the parent sheet, look at the formula bar and type the column number for the reviews column. Then separate it from the next argument by a comma.
In the same parent sheet, type FALSE since you want an exact match of each product in this case.
- Close the parenthesis and hit Enter. Excel then takes you back to the subset data and displays the review result for the first product.
Next, drag down the result for the first product to see results for the other products in the subset sheet.
Take Control of Your Query With Excel Lookups
VLOOKUP is a great way to query data faster in Microsoft Excel. Although VLOOKUP queries vertically across the column and has a few other limitations, Microsoft keeps updating its Excel lookup features to expand the lookup functionalities.
For instance, HLOOKUP has a horizontal orientation. But XLOOKUP is a new feature that can look both vertically and horizontally in a spreadsheet. Most of these lookup features follow the same process, with only a few differences. Using any of them for any specific lookup purpose is a smart way to get a hold of your Excel queries.