I. Intro
The VLOOKUP function searches through a column for a specified value to find information corresponding to that value in the same row.
II. About the function
- Formula: =VLOOKUP (search_key, range, index, [is_sorted])
- Arguments:
- search_key: The value you want to look up in a column.
- range: The range to look up. The column containing the search value must be the first column of the range.
- Note: If the search value is in number format, sorting in ascending order is recommended to avoid errors in the results.
- index: The index of the column containing the result you want. The first column in the range is "1", the next column is "2", and so on.
- is_sorted: Optional. Indicates whether to use an approximate match. Approximate match is used by default if left blank or if you set it to 1 or true. Set it to False or 0 for exact match.
III. Steps
Use the function
For example, let's say a teacher wants to calculate the grades of their students.
- Formula: =VLOOKUP(H2, A2: F11, 4, FALSE)
- H2 (10001) is the first student number in the column.
- A2:F11 contains all the course results.
- 4 refers to the fourth column (Course C).
- FALSE means we want an exact match.
- Select a cell and click Formula in the toolbar, then select Search > VLOOKUP. You can also directly enter =VLOOKUP in a cell.
- Enter the arguments in the cell and press Enter.
- Drag the + symbol in the lower-right corner of the cell to apply the formula to more cells as necessary.
250px|700px|reset
Reference data from other spreadsheets in VLOOKUP
Select a cell and enter =VLOOKUP. Enter the search key, click Reference data from another sheet > Select Spreadsheet, and select the search range.
250px|700px|reset
250px|700px|reset
Delete the function
Select the cell with the VLOOKUP function and press Delete.
IV. Use cases
Human resources
HR may need to screen hundreds of applicants to find the right candidate. Looking through such a long list to find their contact information can be tiresome. Here, VLOOKUP can help.
- Formula used below: =VLOOKUP(5,A2:F9,5,0).
- About the parameters: Let's say you want to find the email of candidate 5, Lily. In this instance, you can enter 5 or H2 as the search value and set the search range as A2:F9. The email addresses are in the fifth column, so enter 5 as the index. Finally, enter FALSE or 0 to find an exact match.
250px|700px|reset
Inventory management
Keeping a detailed list of product characteristics and records, such as material, size, color, and price, is crucial for inventory management. VLOOKUP makes searching through such a complex list easier.
- Formula used below: =VLOOKUP(E2,A2:D7,3,0)
- About the parameters: Let's say you want to find the inventory level of Product C. Enter Product C in E2.
- Enter E2 for the search value and A2:D7 for the range. Inventory is the third column in the range, so the index is 3. Finally, enter 0 or FALSE to find an exact match.
250px|700px|reset