If you frequently need to look up data in a table, you can run a search by using the VLOOKUP function in Excel. For example, if you want to look up the price of a product, employee’s salary, or party affiliation in a list of voters, you can run a VLOOKUP for the data point you’re looking for.
There are 4 things you’ll need to know to build the VLOOKUP:
- The cell where you’re enter the item you’re searching by, called the lookup value. For example, if you’re looking up an employee’s salary, this is the cell where you’ll enter the employee’s name.
- The “range” of the table you’re searching in, which is the columns and rows where it sits in your Excel spreadsheet. The lookup value needs to match something in the first column of the range for the VLOOKUP to work.
- Within the range, what number column holds the data you want returned by the VLOOKUP, e.g. the salary in our example below. If the table starts in column A, then A is 1, B is 2, etc.
- Whether you want an exact match, or if an approximate match will suffice.
Follow these steps to set up the VLOOKUP:
Select the cell where the VLOOKUP formula will be calculated.
Click “Formulas” in the Ribbon.
Click “Lookup & Reference.”
Select VLOOKUP from the dropdown menu.
Enter the cell where you’ll be entering the value you’re searching by. In this example, H1 is the lookup value, since that’s where we’ll enter the ID of the employee whose salary we’re looking for. When the VLOOKUP is finished, Excel will enter a salary in H2 when we put an employee ID in H1.
Enter the range that Excel will search in.
Enter the column number where the VLOOKUP will find the data you’re looking for. This is 3 in our example because salaries are listed in column C.
Specify whether you want an exact match. Enter FALSE for an exact match or TRUE for an approximate match.
Click OK at the bottom of the popup window.
Enter the value for which you want information. In this case, we’re looking for Sally’s salary, so we put in her employee ID. (Remember, the lookup value needs to match something in the first column of the table you’re looking in.) Excel returns her salary of $65,000.
You can also run a VLOOKUP using a table in another sheet of the Excel file you’re in. Instructions for that are here. You can even lookup data in another Excel file, using instructions here. For example if you want to look up an employee’s start date, home address, and phone number, and each one is in a different place, you can have a summary sheet with several VLOOKUPs, each referencing another sheet or Excel file.