Buzz
Today, Mytour will guide you through the process of using the VLOOKUP formula in Microsoft Excel to locate corresponding data from a specific cell. The VLOOKUP formula is invaluable for finding information such as employee salaries or budgets for specific days. You can use VLOOKUP on both Windows and Mac versions of Excel.
Steps
Open the Excel document. Double-click the Excel document containing the data where you want to apply the VLOOKUP function.
- If you haven't created a document yet, open Excel, click Blank workbook (only available on Windows), and input your data in columns.
Ensure your data is properly formatted. VLOOKUP only works with data organized in columns (e.g., vertical sections), meaning your data should generally have headings at the top row, not in the far-left column.
- If your data is organized by rows, VLOOKUP will not be able to find the values.
We need to understand each component of the VLOOKUP formula. The VLOOKUP formula consists of four parts, each representing a different aspect of the data in the spreadsheet:
- Lookup value - The cell that contains the data you wish to search for. For instance, if you're searching for data in cell F3, the lookup value will be in row three of the spreadsheet.
- Table array - The entire range of data from the top-left cell to the bottom-right cell (excluding headers). For example, if the data spans from A2 to A20 and extends to column F, the table array will range from cell A2 to F20.
- Column index number - The column number that contains the value you want to retrieve. The column index reflects the order of columns; for example, in a spreadsheet with columns A, B, and C, the index for column A is 1, for B is 2, and for C is 3. The index starts from 1 and counts from the leftmost column. If your data starts in column F, its column index will be 1.
- Range lookup - Typically, you want an exact match for the VLOOKUP result, so enter FALSE for this value. If you're looking for an approximation, you can enter TRUE.
Select a blank cell. Click on the cell where you want the result of the VLOOKUP formula to appear.
Enter the VLOOKUP formula. Type =VLOOKUP( to begin the formula. The rest of the formula will be placed between the opening and closing parentheses.
Enter the lookup value. Find the cell where the lookup value is written, then enter the cell's name into the VLOOKUP formula followed by a comma.
- For example: if the lookup value is in cell A12, you will enter A12, in the formula.
- You need to separate each part of the formula with commas, but no spaces are required.
Enter the table array value. Identify the top-left cell containing the data, input the cell name into the formula, then add a colon (:) and find the bottom-right cell in the data range, adding it to the formula followed by a comma.
- For example, if the data range extends from A2 to C20, you will enter A2:C20, into the VLOOKUP formula.
Enter the column index number. Identify the column index for the value you wish to retrieve via VLOOKUP, then include it in the formula, followed by a comma.
- For example: if the table includes columns A, B, and C, and the data you need is in column C, you should enter 3, here.
Enter FALSE) to close the formula. This ensures that VLOOKUP searches for the exact match within the specified column for the item you selected. The formula will then look like:
- =VLOOKUP(A12,A2:C20,3,FALSE)
Press ↵ Enter. The lookup process will begin, and the result will appear in the cell where the formula is entered.
Tip
- A common use of VLOOKUP in inventory tables is to input the item name in the "lookup value" field and use the item’s cost column as the "column index number".
- To prevent a cell reference from changing when adding or modifying cells in the VLOOKUP table, add a '$' before the column and row numbers. For instance: A12 becomes $A$12, and A2:C20 becomes $A$2:$C$20.
Warning
- Double-check the column index number before adding it to the VLOOKUP formula, as the index will change depending on the starting column of the data, not the column's position in the table.
