Microsoft Excel offers a variety of tools for sorting and organizing data. One of the more useful, and often less understood tools is the
VLOOKUP function. This function allows you to search through a specified set of data and return a value based on another predefined value. For example, if you have two columns in your spreadsheet with one column being an item and another being the price, you could configure the function to return the price of the specific item.
The example below should help you better visualize this concept. To get started, review the data in the spreadsheet below:
This spreadsheet contains a list or items along with a price and category. In a real word scenario you would likely be dealing with a significantly larger list but for the purposes of this example we will keep things simple.
Now we will use the
VLOOKUP function to return the price of the Sleeping Bag. First you will want to select an empty cell to enter the formula. In this example I will use cell E1 to display the results.
VLOOKUP function requires you to specify 4 arguments in order for it to return the necessary result. To begin calling the function click on the empty cell and then click on the formula bar (highlighted in previous screenshot) and enter the following:
This will begin the function call and prompt you for the first argument. The first argument is the name of the item you are searching for. In this example we are searching for Sleeping Bag. Since the data type of the item is a character type it will need to be put in double quotes within the formula, as seen here:
All arguments within a function are separated by a comma.
The next argument specifies the full range of cells that contain all the data you are searching. Since we are searching for the price of the Sleeping Bag item, the range here will be A2 to B11. Use a colon to designate the range of cells as shown below:
The third argument will be the column index number. To better understand this just know that the first column is index number 1, the second is index number 2, the third is 3…etc. The column index number should correspond to the column that contains the data you want returned by the function. Since we are wanting to see the price assigned to the specified item, we will set the column index number to 2.
One thing to remember is that
VLOOKUP will always search the first column in the specified range. It may be necessary to rearrange the columns in the spreadsheet before you perform a
VLOOKUP so that the first column in the range corresponds to the first argument specified in the function. In this example, the first column just happens to be column A.
The fourth and final argument is used to determine whether the function should look for an exact or approximate match. This argument is a logical value of either
FALSE. A value of
TRUE assumes the fist column is sorted either alphabetically or numerically. It then finds the closest match.
FALSE will search for an exact match. The fourth argument is actually optional and will default to TRUE if not employed. I find it best to specify the fourth argument regardless of the initial sorting on the spreadsheet.
End the function with a closed parentheses and press ENTER.
If you entered the formula correctly you should see the price assigned to Sleeping Bag displayed in the cell.
To demonstrate another example, let’s say you want to find the Equipment Type of a particular item. The rules when dealing with more than two columns are the same. An example of this is displayed below:
Here I am searching for the Equipment Type of the Sleeping Bag item. The only difference here is that I am specifying a wider range of cells to include Equipment Type as well as changing the column index number to return the value from the Equipment Type column.
VLOOKUP is a powerful tool for analyzing data within massive spreadsheets and can save you countless hours of sorting and reorganizing if used effectively. Once you become familiar with the syntax of the function and the required structure of the spreadsheet you should be able to easily leverage this tool within Excel.