VLOOKUP, Excel Functions
In this article I will explain the function VLOOKUP. In short VLOOKUP searches a table of data for a specific Key. Upon finding the Key it returns the requested value associated with that Key.
Table: A set of records, where each record has a unique Key.
Record: A Key plus one or more Values.
Key: A unique identifier. This could be a number, a string, a date, … as long as it is unique to that record.
Value: Different properties of a record.
Example: As an example take a look at the table below:
Table: The table is from cell A2 to cell C8.
Record: Each row is a record
Key: In this table there are different drinks. Each drink has a unique name, therefore the drink name is a key. By looking up the name of the drink you can find its price and preparation time in the table. On the other hand the price and p preparation times are not necessarily unique. For example you can’t look up the price $4.00 and find a unique drink (there are 2 drinks associated with that price).
Note: The column with the Key values must be the first column.
Value: The price and preparation time are the value fields.
VLOOKUP: The VLOOKUP function has the following format:
=VLOOKUP(Lookup_value, Table_array, Col_index_num, Range_lookup)
Lookup_value: The Key value to find.
Table_array: The table with the data.
Col_index_num: The column with the Value to return after finding the matching Key.
Range_lookup: An optional parameter. If this parameter is set to false, VLOOKUP will search to find an exact match. If it is set to True, or not passed at all, VLOOKUP will find a close match.
Example: Lets say we are trying to create an invoice or bill for the drink orders:
We need a function in the Unit Price column ( Column C) to update the price based on the value selected in the Item column (Column A). So for the example above we need a function to find the Key coffee from the table in sheet2 and return its price ($5.00) to cell C9:
This can be achieved by using the VLOOKUP function with the following parameters:
=VLOOKUP("Coffee", Sheet2!A2:C8, 2, False)
“Coffee” is the Key we are trying to find the price for:
“Sheet2!A2:C8” is the location of the database. The number 2 is the column index of the value to return after finding the matching key (in this case “coffee”). Below you can see the index of the values in the database table. Had we chosen 3 instead of 2, the preparation time would have been printed in the cell instead of the price:
Since we are looking for an exact match, the lasts parameter is set to False. In conclusion this is the formula we need to print in cell C9:
=VLOOKUP(A9, Sheet2!A$2:C$8, 2, FALSE)
Result:
By inputting the following function in cell D9, we will get the cost for the 2 coffees:
=C9*B9
You can also download the complete Invoice file here.
See also:
If you need assistance with your code, or you are looking to hire a VBA programmer feel free to contact me. Also please visit my website www.software-solutions-online.com