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, when looking up a number in a phone book you read down the list of names until you find the name and then move your finger to the right to find the associated phone number.

A 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 the part number and immediately get the description or price of that part number. Let's 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      

With this example on how to create a VLOOKUP, enter the below formula 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. In this example, we are 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 does 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 are finding the discount the customer would receive if they were ordering dozens 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 example, is almost the same as the previous example, except that it is TRUE instead of FALSE. Setting the range lookup to TRUE finds 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