Vlookup
2023-05-20The VLOOKUP (Vertical Lookup) is a function in Microsoft Excel that is used to link data across different tables, based on a common value. The 'V' in VLOOKUP stands for vertical which means the function scans vertically down the columns of a spreadsheet. Here's a simple explanation of the VLOOKUP function and its syntax:
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
The function arguments are:
- lookup_value: This is the value that you want to search for. This value must exist in the first column of the range of cells in the specified table_array.
- table_array: The range of cells that contain your lookup value, in the left-most column, and your desired data, anywhere else in the range.
- col_index_num: The column number of the data that you want returned from the table_array.
- range_lookup (optional): A logical value (TRUE or FALSE only) that specifies if the VLOOKUP should find an approximate or an exact match to the lookup_value. If TRUE (or omitted), an approximate match is returned i.e., if an exact match is not found, the next largest value that is less than lookup_value is returned. If FALSE, VLOOKUP will find an exact match. If an exact match is not found, the function returns an error.
Here is a simple example of a VLOOKUP function:
Let's assume we have a table with Employee IDs in the first column (column A) and Employee Names in the second column (column B). We're trying to find the name of the employee with the ID #7 (listed in cell 'E123').
We would use the following formula: =VLOOKUP("E123", A2:B100, 2, FALSE)
In this example:
- "E123" is the lookup_value, the value that's common in the working table and the lookup table.
- A2:B100 is the table_array, the range of cells that contain the lookup_value and the desired data.
- 2 is the col_index_num, the number in the table_array that has our desired data. In this case, we want to return a value from the second column, which contains the employee names.
- FALSE is the range_lookup value, which means we're looking for an exact match to our lookup_value.
So, this function is telling Excel to look for "E123" in cells A2 through B100, and when it finds "E123", it should return the corresponding value from the second column of that range. The "FALSE" indicates that we want an exact match, so if "E123" is not found, the function will return an error.