# Vlookup

What Does It Do?
This function scans down the row headings at the side of a table to find a specified item. When the item is found, it then scans across to pick a cell entry.

Syntax
=VLOOKUP(ItemToFind, RangeToLookIn, ColumnToPickFrom, SortedOrUnsorted)
The ItemToFind is a single item specified by the user. The RangeToLookIn is the range of data with the row headings at the left hand side. The ColumnToPickFrom is how far across the table the function should look to pick from. The Sorted/Unsorted is whether the column headings are sorted. TRUE for yes, FALSE for no.

Formatting
No special formatting is needed.

 col 1 col 2 col 3 col 4 col 5 col 6 Jan 10 20 30 40 50 Feb 80 90 100 110 120 Mar 97 69 45 51 77
 Type a month to look for : Feb Which column needs to be picked out : 4
=VLOOKUP(G11,C6:H8,G12,FALSE)
 The result is : 100

# Example 1:

This table is used to find a value based on a specified name and month. The =VLOOKUP() is used to scan down to find the name. The problem arises when we need to scan across to find the month column. To solve the problem the =MATCH() function is used.

The =MATCH() looks through the list of names to find the month we require. It then calculates the position of the month in the list. Unfortunately, because the list of months is not as wide as the lookup range, the =MATCH() number is 1 less than we require, so and extra 1 is added to compensate.

The =VLOOKUP() now uses this =MATCH() number to look across the columns and picks out the correct cell entry.

The =VLOOKUP() uses FALSE at the end of the function to indicate to Excel that the row headings are not sorted.

 Jan Feb Mar Bob 10 80 97 Eric 20 90 69 Alan 30 100 45 Carol 40 110 51 David 50 120 77
 Type a name to look for : eric Type a month to look for : mar
 The result is : 69 =VLOOKUP(F56, C50:F54, MATCH(F57,D49:F49,0)+1, FALSE)

Table 2: Shows the same data but using the =ABS() function to correct the calculations.

Table 2:
 Test Cut Required Length Actual Length Difference Error Perc- entage Test 1 120 120 0 0% Test 2 120 90 30 25% Test 3 120 150 30 25% =ABS (D45-E45)

# Example 2:

This table is used to find a value based on a specified name and month. The =VLOOKUP() is used to scan down to find the name. The problem arises when we need to scan across to find the month column. To solve the problem the =MATCH() function is used.

The =MATCH() looks through the list of names to find the month we require. It then calculates the position of the month in the list. Unfortunately, because the list of months is not as wide as the lookup range, the =MATCH() number is 1 less than we require, so and extra 1 is added to compensate.

The =VLOOKUP() now uses this =MATCH() number to look across the columns and picks out the correct cell entry.

The =VLOOKUP() uses FALSE at the end of the function to indicate to Excel that the row headings are not sorted.

 Maker Spare Cost Vauxhall Ignition £50 VW GearBox £600 Ford Engine £1,200 VW Steering £275 Ford Ignition £70 Ford CYHead £290 Vauxhall GearBox £500 Ford Engine £1,200 =VLOOKUP(C81, F75:I79, MATCH(B81,G74:I74,0)+1, FALSE)

 Lookup Table Vauxhall Ford VW GearBox 500 450 600 Engine 1000 1200 800 Steering 250 350 275 Ignition 50 70 45 CYHead 300 290 310

# Example 3:

In the following example a builders merchant is offering discount on large orders. The Unit Cost Table holds the cost of 1 unit of Brick, Wood and Glass. The Discount Table holds the various discounts for different quantities of each product.
The Orders Table is used to enter the orders and calculate the total.

All the calculations take place in the Orders Table. The name of the Item is typed in column C of the Orders Table.

The Unit Cost of the item is then looked up in the Unit Cost Table. The FALSE option has been used at the end of the function to indicate that the product names down the side of the Unit Cost Table are not sorted. Using the FALSE option forces the function to search for an exact match. If a match is not found, the function will produce an error.
=VLOOKUP(C126,C114:D116,2,FALSE)

The discount is then looked up in the Discount Table If the Quantity Ordered matches a value at the side of the Discount Table the =VLOOKUP will look across to find the correct discount. The TRUE option has been used at the end of the function to indicate that the values down the side of the Discount Table are sorted. Using TRUE will allow the function to make an approximate match. If the Quantity Ordered does not match a value at the side of the Discount Table, the next lowest value is used. Trying to match an order of 125 will drop down to 100, and the discount from the 100 row is used.
=VLOOKUP(D126, F114:I116, MATCH(C126,G113:I113,0)+1, TRUE)

 Discount Table Unit Cost Table Brick Wood Glass Brick £2 1 0% 0% 0% Wood £1 100 6% 3% 12% Glass £3 300 8% 5% 15%
 Orders Table Item Units Unit Cost Discount Total Brick 100 £2 6% £188 Wood 200 £1 3% £194 Glass 150 £3 12% £396 Brick 225 £2 6% £423 Wood 50 £1 0% £50 Glass 500 £3 15% £1,275

Formula for :
Unit Cost =VLOOKUP(C126, C114:D116,2, FALSE)
Discount =VLOOKUP(D126, F114:I116, MATCH(C126, G113:I113,0)+1, TRUE)
Total =(D126*E126)- (D126*E126*F126)