This wikiHow teaches you how to look up related information in a cell in Microsoft Excel using the VLOOKUP formula. The VLOOKUP formula is useful for finding information such as employee salaries or budgets for a given day. You can use the VLOOKUP formula on both Windows and Mac versions of Excel.
Step
Step 1. Open the Excel document
Double-click the Excel document containing the data you want to search using the VLOOKUP function.
If the document has not been created, open Excel, click Blank workbook (Windows only), and enter data in column form.
Step 2. Make sure the data is in the correct format
VLOOKUP only works with data arranged in columns (i.e. vertically), meaning that the data most likely has a header in the top row, but not the leftmost column.
If the data is arranged in rows, you cannot use VLOOKUP to look up the data
Step 3. Understand each aspect of the VLOOKUP formula
The VLOOKUP formula consists of four parts, each of which refers to the information in your spreadsheet:
- Lookup Value - The cell whose data you want to search. For example, you want to search for data in cells F3, is located on the third row of the spreadsheet.
- Table Arrays - The table ranges from the top left cell to the bottom right cell (excluding column heads). For example, the table starts from A2, down until A20, extends to the column F; your table range is from A2 until F20.
- Column Index Number - The index number of the column whose data you want to view. The "index number" column refers to the sequence number. For example, in your spreadsheet there is a column A, B, and C; index number of A is 1, B is 2, and C is 3. The index number starts from 1 in the leftmost column, so if the data starts from column F, the index number is 1.
- Range Lookup - Usually we want to find exactly the same data for VLOOKUP results; this can be done by typing FALSE in this section. To get the approximate result, type TRUE.
Step 4. Select a blank cell
Click a cell to display the results of the VLOOKUP formula.
Step 5. Enter the VLOOKUP formula
Type =VLOOKUP(to start the VLOOKUP formula. Then the formula starts with an opening parenthesis and ends with a closing parenthesis.
Step 6. Enter the value to search
Specify the cell whose value you want to search for, then enter the cell name into the VLOOKUP formula followed by a comma.
- For example, you want to find data for cells A12, type A12, into the formula.
- Separate each section in the formula with a comma, but don't add spaces.
Step 7. Enter the table range
Specify the top left cell where the data is stored and type its name into the formula, type a colon (:), specify the bottom right cell of the data and add it to the formula, then type a comma.
For example, your data table starts from cell A2 until C20, type A2:C20, into the VLOOKUP formula.
Step 8. Enter the column index number
Calculate the index number of the column containing the value you want to display with the VLOOKUP formula, then type it into the formula and end with a comma.
For example, if the table uses columns A, B, and C and you want to display data in column C, tick 3,.
Step 9. Type FALSE) to end the formula
This means that VLOOKUP will look for values that exactly match the specified column with the selected cell. The formula will look like this:
=VLOOKUP(A12, A2:C20, 3, FALSE)
Step 10. Press Enter
This step will run the formula and display the results in the selected cells.
Tips
- A common application of VLOOKUP on inventory lists is to enter the item name in the "lookup value" section and use the item price column as the "column index number" value.
- To prevent cell values from changing in the VLOOKUP formula when adding or adjusting cells in the table, add a '$' in front of each letter and number in the cell name. For example, A12 written as $A$12, and A2:C20 Becomes $A$2:$C$20.
