Syntax: =vlookup(lookup_value, table_array, col_index_number, [range_lookup])
Vlookup stands for ‘verticle lookup’ and can be used to return a value from a data table or array. The function is a left to right lookup, so the data columns must be ordered in such a way to accommodate this limitation.
The function can be broken down into four parts:
- lookup_value. What do you want to look up?
- table_array. Where do you want to look it up?
- col_index_number. In the table you are looking for the data, which column do you want to return? The first column is considered 1.
- [range_lookup]. The brackets signify an optional argument. This value can be set to TRUE or FALSE. FALSE returns an exact match. Omitting this argument defaults the formula to the TRUE value. In an ordered list, TRUE finds the closest value without going over. TRUE and FALSE can also be entered numerically as a 1 or 0, respectively.
Objective: Look up the date 6/30/2013 and return the Net Profit number for the month ending 6/30/2013
- What do I want to look up? I want to look up 6/30/2013 which is located in cell B16.
- Where do I want to look it up? I want to find 6/30/2013 in the table located at the range B21:E33 (note the value you are looking up must be in the left most column that is specified in the lookup table).
- What column do I want the formula to return? The Net Profit number is in column 4 of the table specified above.
- Exact match or not? I am looking for an exact match so I’ll set this to FALSE.
Result: the formula returned a value of 2,625. Looking in the data table I can confirm that this is the correct value.
Click here to open the excel spreadsheet shown below: vlookup_example