How VLOOKUP Works
Vlookup is a function to lookup up and retrieve data in a table. The "V" in VLOOKUP stands for vertical, which means the data in the table must be arranged vertically, with data in rows.
(For Horizontally structure data see HLOOKUP)
If you have a well-structured table, with information arranged vertically, and a column on the left which you can use to match a row, you can probably use VLOOKUP.
To use VLOOKUP, you supply 4 pieces of information, or "arguments":
- The value you are looking for (lookup_value)
- The range of cells that make up the table (table_array)
- The number of the column from which to retrieve a result (column_index)
- The match mode (range_lookup, TRUE = approximate, FALSE = exact
This means that VLOOKUP can only get data from columns to the right of first column in the table. When lookup values appear in the first (leftmost) column, this limitation doesn't mean much, since all other columns are already to the right. However, if the lookup column appears inside the table somewhere, you'll only be able to lookup values from columns to the right of that column. You'll also have to supply a smaller table to VLOOKUP that starts with the lookup column.
VLOOKUP USES APPROXIMATE MATCH BY DEFAULT
Exact and approximate matching in VLOOKUP is controlled by the 4th argument, called "range lookup". This name is not intuitive, so you'll just have to memorize how it works.
For exact match, use FALSE or 0. For approximate match, set range_lookup to TRUE or 1:
=VLOOKUP(value,table,column,TRUE) // approximate match
=VLOOKUP(value,table,column,FALSE) // exact match
VLOOKUP USES EXACT MATCH
To force VLOOKUP to find an exact match, make sure to set the 4 argument (range_lookup) to FALSE or zero. These two formulas are equivalent:
=VLOOKUP(value, data, column, FALSE)
=VLOOKUP(value, data, column, 0)
To use VLOOKUP in approximate match mode, either omit the 4th argument (range_lookup) or supply it as TRUE or 1. These 3 formulas are equivalent:
=VLOOKUP(value, data, column)
=VLOOKUP(value, data, column, 1)
=VLOOKUP(value, data, column, TRUE)