Ms excel

Excel 1178 views 2 replies
User Avatar

article
487 Points Joined June 2010

Can anyone give me an example of pivot table and vlookup/hlookup explaining how does it work?

0 Reply Share Follow
On 12 September 2012 at 12:40 Report
Replies (2)
User Avatar

ACCOUNTS EXCECUTIVE
575 Points Joined February 2010

vlookup

VLOOKUP          
           
  <   Index   >
 
 
                   
              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)        

 

Reply
On 12 September 2012 at 16:14
User Avatar

ACCOUNTS EXCECUTIVE
575 Points Joined February 2010
HLOOKUP          
           
  <   Index   >
 
 
                   
      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)

 

Reply
On 12 September 2012 at 16:15


CCI Pro

Leave a Reply

Post Reply

Your are not logged in . Please login to post replies

Click here to Login / Register  

Recent Threads

More recent discussions | Post

Related Threads
Loading