Vlookup to subtract values across worksheets

    Good Day,


    I have a workbook were I pull reports for a previous day and a running total for the current day on 2 separate sheets. I need to workout the difference in order to get today's figures on a third sheet. The names however are not in order on the 2 sheets. Would you use Vlookup formula and can you point me to the right syntax to work out the difference between the values corresponding to a particular name in the sheet "Current Day Total" & "Previous Day Total".


    Appreciate your help.


    Best regards


    Rob


    [TABLE="width: 379"]

    [tr]


    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [/TABLE]

    Re: Vlookup to subtract values across worksheets


    A couple of questions:


    1. Are these names unique values?
    2. Is there integrity across the sheets regarding the spelling of those names?
    3. Will the position of the tables being referenced move at all? (Hopefully not!)
    4. What is the calculation you want to perform? Is it current - previous or previous - current?

    Ali :)


    Enthusiastic self-taught user of MS Excel who's always learning!
    If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

    :!:Forum Rules

    Re: Vlookup to subtract values across worksheets


    See if this does what you want. In C2, copied across and down:


    Code
    =IFNA(INDEX('Previous Day Total'!$C$2:$E$12,MATCH(Daily!$B2,'Previous Day Total'!$B$2:$B$28,0),MATCH(Daily!C$1,'Previous Day Total'!$C$1:$E$1,0)),0)-IFNA(INDEX('Current Day Total  '!$C$2:$E$9,MATCH(Daily!$B2,'Current Day Total  '!$B$2:$B$28,0),MATCH(Daily!C$1,'Current Day Total  '!$C$1:$E$1,0)),0)

    Ali :)


    Enthusiastic self-taught user of MS Excel who's always learning!
    If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

    :!:Forum Rules

    Re: Vlookup to subtract values across worksheets


    Hello Ali.
    Unfortunately I am getting an invalid name error (#NAME?) with the formula.


    1. Are these names unique values? Yes
    2. Is there integrity across the sheets regarding the spelling of those names? Yes
    3. Will the position of the tables being referenced move at all? (Hopefully not!) No
    4. What is the calculation you want to perform? Is it current - previous or previous - current? current - previous

    Re: Vlookup to subtract values across worksheets


    Which version of Excel are you using?

    Ali :)


    Enthusiastic self-taught user of MS Excel who's always learning!
    If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

    :!:Forum Rules

    Re: Vlookup to subtract values across worksheets


    Try this instead:


    Code
    =IF(ISNA(INDEX('Current Day Total  '!$C$2:$E$12,MATCH(Daily!$B2,'Current Day Total  '!$B$2:$B$28,0),MATCH(Daily!C$1,'Current Day Total  '!$C$1:$E$1,0))),0,INDEX('Current Day Total  '!$C$2:$E$12,MATCH(Daily!$B2,'Current Day Total  '!$B$2:$B$28,0),MATCH(Daily!C$1,'Current Day Total  '!$C$1:$E$1,0))-IF(ISNA(INDEX('Previous Day Total'!$C$2:$E$12,MATCH(Daily!$B2,'Previous Day Total'!$B$2:$B$28,0),MATCH(Daily!C$1,'Previous Day Total'!$C$1:$E$1,0))),0,INDEX('Previous Day Total'!$C$2:$E$12,MATCH(Daily!$B2,'Previous Day Total'!$B$2:$B$28,0),MATCH(Daily!C$1,'Previous Day Total'!$C$1:$E$1,0))))

    Ali :)


    Enthusiastic self-taught user of MS Excel who's always learning!
    If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

    :!:Forum Rules

    Re: Vlookup to subtract values across worksheets


    Hello Ali


    I am using Excel 2010.


    The 2nd formula does work but gave erroneous results. i.e. Donald would have 2 points (11 on 'Current Day Total' - 9 on 'Previous Day Total')


    Linda
    Hugh
    Bruce
    Leon
    Dawn
    Marcus
    Kathryn
    Mark
    Charles
    Maritza


    Thank you so much




    Rob

    Re: Vlookup to subtract values across worksheets


    I think you might have copied and pasted the formula before I reposted it with a correction - could you try again, please?

    Ali :)


    Enthusiastic self-taught user of MS Excel who's always learning!
    If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

    :!:Forum Rules

    Re: Vlookup to subtract values across worksheets


    You are most welcome - sorry about the confusion there! :)

    Ali :)


    Enthusiastic self-taught user of MS Excel who's always learning!
    If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

    :!:Forum Rules

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login