Definition:
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 stands for horizontal (row).
When performing a VLOOKUP, each match returns the corresponding
value in 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 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 table below as an example for working with a VLOOKUP.
With this example on how to create a VLOOKUP, enter the below formula into cell G2.
Below is another table and an example of how you can use VLOOKUP to find an approximate match instead of an exact match. In this example, 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%.
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 stands for horizontal (row).
Note: This page focuses on the VLOOKUP formula, being more commonly used than the HLOOKUP formula.
Videos Link1: https://www.youtube.com/watch?v=JodZ3cz9fCU
Videos Link2: https://www.youtube.com/watch?v=1hXYU2X5djQ
Uses:
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 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 table below 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 |
=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 value of what VLOOKUP should look for in the table. 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 the columns from left-to-right to determine what column should have its data returned. 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 and an example of how you can use VLOOKUP to find an approximate match instead of an exact match. In this example, 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%.
