Use Excel’s VLOOKUP function to find anything on a table
Do you work with a large spreadsheet or always look for the same data in a table? Then you should use the VLOOKUP function in Excel to make your life easier, and here’s how.
In Microsoft Excel, VLOOKUP (vertical lookup) is a useful search function that allows you to find any information within a table range by looking at the first column of data and returning a related value from another column on the right.
Although in a small table, you may be able to find information rather quickly, it’s not the case when working with an extensive spreadsheet database with hundreds of rows and columns, as you can spend a long time analyzing and finding the information you need, and this is when VLOOKUP in Excel comes in handy.
VLOOKUP works by performing a vertical search for a value in the first column of the spreadsheet (that acts as the unique identifier), and then it returns a result from the matching row. In other words, the Excel function works like a drink menu, where you start with a piece of information you know, such as the name of the drink, and then you look to the right to get the information you don’t know, for example, the price per bottle size.
In this Windows 10 guide, we’ll walk you through the steps to correctly write a basic VLOOKUP function with the desktop version of Microsoft Excel, whether you use an Office 365 subscription, Office 2019, Office 2016, or earlier version.
How to write VLOOKUP function in Excel
To write a VLOOKUP function manually in Excel, use these steps:
- Open Excel.
Create a first column with a list of items that will act as unique identifiers (required).
Create one or more columns, on the right side, with the different values for each of the items from the first column.
Quick tip: We’re creating a basic table, but the concept applies for any type of table configured in a vertical orientation.
Select an empty cell and specify the name of the item you’re trying to find an answer. For example, Orange.
- Select an empty cell to store the formula and the returned value.
Type the following syntax to create a VLOOKUP formula and press Enter:
=VLOOKUP()
Type the following arguments inside the parenthesis to write a function and press Enter:
=VLOOKUP(lookup_value,table_array,col_index_num,range_lookkup)- lookup_value: defines the cell that contains the product identifier from the first column.
- table_array: defines the range of data where you want to perform a search. Usually, this is the selection of the entire table.
- col_index_num: defines the column number (from left to right) that the function will look to find a value.
- range_lookkup: you can use two options: “false” for exact match or “true” for an approximate match. Usually, you’ll want to use the false option.
In the command, make sure to update the variables within the parenthesis with the information you want to query. Also, remember to use a comma to separate each value in the function. You don’t need a space between each comma.
This example returns the price for the 20oz bottle of orange juice:
=VLOOKUP(C10,B4:E8,4,FALSE)
- lookup_value: defines the cell that contains the product identifier from the first column.
Once you complete the steps, the lookup feature will return the value for the item you specified.
If you’re trying to find data for another item, simply change the name of the value. For example, if you want to see the price for the “20oz” bottle of Kiwi juice, then replace “Orange” for “Kiwi” in the “lookup_value” cell to update the result.
How to build VLOOKUP function in Excel
To use the function arguments tool to write a VLOOKUP formula in Microsoft Excel, use these steps:
- Open Excel.
Create a first column with a list of items that will act as unique identifiers (required).
Create one or more columns, on the right side, with the different values for each of the items from the first column.
Select an empty cell and specify the name of the item you’re trying to find an answer. For example, Orange.
- Select an empty cell to store the formula and the returned value.
- Click the Formulas tab.
Click the Lookup and Reference drop-down menu, and select the VLOOKUP option.
In the Lookup_value field, specify the cell that contains the reference of the item you’re trying to find an answer. For example, C10.
In the Table_array field, select the section of the table function that will perform the search. Usually, you’d want select the entire table.
In the Col_index_num field, specify the column number that contains the answer. For example, 4, which is the column that stores the information you want to retreive. In this case, it’s the price for the 20oz bottles of juice.
In the Range_lookup field, specify whether VLOOKUP should look for a specific match (false) or an approximate match (true). Usually, you’d use the false option.
- Click the OK button.
After you complete the steps, the VLOOKUP feature will show a result based on the parameters you have defined in the tool.
If you want to find out the details about another item with different information from the first column, repeat steps No. 4 through 12.
We’re focusing this guide in the desktop version of Microsoft Excel for Windows 10, but these instructions should also work in Office for macOS. You can also use VLOOKUP on the web version of Excel, but the function arguments tools aren’t available, which means that you’ll need to write the formula manually with the above steps.
More Windows 10 resources
For more helpful articles, coverage, and answers to common questions about Windows 10, visit the following resources:
