+ Reply to Thread
Results 1 to 8 of 8

Need help with VLOOKUP or INDEX/MATCH with Subtract formula

  1. 05-05-2014, 12:57 PM #1
    drheenas
    drheenas is offline
    Registered User
    Join Date
    05-05-2014
    Location
    US
    MS-Off Ver
    Excel 2007
    Posts
    2

    Need help with VLOOKUP or INDEX/MATCH with Subtract formula

    I have 2 sheets with 2 columns. I want to match the student id's in both worksheets. IF the student ID's match, then I want to subtract their corresponding ranking from before term with after term and get the difference in Column C (End of term worksheet)

    I tried using: ='Data Start of Term'!B2 - SUMIF(A2,'Data End of Term'!B2,'Data Start of Term'!A2) but its not giving me the true value.

    ['Data End of Term'!C2] =B2-INDEX('Data Start of Term'!$B:$B,MATCH(A2,'Data Start of Term'!$A:$A,0))... this just gives the value as 0- if it matches, doesn't calculate difference

    Appreciate your help!
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 05-05-2014, 01:07 PM #2
    ConneXionLost
    ConneXionLost is offline
    Forum Expert ConneXionLost's Avatar
    Join Date
    03-11-2009
    Location
    Victoria, Canada
    MS-Off Ver
    2010
    Posts
    2,952

    Re: Need help with VLOOKUP or INDEX/MATCH with Subtract formula

    Perhaps like this:

    Please Login or Register  to view this content.
    Cheers,
    Would you like to say thanks? Please click the: " Add Reputation" button, on the grey bar below the post.
    Reply With QuoteRegister To Reply
  3. 05-05-2014, 01:10 PM #3
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,939

    Re: Need help with VLOOKUP or INDEX/MATCH with Subtract formula

    Hi and welcome to the forum

    Try this, copied down...
    =IFERROR(B2-VLOOKUP(A2,'Data Start of Term'!$A$2:$B$22,2,0),"ID not found last term")
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford
    Reply With QuoteRegister To Reply
  4. 05-05-2014, 01:20 PM #4
    etaf
    etaf is offline
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.84 (24041420))
    Posts
    8,751

    Re: Need help with VLOOKUP or INDEX/MATCH with Subtract formula

    try

    =VLOOKUP([@[Student id]],'Data Start of Term'!$A$1:$B$1443,2,FALSE) - VLOOKUP([@[Student id]],'Data End of Term'!$A$1:$B$1443,2,FALSE)
    or you can use index match

    BUT you have a huge table and it takes a long while to update the formula - how many records are you likely to have ?
    I would reduce your TABLE considerably

    I have used range upto row 1443 - but it still took a long time to update , and as a table updates the 1 million+ rows you have
    Wayne
    if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here

    If you have a solution to your thread - Please mark your thread solved do the following: >
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
    Reply With QuoteRegister To Reply
  5. 05-05-2014, 01:32 PM #5
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,939

    Re: Need help with VLOOKUP or INDEX/MATCH with Subtract formula

    I agree with etaf on this...
    BUT you have a huge table and it takes a long while to update the formula - how many records are you likely to have ?
    I would reduce your TABLE considerably
    I actually converted those tables to ranges so it would not take so long
    Reply With QuoteRegister To Reply
  6. 05-05-2014, 04:01 PM #6
    drheenas
    drheenas is offline
    Registered User
    Join Date
    05-05-2014
    Location
    US
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: Need help with VLOOKUP or INDEX/MATCH with Subtract formula

    Thank you and appreciate all your help!

    @etaf: I get an error message with the formula saying that the name I entered is not valid

    @FDibbins: I am able to get the values which differ in both sets but it doesn't calculate the difference. It just displays 0 for corresponding ranking found with student ids

    @ConneXionLost: I am able to get values for the sample data set using your formula. The actual data set has 1234 rows in "Data Start of Term" and 1256 rows in "Data End of Term" and the rankings are in Column X in the actual data set. I am not able to get the same results replicating your formula in the larger data set
    =X2-IFERROR(INDEX('Data Start of Term'!$A$1:$X$1234,MATCH(A2,'Data Start of Term'!$A$1:$A$1234,0),2),0)

    Request your help for this again!
    Reply With QuoteRegister To Reply
  7. 05-05-2014, 04:06 PM #7
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,939

    Re: Need help with VLOOKUP or INDEX/MATCH with Subtract formula

    @FDibbins: I am able to get the values which differ in both sets but it doesn't calculate the difference. It just displays 0 for corresponding ranking found with student ids
    If it is showing 0, then there is no difference between the 2 values.

    =IFERROR(B2-VLOOKUP(A2,'Data Start of Term'!$A$2:$B$22,2,0),"ID not found last term")

    The bolded part is finding B2's equivalent in the other sheet (if it exists), which is then subtracted from B2 on this sheet...0 says there is no difference
    Reply With QuoteRegister To Reply
  8. 05-05-2014, 07:14 PM #8
    ConneXionLost
    ConneXionLost is offline
    Forum Expert ConneXionLost's Avatar
    Join Date
    03-11-2009
    Location
    Victoria, Canada
    MS-Off Ver
    2010
    Posts
    2,952

    Re: Need help with VLOOKUP or INDEX/MATCH with Subtract formula

    If you want to move the "Ranking" column, then this might be better:

    Please Login or Register  to view this content.
    Cheers,
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. VLOOKUP or INDEX MATCH where the reference MATCH cell has a formula behind it
    By Grizz in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-13-2014, 04:16 AM
  2. help with index/match or vlookup/match formula
    By tsiguy96 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-27-2013, 07:57 PM
  3. Match Index or Vlookup formula question
    By ianco in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 05-21-2012, 08:20 AM
  4. which formula to use, vlookup, match, index or ??
    By efour in forum Excel General
    Replies: 2
    Last Post: 03-23-2010, 06:32 AM
  5. n/a in vlookup/index/match formula
    By andrewm in forum Excel Formulas & Functions
    Replies: 38
    Last Post: 09-06-2005, 12:05 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1