Excel User Group
Microsoft Excel blogs, forums, files. Read, ask questions, provide answers.

vlookup within vlookup

Latest post Wed, Sep 10 2014 11:59 AM by Peter Eden. 1 replies.
  • Tue, Sep 9 2014 11:51 AM

    vlookup within vlookup

    I have two tables that i want to add together depending on values to produce a thrid summary table.

    Table one has the amount of days per month worked in each stage of the project.

      July August September
    Feasibility 144 168 160
    Concept 70 176 160
    Detail 160 24 0

    Table two has all the rates of the different project team

      Feasibility Concept Detail
    Project Engineering £179 £89 £179
    Design Manager £93 £93 £93
    Electrical Engineer £193 £193 £193

    I want to now produce a table which multiplies all the days worked with the relevant rate for the stage of work and then gives me a one value of cost per month as per below:

      July August September
    Project Engineering  (144x£179)+(70x£93)+(160x£193)    
    Design Manager      
    Electrical Engineer      

     I have 10 years worth to do so i need a formular that i can write into the cell and it will do that for me. I have written the maths that i need the formular to do in July for the Project Engineer so you can see what i mean. Can someone help?

     

    Filed under: ,
    • Post Points: 21
  • Wed, Sep 10 2014 11:59 AM In reply to

    • Peter Eden
    • Not Ranked
    • Joined on Wed, Sep 10 2014
    • Posts 1
    • Points 5

    Re: vlookup within vlookup

    I would use a combination of vlookup and hlookup to get the hours and rates.

    In this example I have used named ranges to make it clearer, the first table being 'Days' and the second being 'Rates'

    The formula for Project engineering for July where P/E is cell B15 and July is C14 is :

    =VLOOKUP($B15,Rate,2,0)*HLOOKUP(C$14,Days,2,0)+VLOOKUP($B15,Rate,3,0)*HLOOKUP(C$14,Days,3,0)+VLOOKUP($B15,Rate,4,0)*HLOOKUP(C$14,Days,4,0)

      July August September
    Feasibility 144 168 160
    Concept 70 176 160
    Detail 160 24 0
     
      Feasibility Concept Detail
    Project Engineering £179 £89 £179
    Design Manager £93 £93 £93
    Electrical Engineer £193 £193 £193
     
      July August September
    Project Engineering 60646 50032 42880
    Design Manager 34782 34224 29760
    Electrical Engineer 72182 71024 61760

    I am not sure that the sum that you put in your table was correct though :)

     

    • Post Points: 5
Page 1 of 1 (2 items) | RSS
Copyright Excel User Group and the relevant contributors, 2010. All Trade Marks acknowledged. This site is a peer-to-peer site and NOT affiliated in any way to Microsoft Corp. All rights reserved.