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

Spread the love
  • Yum

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.

Get ready to stream UFC 247 with an ESPN+ subscription

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

    Excel column listing

    Excel column listing

    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.

    Excel table information for VLOOKUP

    Excel table information for VLOOKUP

    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.

    Excel lookup value selection

    Excel lookup value selection

    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()

    Excel VLOOKUP function

    Excel VLOOKUP function

    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)

    Excel VLOOKUP function

    Excel VLOOKUP function

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

    Excel column listing

    Excel column listing

    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.

    Excel table information for VLOOKUP

    Excel table information for VLOOKUP

    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.

    Excel lookup value selection

    Excel lookup value selection

    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.

    Excel formulas menu, VLOOKUP option

    Excel formulas menu, 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.

    VLOOKUP tool lookup value field

    VLOOKUP tool lookup value field

    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.

    VLOOKUP tool table array field

    VLOOKUP tool table array field

    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.

    VLOOKUP tool column index number

    VLOOKUP tool column index number

    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.

    VLOOKUP tool range lookup exact match field

    VLOOKUP tool range lookup exact match field

    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.

Leave a Reply

Your email address will not be published. Required fields are marked *