# 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

• 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! :)

• Marked as answer by 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! :)

• Marked as answer by 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