Microsoft Excel comes with a library of functions that enable you to easily search, sort, and find relationships between values in your data tables. The VLOOKUP() function allows you to search a column for a value and returns a corresponding value from another column in that row. Use VLOOKUP() when you want to compare data stored in columns.
Syntax and Parameters
The VLOOKUP() function takes four parameters in the following order: lookup_value, table_array, col_index_num and range_lookup. The "lookup_value" specifies the value to search for within the first column of the "table_array" and can be either a value or a reference. Appropriately, "table_array" denotes the range of data to search. "Col_index_num" indicates which column the function will use to return a value and must be a numerical value greater than 1 and less than or equal to the number of columns in "table_array" to prevent #VALUE! And #REF! errors. Finally, "range_lookup" is a binary field used to specify whether the "lookup_value" should be an exact or approximate match. "Range_lookup" is the only optional parameter in the function.
When using VLOOKUP() to find data in your spreadsheets, you can search for an exact match or an approximate match by using the "range_lookup" parameter. If "range_lookup" is either TRUE or omitted, VLOOKUP() will return an approximate match for "lookup_value" if an exact match is not found. VLOOKUP() returns the closest value that is less than "lookup_value" when searching for an approximate match. When "range_lookup" is specified as FALSE, VLOOKUP() will only return an exact match of "lookup_value." If an exact match cannot be located, VLOOKUP() will return a #N/A! error.
Assume the following table is specified at the "table_array" of VLOOKUP(), with the first row being column identifiers:
Entering "=VLOOKUP(2,A1:D3,2,TRUE)" would return "P." VLOOKUP() searches for the value "2" in column A, and returns the value of column B, which is the second column in the array. Changing the "col_index_number" from "2" to "3" would return "Q" instead of "P."
Entering "=VLOOKUP(4,A1:D3,3,FALSE)" would return "F." The "lookup_value" of "4" cannot be located, but "range_lookup" is FALSE, so VLOOKUP() locates the closest approximate match of "3.5" and returns the information stored in the third column.
Once you understand how to use VLOOKUP() to locate data in your spreadsheet, you can use it in advanced equations and formulas to perform calculations that depend on data stored in the same row as your search value. For example, you could use VLOOKUP() in an equation to calculate approximate taxes by searching for an income level in a table of staggered tax brackets and returning the appropriate tax percentage. In an employee database, you could use multiple instances of VLOOKUP() to return the first, middle and last name of the employee associated with a specific identification number.