Looks like no one’s replied in a while. To start the conversation again, simply ask a new question.

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
4 replies

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


Formula for Score Based on Value Range

Welcome to Apple Support Community
A forum where Apple customers help each other with their products. Get started with your Apple Account.