The VLOOKUP (Vertical Lookup) function in Microsoft Excel is a powerful tool in Microsoft Excel; which can be used to instantly search (Lookup) a specific value in the first column of a table and bring up its corresponding value from another column in the same row.
For example, if there is a phone directory containing “Phone numbers” in one column and “Names” of its owners in the next column, the VLOOKUP function can almost instantly bring up a Name that matches any given Phone Number.
In this example, the VLOOKUP function goes through the phone number column to find the phone number and then moves to the right to return its corresponding value (the name of owner).
Syntax of VLOOKUP Formula
The VLOOKUP function has the following Syntax:
=VLOOKUP (Lookup_value , table_array, col_index_num , [range_lookup] )
- Lookup_value: This is the item that you want to lookup or search.
- Table_Array: This is where the data that you want to search (lookup) is located.
- Col_Index_Num: Column number in the table_array from which to return the value.
- [Range_Lookup]: This is optional value and it can either be True or False. If True (or omitted), VLOOKUP finds an approximate or nearest match. If False, it finds the exact match.
How to Use VLOOKUP Function in Microsoft Excel
In order to understand the use of VLOOKUP function, let us try to find the price of a Chromebook from a Sales Data worksheet that has the Names of items in Column A and their corresponding prices in column E.
1. Type the Name of item that you want to lookup in Cell A13 – In our case, the item that we want to lookup is Chromebook.
2. Move to Cell B13 > Type =VLOOKUP and Excel will automatically provide you with the Syntax to follow.
3. Going by the Syntax, select Cell A13 as the Lookup_value – This is where you typed the Name of item that you want to Lookup (Chromebook).
4. Next, select Cells A1:F10 as the Table_array – This is where the data that you want to scan is located.
5. The next part is Column_Index_Num – Type “5” to specify the location (fifth-column from left to right); where the prices of items are located.
6. The last part is [Range_Lookup]: As explained above, simply pick False to indicate that you want to find the exact match for the price of Chromebook.
7. Close the bracket and hit the Enter Key on the keyboard of your computer.
Once you hit the Enter Key, VLOOKUP function will search for (Chromebook) in Column A and bring up the price of Chromebook from Column E.
Limitations of VLOOKUP Function
While the VLOOKUP Function is powerful, it has certain limitations that you need to be aware of.
- Duplicate Entries: VLOOKUP returns data for the very first match that it finds and ignores duplicate entries (If any).
- Breaks Easily: If you insert a new column in the data sheet, it changes the column numbers; which breaks the formula.
- Right-Looking: VLOOKUP retrieves data from columns to the right, it cannot look backward.