**VLOOKUP**

**What Does It Do ?**

When the item is found, it then scans across to pick a cell entry.

**Syntax**

The

**Item To Find**is a single item specified by the user.

The

**Range To LookIn**is the range of data with the row headings at the left hand side.

The

**Column To PickFrom**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**

**Example 1**

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.

**Example 2**

different makes of cars.

The =VLOOKUP() scans down row headings in column F for the spare part entered in column C.

When the make is found, the =VLOOKUP() then scans across to find the price, using the result of the =MATCH() function to find the position of the make of car.

The functions use the absolute ranges indicated by the dollar symbol . This ensures that when the formula is copied to more cells, the ranges for =VLOOKUP() and =MATCH() do not change.

** Example 3**

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.

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) **

** 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)