Requirement Summary
In this guide, we will explore how to use the VLOOKUP function in Excel to search for a value in the first column of a table and return a value in the same row from another column.
Code
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
Code Explanation
The VLOOKUP function in Excel has four main arguments:
- lookup_value: This is the value to search for in the first column of the table.
- table_array: This is the range of cells that contains the data. The first column of the range is searched for the lookup value.
- col_index_num: This is the column number in the table_array from which the matching value should be returned.
- range_lookup: This is an optional argument. If TRUE or omitted, it looks for the closest match. If FALSE, it looks for an exact match.
For example, let’s say we have a table with student names in column A and their corresponding scores in column B. To find the score of a specific student, we can use the VLOOKUP function like this:
=VLOOKUP("John", A1:B10, 2, FALSE)
In this example:
- “John” is the lookup value.
- A1:B10 is the table array.
- 2 is the column index number for the score column.
- FALSE specifies an exact match.
This formula will search for “John” in column A and return his score from column B.
VLOOKUP is a powerful function in Excel that can save you time and effort when looking up values in tables. Mastering it can greatly enhance your data analysis capabilities.
