Can anyone give me an example of pivot table and vlookup/hlookup explaining how does it work?
2 Replies
DAYANANDA K N
(ACCOUNTS EXCECUTIVE)
(575 Points)
Replied 12 September 2012
vlookup
| VLOOKUP |
|
|||||||||||||||||||||
| 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 : | Feb | |||||||||||||||||||||
| Which column needs to be picked out : | 4 | |||||||||||||||||||||
| The result is : | 100 | |||||||||||||||||||||
| =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 | ||||||||||||||||||||
| 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) | ||||||||||||||||||||||
| 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 | |||||||||||||||||
| VW | GearBox | £600 | GearBox | 500 | 450 | 600 | ||||||||||||||||
| Ford | Engine | £1,200 | Engine | 1000 | 1200 | 800 | ||||||||||||||||
| VW | Steering | £275 | Steering | 250 | 350 | 275 | ||||||||||||||||
| Ford | Ignition | £70 | Ignition | 50 | 70 | 45 | ||||||||||||||||
| Ford | CYHead | £290 | CYHead | 300 | 290 | 310 | ||||||||||||||||
| Vauxhall | GearBox | £500 | ||||||||||||||||||||
| Ford | Engine | £1,200 | ||||||||||||||||||||
| =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 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) | |||||||||||||||||||||
DAYANANDA K N
(ACCOUNTS EXCECUTIVE)
(575 Points)
Replied 12 September 2012
| HLOOKUP |
|
|||||||||||||||||||||
| Jan | Feb | Mar | row 1 | The row numbers are not needed. | ||||||||||||||||||
| 10 | 80 | 97 | row 2 | they are part of the illustration. | ||||||||||||||||||
| 20 | 90 | 69 | row 3 | |||||||||||||||||||
| 30 | 100 | 45 | row 4 | |||||||||||||||||||
| 40 | 110 | 51 | row 5 | |||||||||||||||||||
| 50 | 120 | 77 | row 6 | |||||||||||||||||||
| Type a month to look for : | Feb | |||||||||||||||||||||
| Which row needs to be picked out : | 4 | |||||||||||||||||||||
| The result is : | 100 | =HLOOKUP(F10,D3:F10,F11,FALSE) | ||||||||||||||||||||
| What Does It Do ? | ||||||||||||||||||||||
| This function scans across the column headings at the top of a table to find a specified item. | ||||||||||||||||||||||
| When the item is found, it then scans down the column to pick a cell entry. | ||||||||||||||||||||||
| Syntax | ||||||||||||||||||||||
| =HLOOKUP(ItemToFind,RangeToLookIn,RowToPickFrom,SortedOrUnsorted) | ||||||||||||||||||||||
| The ItemToFind is a single item specified by the user. | ||||||||||||||||||||||
| The RangeToLookIn is the range of data with the column headings at the top. | ||||||||||||||||||||||
| The RowToPickFrom is how far down the column 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 month and name. | ||||||||||||||||||||||
| The =HLOOKUP() is used to scan across to find the month. | ||||||||||||||||||||||
| The problem arises when we need to scan down to find the row adjacent to the name. | ||||||||||||||||||||||
| To solve the problem the =MATCH() function is used. | ||||||||||||||||||||||
| The =MATCH() looks through the list of names to find the name we require. It then calculates | ||||||||||||||||||||||
| the position of the name in the list. Unfortunately, because the list of names is not as deep | ||||||||||||||||||||||
| as the lookup range, the =MATCH() number is 1 less than we require, so and extra 1 is | ||||||||||||||||||||||
| added to compensate. | ||||||||||||||||||||||
| The =HLOOKUP() now uses this =MATCH() number to look down the month column and | ||||||||||||||||||||||
| picks out the correct cell entry. | ||||||||||||||||||||||
| The =HLOOKUP() uses FALSE at the end of the function to indicate to Excel that the | ||||||||||||||||||||||
| column headings are not sorted, even though to us the order of Jan,Feb,Mar is correct. | ||||||||||||||||||||||
| If they were sorted alphabetically they would have read as Feb,Jan,Mar. | ||||||||||||||||||||||
| 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 month to look for : | feb | |||||||||||||||||||||
| Type a name to look for : | alan | |||||||||||||||||||||
| The result is : | 100 | |||||||||||||||||||||
| =HLOOKUP(F54,D47:F54,MATCH(F55,C48:C52,0)+1,FALSE) | ||||||||||||||||||||||
| Example 2 | ||||||||||||||||||||||
| This example shows how the =HLOOKUP() is used to pick the cost of a spare part for | ||||||||||||||||||||||
| different makes of cars. | ||||||||||||||||||||||
| The =HLOOKUP() scans the column headings for the make of car specified in column B. | ||||||||||||||||||||||
| When the make is found, the =HLOOKUP() then looks down the column to the row specified | ||||||||||||||||||||||
| by the =MATCH() function, which scans the list of spares for the item specified in column C. | ||||||||||||||||||||||
| The function uses the absolute ranges indicated by the dollar symbol $. This ensures that | ||||||||||||||||||||||
| when the formula is copied to more cells, the ranges for =HLOOKUP() and =MATCH() do | ||||||||||||||||||||||
| not change. | ||||||||||||||||||||||
| Maker | Spare | Cost | ||||||||||||||||||||
| Vauxhall | Ignition | £50 | Vauxhall | Ford | VW | |||||||||||||||||
| VW | GearBox | £600 | GearBox | 500 | 450 | 600 | ||||||||||||||||
| Ford | Engine | £1,200 | Engine | 1000 | 1200 | 800 | ||||||||||||||||
| VW | Steering | £275 | Steering | 250 | 350 | 275 | ||||||||||||||||
| Ford | Ignition | £70 | Ignition | 50 | 70 | 45 | ||||||||||||||||
| Ford | CYHead | £290 | CYHead | 300 | 290 | 310 | ||||||||||||||||
| Vauxhall | GearBox | £500 | ||||||||||||||||||||
| Ford | Engine | £1,200 | ||||||||||||||||||||
| =HLOOKUP(B79,G72:I77,MATCH(C79,F73:F77,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 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. | ||||||||||||||||||||||
| 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 across the top 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. | ||||||||||||||||||||||
| =HLOOKUP(C127,E111:G112,2,FALSE) | ||||||||||||||||||||||
| The discount is then looked up in the Discount Table | ||||||||||||||||||||||
| If the Quantity Ordered matches a value at the top of the Discount Table the =HLOOKUP will | ||||||||||||||||||||||
| look down the column to find the correct discount. | ||||||||||||||||||||||
| The TRUE option has been used at the end of the function to indicate that the values | ||||||||||||||||||||||
| across the top 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 top 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 column is used. | ||||||||||||||||||||||
| =HLOOKUP(D127,E115:G118,MATCH(C127,D116:D118,0)+1,TRUE) | ||||||||||||||||||||||
| Unit Cost Table | ||||||||||||||||||||||
| Brick | Wood | Glass | ||||||||||||||||||||
| £2 | £1 | £3 | ||||||||||||||||||||
| Discount Table | ||||||||||||||||||||||
| 1 | 100 | 300 | ||||||||||||||||||||
| Brick | 0% | 6% | 8% | |||||||||||||||||||
| Wood | 0% | 3% | 5% | |||||||||||||||||||
| Glass | 0% | 12% | 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 | ||||||||||||||||||
| Unit Cost | =HLOOKUP(C127,E111:G112,2,FALSE) | |||||||||||||||||||||
| Discount | =HLOOKUP(D127,E115:G118,MATCH(C127,D116:D118,0)+1,TRUE) | |||||||||||||||||||||
Recent Topics
- Extended Date of GSTR-1 for the month of July 2020
- NRO Interest - Taxed at source and in Foreign Coun
- Clarity needed on effective date
- Income Tax Refund Deadline Shortened to Five Years
- Rectification Applications for Past GST Demands
- GST Invoices Series
- FILING OF ITR AY 20-21
- CLAIMING OF UNCLAIMED RCM
- Building construction works
- REGARDING GSTR-9 F.Y 2023-24
Related Threads
CAclubindia
