Use Excel’s VLOOKUP function to find anything on a table

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:

1. Open Excel.
2. Create a first column with a list of items that will act as unique identifiers (required).

   

   Source: Windows Central

3. Create one or more columns, on the right side, with the different values for each of the items from the first column.

   

   Source: Windows Central
   Quick tip: We’re creating a basic table, but the concept applies for any type of table configured in a vertical orientation.

4. Select an empty cell and specify the name of the item you’re trying to find an answer. For example, Orange.

   

   Source: Windows Central

5. Select an empty cell to store the formula and the returned value.
6. Type the following syntax to create a VLOOKUP formula and press Enter:

   =VLOOKUP()

   

   Source: Windows Central

7. Type the following arguments inside the parenthesis to write a function and press Enter:

   =VLOOKUP(lookup_value,table_array,col_index_num,range_lookkup)

   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)

   

   Source: Windows Central

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:

1. Open Excel.
2. Create a first column with a list of items that will act as unique identifiers (required).

   

   Source: Windows Central

3. Create one or more columns, on the right side, with the different values for each of the items from the first column.

   

   Source: Windows Central

4. Select an empty cell and specify the name of the item you’re trying to find an answer. For example, Orange.

   

   Source: Windows Central

5. Select an empty cell to store the formula and the returned value.
6. Click the Formulas tab.
7. Click the Lookup and Reference drop-down menu, and select the VLOOKUP option.

   

   Source: Windows Central

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

   

   Source: Windows Central

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

   

   Source: Windows Central

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

    

    Source: Windows Central

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

    

    Source: Windows Central

12. 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:

We may earn a commission for purchases using our links. Learn more.