+ Reply to Thread
Results 1 to 13 of 13

Help: SUMPRODUCT/LOOKUP for values

  1. #1
    Registered User
    Join Date
    10-01-2017
    Location
    Wales
    MS-Off Ver
    2017
    Posts
    59

    Help: SUMPRODUCT/LOOKUP for values

    Hello,

    I am hoping for some help for my spreadsheet.
    I am trying to show pupil progress, using 'sub levels'. I have used a VLOOKUP table for tables with 4a higher than 4b, followed by 4c. Levels go all the way from level 1 to level 6 (6 being highest).

    I want to measure pupil progress, and how many 'sub levels' they are away from their table. However, this needs to be done at different points in the year, meaning if cells are blank I want it to track progress up to that point (e.g. if in AUT Term, only count sub levels to there). If the cells are full, I want it to show for the final level (being SUM Term).

    I have attached my spreadsheet.
    For example:
    Cell K3 should show 0, as they have achieved target of 4A, also cell L3 should show Y for meeting target
    Cell K4 should show 3, but if G4 is updated, this should change I4 and L4 by SUM, also I4 should currently show -1
    Cell K5 should show +1, as they are 3 grades above target, with cell L5 having an E for 'exceeded'

    I would like to have formulas in the cell if possible, and not use any 'helper' cells

    I hope this is somewhat clear and I really appreciate your help
    J
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    05-20-2015
    Location
    Chicago, Illinois
    MS-Off Ver
    2016
    Posts
    2,105

    Re: Help: SUMPRODUCT/LOOKUP for values

    I'm not sure I understand what you're attempting, but this line in particular throws me off:

    Quote Originally Posted by Jones90 View Post
    Cell K5 should show +1, as they are 3 grades above target, with cell L5 having an E for 'exceeded'
    If the target is 5A and the Summer mark is 5A, shouldn't K5 show 0?

    My best guess at what you're doing is that you want column K to calculate the difference between the target in J and the most recent mark for any of the periods in E:H. If I've got that right, you should be able to use the formula below in K3. It should be array-entered (confirm with Ctrl + Shift + Enter instead of Enter):

    =MATCH(INDEX($E3:$H3,MAX(IF($E3:$H3<>"",COLUMN($E3:$H3)-COLUMN($E3)+1))),'Grade Table'!$A$3:$A$32,0)-MATCH(J3,'Grade Table'!$A$3:$A$32,0)

    Fill it down for the rest of column K. Give that a try and see if it proves helpful. If not, could you please clarify what you're looking for - perhaps with a few more explanations of expected values?

    Similarly, for I3 you can array-enter the following and fill down:

    =MATCH(E3,'Grade Table'!$A$3:$A$32,0)-MATCH(INDEX($F3:$H3,MAX(IF($F3:$H3<>"",COLUMN($F3:$H3)-COLUMN($F3)+1))),'Grade Table'!$A$3:$A$32,0)
    Last edited by CAntosh; 10-16-2017 at 04:14 PM.
    If your problem has been solved, please use "Thread Tools" to mark the thread as "Solved".

    If you're grateful for this site's existence and would like to contribute to the community, please consider posting something hilarious in our joke thread:
    https://www.excelforum.com/the-water...ke-thread.html

  3. #3
    Forum Expert tim201110's Avatar
    Join Date
    10-23-2011
    Location
    Russia
    MS-Off Ver
    2016, 2019
    Posts
    2,359

    Re: Help: SUMPRODUCT/LOOKUP for values

    formula in column L can not find a value from col A in col H from row 831
    Why dates in H earlier than in A?

  4. #4
    Registered User
    Join Date
    10-01-2017
    Location
    Wales
    MS-Off Ver
    2017
    Posts
    59

    Re: Help: SUMPRODUCT/LOOKUP for values

    Thank you, this seems to have solved both columns I & K very well.
    It is much appreciated

  5. #5
    Registered User
    Join Date
    10-01-2017
    Location
    Wales
    MS-Off Ver
    2017
    Posts
    59

    Re: Help: SUMPRODUCT/LOOKUP for values

    Quote Originally Posted by tim201110 View Post
    formula in column L can not find a value from col A in col H from row 831
    Why dates in H earlier than in A?

    Thank you for your reply. Apologies, but I have yet to type in a formula here as I was unsure of what it would be.

    I am trying to work out in L whether they have not met target= show 'N', met target = show 'Y', exceeded target = show 'E'.
    However, pupils may have met target by the end of AUT term if they perform very well, therefore, is there a way of searching AUT, SPR & SUM to see whether they have achieved targets, rather than SUM alone?

    If not, I believe this could be done simply using an IF < = or > function, using SUM only (Col H) as a ref?

    Again, your feedback has been very helpful and always appreciated.
    J

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    42,099

    Re: Help: SUMPRODUCT/LOOKUP for values

    Maybe something like this:

    =LOOKUP(MAX(E3:K3),{-30,0,1},{"N","Y","E"})
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  7. #7
    Registered User
    Join Date
    10-01-2017
    Location
    Wales
    MS-Off Ver
    2017
    Posts
    59

    Re: Help: SUMPRODUCT/LOOKUP for values

    Quote Originally Posted by AliGW View Post
    Maybe something like this:

    =LOOKUP(MAX(E3:K3),{-30,0,1},{"N","Y","E"})

    Hello again,

    Many thanks for your efforts here, however, I did not seem to work....It showed E in L3, even though it should have shown Y as the targets and final outcome matched....any other suggestions would be most appreciated as always.
    J

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    42,099

    Re: Help: SUMPRODUCT/LOOKUP for values

    It would help if I understood your table, but I fear I don't. It's unclear to me what is being measured against what. I should say it's not the levels that are unclear - I am a teacher myself.

  9. #9
    Registered User
    Join Date
    10-01-2017
    Location
    Wales
    MS-Off Ver
    2017
    Posts
    59

    Re: Help: SUMPRODUCT/LOOKUP for values

    Quote Originally Posted by AliGW View Post
    It would help if I understood your table, but I fear I don't. It's unclear to me what is being measured against what. I should say it's not the levels that are unclear - I am a teacher myself.
    Hello again,
    Column J is pupils' targets. If pupils have shown that they have achieved their targets in either the AUT (Col F), SPR (Col G), SUM (Col H), this means column L would show 'Y'. If they have not met their target it should show 'N', if they have exceeded their target, e.g. they have scored a 5c, when their target was a 4a then it should show 'E' as they Exceeded their target.

    I hope this is clearer. I understand this is rather complex, but as you can see I need all the help I can get.

    Many thanks
    J

  10. #10
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    42,099

    Re: Help: SUMPRODUCT/LOOKUP for values

    I'll have another look. It's not complex, just not very clearly labelled - you should see some of my departmental and whole school workbooks!!! Watch this space ...

  11. #11
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    42,099

    Re: Help: SUMPRODUCT/LOOKUP for values

    In L3 copied down, assuming that K3 reflects their best result of the three:

    =LOOKUP(K3,{-30,0,1},{"N","Y","E"})

  12. #12
    Registered User
    Join Date
    10-01-2017
    Location
    Wales
    MS-Off Ver
    2017
    Posts
    59

    Re: Help: SUMPRODUCT/LOOKUP for values

    Quote Originally Posted by AliGW View Post
    In L3 copied down, assuming that K3 reflects their best result of the three:

    =LOOKUP(K3,{-30,0,1},{"N","Y","E"})
    Hello,
    This seemed to give me opposite results so I changed the E and N around and it works perfectly
    Thank you very much for responding, it has really saved me an awful lot of work
    J

  13. #13
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    42,099

    Re: Help: SUMPRODUCT/LOOKUP for values

    Glad to have helped. I fail to see why a minus score would indicate exceeding one’s target and a positive one not meeting it, but hey, if it works, it works!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 2
    Last Post: 01-20-2017, 04:27 PM
  2. [SOLVED] Using SUMPRODUCT(COUNTIFS(...)) to add values based on a lookup table
    By HeyInKy in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 11-29-2016, 04:11 PM
  3. [SOLVED] Help with SUMPRODUCT and Match using a Range of values for Lookup
    By Esavoye in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-06-2013, 01:05 PM
  4. Sumproduct with user input lookup values
    By dracoalien007 in forum Excel General
    Replies: 2
    Last Post: 05-25-2012, 09:27 AM
  5. Replies: 1
    Last Post: 05-19-2012, 02:54 AM
  6. Replies: 1
    Last Post: 06-28-2010, 09:43 PM
  7. Replies: 2
    Last Post: 04-01-2010, 11:19 AM

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1