How to Setup the VLOOKUP Function in Excel
Use the VLOOKUP function in MS Excel to search out the value of item you need from a data table. By defining a table range (a grouping of cells in a worksheet you want to search), you can retrieve data that matches the first column of your table range and return the result to a defined cell.
For example, if you had a list of five product numbers and their associated colors in the cell range D1:E5, you can look up the color of a single product number by searching the number in the first column of the range and returning the color from the second column of the range.
The VLOOKUP statement is: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]).
Other People Are Reading
Instructions
-
Determine the Value You Want to Look Up, Where to Put the Result and the Data Table
-
1
Put the value of the item you want to look up in a cell. For example, enter "Product 1" in cell A1. This is the value you will look up and match to a value in the first column of your table array.
-
2
Determine where you want to place the result of the VLOOKUP. For example, you can look up the value "Product 1" in cell A1 and place the result of the VLOOKUP (the color of the product) in cell A2. In this manner, you will have the name of the product next to the color of the product.
-
-
3
Select the location for your data set. For example: set the data set will be stored in D1:E5.
Create a Statement to Retrieve the Desired Value
-
4
Set the "lookup_value" from the VLOOKUP statement. For example: the lookup_value of "Product 1" is cell A1.
-
5
Set the "table_array" from the VLOOKUP statement. For example: the table_array is the location of the data set where you will find the color of your products in cells D1:E5. Column D (the first column) will state all of the different products (Product 1, Product 2, Product 3, Product 4, Product 5) and Column E (the second column) will state all of the different colors of each product (Blue, Green, White, Red, Yellow).
-
6
Set the "col_index_num" from the VLOOKUP statement. For example: the col_index_num is the number of the column in your table_array that contains the value you want to look up. Your product number is in column 1 and your color is in column 2. The column you want to retrieve from is column 2; therefore, your col_index_num is 2.
-
7
Set the "[range_lookup]" from the VLOOKUP statement. This is where you determine if you want an exact match or the closest match to the value you state. For example, if you want an exact match from Product 1 in column 1 to return the exact result of blue from column 2, you would set the [range_lookup] as FALSE, otherwise state it as TRUE.
-
8
Complete the VLOOKUP statement to retrieve the desired value in the cell you want to place the result.
Example: in cell A2, input =VLOOKUP(A1, D1:E5,2,FALSE) to complete your VLOOKUP. A1 will contain "Product 1" and A2 will "Blue" as the result.
-
1
Tips & Warnings
Arrange your data table before attempting to create the VLOOKUP statement.
Always put your lookup value in column 1 and sort the table array by column 1 to retrieve the correct answer.
References
Resources
- Photo Credit Stockbyte/Stockbyte/Getty Images
