none
Lookup between values

    Question

  • Hi,

    In this file, I have two columns in range A2:B7.  Names of students are in A2:A7 and their scores are in B2:B7.  I have another table with three columns of lower limit score, upper limit score and category.  I would like to determine the category in range C2:C7.  I have shown my expected result in range C2:C7.

    What calculated field and/or calculated item formula should be written to get the expected result.

    Thank you.


    Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com

    Friday, November 08, 2013 3:23 AM

Answers

  • This formula should work

    =CALCULATE(VALUES(LookupScore[Category]), filter(LookupScore, LookupScore[Lower score] <=CALCULATE(SUM(FactScore[Score])) && CALCULATE(sum(FactScore[Score]))<=LookupScore[Upper Score]))

    1) To get this, my model looks like this

    2) My end result looks like this

    


    Cheers,
    Jason | www.SqlJason.com
    P.S. : Please click the 'Mark as Answer' button if a post solves your problem! :)

    Some Random Thoughts

    Follow me on Twitter

    • Marked as answer by Ashish Mathur Saturday, November 09, 2013 10:49 PM
    Friday, November 08, 2013 2:42 PM

All replies

  • This formula should work

    =CALCULATE(VALUES(LookupScore[Category]), filter(LookupScore, LookupScore[Lower score] <=CALCULATE(SUM(FactScore[Score])) && CALCULATE(sum(FactScore[Score]))<=LookupScore[Upper Score]))

    1) To get this, my model looks like this

    2) My end result looks like this

    


    Cheers,
    Jason | www.SqlJason.com
    P.S. : Please click the 'Mark as Answer' button if a post solves your problem! :)

    Some Random Thoughts

    Follow me on Twitter

    • Marked as answer by Ashish Mathur Saturday, November 09, 2013 10:49 PM
    Friday, November 08, 2013 2:42 PM
  • Thank you.  That worked.

    Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com

    Saturday, November 09, 2013 10:50 PM