Lookup

Microsoft Excel lookup is a Microsoft Excel function that searches for values in a column or row of a spreadsheet list or table. The V in VLOOKUP stands for vertical (column) while the H in HLOOKUP is for horizontal (row). When performing a Vlookup, each match returns the corresponding value on the same row in the next column. For example, if you were looking for a number in a phone book you would read down the list of names until you found the name you were looking for and then move your finger to the right (in the same row) to find the associated phone number.

A perfect real world example of how a VLOOKUP could be used in Microsoft Excel, is a spreadsheet containing a list of thousands of part numbers, each with their own description and price. Using a VLOOKUP you could type in a part number and immediately get the description or price of that part number. Lets use the example below table as an example for working with a VLOOKUP.

  A B C D E F G
1 Part Number Description Price Availability   Part Number:  
2 P123 Mouse $10.00 In Stock   Part Price <VLOOKUP>
3 P124 Keyboard $12.00 Out of Stock      
4 P125 Monitor $98.00 In Stock      

In this example of how to create a VLOOKUP, the below formula is entered into cell G2.

=VLOOKUP(G1,A1:D4,3,FALSE)

Below is an explanation of what each part of the above formula means.

  • =VLOOKUP() - This is the body of the VLOOKUP formula or function.
  • G1 - The lookup value of what contains what VLOOKUP should find, in this example, this is where the part number is entered.
  • A1:D4 - The table array or lookup table, which explains to VLOOKUP the boundaries of the table it is looking at. In this example, we're looking at all the part numbers, descriptions, prices, and availability.
  • 3 - The column index number. VLOOKUP does not rely on the column heading, so you must count from left-to-right how many columns over you want to return the data. In this case, the 'Price' column is three columns over.
  • FALSE - The range lookup defines if you need an exact or approximate match. In this example, we want to get the exact price of the part number.

Tip: The lookup value must be in the left-most column of where the lookup table begins or it will not work.

Once the above formula has been entered into cell G2 any valid part number that is entered into G1 returns the price for that part. For example, if we entered P124 into G1, it would return $12.00.

Below is another table example, and an example of how you can use VLOOKUP to find an approximate match instead of an exact match. In the example below, we're finding the discount a customer would receive if they were ordering a lot of parts. For example, if they ordered 134 parts, the below formula would return a discount of 10%.

  A B C D E
1 Quantity Discount   Part Quantity  
2 1 0%   Discount <VLOOKUP>
3 10 5%      
4 100 10%      
5 1000 15%      

=VLOOKUP(E1,A1:B5,2,TRUE)

This next example, is almost the same as the earlier example, except that it is TRUE instead of FALSE. Setting the range lookup to TRUE will find an approximate match instead of an exact match. Since 134 (as mentioned earlier) is not in the list of values, it returns the result for the closest match, which is 100 or 10%.

Tip: When using TRUE make sure your left-most column is in ascending order.

Also see: Excel, Formula