Spreadsheet: 88588d1288368184-vlookup-with-match-book2
Sheet: Sheet1

VLOOKUP |  |  |  |  |  |  |  |  | EXIT
 |  |  |  |  |  |  | The column numbers are not needed.
 |  |  |  |  |  |  | they are part of the illustration.
 |  | 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 : | Mar
 |  |  |  |  | Which column needs to be picked out : | 4
 |  |  |  |  | The result is : | 45
 |  |  |  |  |  | =VLOOKUP(G11,C6:H8,G12,FALSE)
 | 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.
 | 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
 |  | [NAME] | 10 | 80 | 97
 |  | [NAME] | 20 | 90 | 69
 |  | [NAME] | 30 | 100 | 45
 |  | Carol | 40 | 110 | 51
 |  | [NAME] | 50 | 120 | 77
 |  |  |  | Type a name to look for : | [NAME]
 |  |  |  | Type a month to look for : | [NAME]
 |  |  |  | The result is : | 69
 |  |  |  |  | =VLOOKUP(F56,C50:F54,MATCH(F57,D49:F49,0)+1,FALSE)
 | Example 2
 | This example shows how the =VLOOKUP() is used to pick the cost of a spare part for
 | 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.
 | Maker | Spare | Cost |  | Lookup Table
 | Vauxhall | Ignition | 50 |  |  | Vauxhall | Ford | VW |  | The MATCH(B81,G74:I74,0)+1 returns the column you want returned from your table. So, this formula is saying that add 1 to whatever column (counting from column G) you find a match to whatever is in B81. As an example, if "Green" is in B81 and in H74 (Column 2 from G74), the VLOOKUP simplifies to
 | VW | GearBox | 450 |  | GearBox | 500 | 450 | 600
 | Ford | Engine | 1200 |  | Engine | 1000 | 1200 | 800 |  | 290
 | VW | Steering | 275 |  | Steering | 250 | 350 | 275
 | Ford | Ignition | 70 |  | Ignition | 50 | 70 | 45 |  | Follow that?
 | Ford | CYHead | 290 |  | CYHead | 300 | 290 | 310
 | Vauxhall | CYHead | 300
 | Ford | CYHead | 290
 |  |  | =VLOOKUP(C81,F75:I79,MATCH(B81,G74:I74,0)+1,FALSE)
 | 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 [NAME].
 | 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 | [NAME]
 |  | Brick | 2 |  | 1 | 0 | 0 | 0
 |  | Wood | 1 |  | 100 | 0.06 | 0.03 | 0.12
 |  | [NAME] | 3 |  | 300 | 0.08 | 0.05 | 0.15
 |  | Orders Table
 |  | Item | Units | Unit Cost | Discount | Total
 |  | Brick | 100 | 2 | 0.06 | 188
 |  | Wood | 200 | 1 | 0.03 | 194
 |  | [NAME] | 150 | 3 | 0.12 | 396
 |  | Brick | 225 | 2 | 0.06 | 423
 |  | Wood | 50 | 1 | 0 | 50
 |  | [NAME] | 500 | 3 | 0.15 | 1275
 | [NAME] 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)