Formula for Score Based on Value Range

Question

Level 1

12 points

Formula for Score Based on Value Range

I could use some help with a formula.

In the table Points System, I have a range with a score (points) for given numbers.

In the table Score, I would like to enter a value in column A and have the score for the value reflected in column B.

Is it possible to have a formula in column B of the Score table (e.g., if between 7 - 9 would equal a score of 4) that would not require the Points System table reference.

I saw a couple of BETWEEN functions, but neither were applicable. I tried several variations of multiple IF statements with no luck:

(IF(AND(A2≤19),0,IF(A2≤16,1,IF(A2≤13,2,IF(A2≤10,3,IF(A2≤10,3),IF(A2≤17,4),IF(A2≤17,3),IF(A2≤6,5)))))

Thank for your help.

Posted on Oct 6, 2024 5:19 PM

Reply

Answer 1

SGIII

Level 8

36,434 points

Oct 6, 2024 8:38 PM in response to Bear34_1

If you use the inexact match-type feature of XLOOKUP you can avoid getting tangled up in logic.

=XLOOKUP(A2,Points::A,Points::B,"out of range",1)

Note that the formula doesn't need a column for the lower end of the ranges. But you can of course add one for cosmetic reasons to remind yourself.

More on XLOOKUP here:

XLOOKUP - Apple Support

=XLOOKUP(search-value, search-range, return-range, if-not-found, match-type, search-type)

SG

Reply

Answer 2

6x6

Level 6

16,712 points

Oct 6, 2024 5:26 PM in response to Bear34_1

I believe this is what you seek:

(I used cell B6 in this example because it was convenient in one of my test tables. Your cell would be A2.)

Reply

Answer 3

Bear34_1 Author

Level 1

12 points

Oct 7, 2024 8:11 AM in response to 6x6

I appreciate you for taking the time to help me. It's exactly what I need.

Reply

Answer 4

Bear34_1 Author

Level 1

12 points

Oct 7, 2024 8:13 AM in response to SGIII

I appreciate you for taking the time to help me. I need to spend time reviewing the XLOOKUP function, it well be very useful in the future.

Reply