Excel use cell reference as a logical operator and look for value

Question

I have a table to search for a value like this:

logical test | points
-------------|-------
<=   0       |   1
<=   10      |   2
<=   20      |   4
>    20      |   5

If my cell (e.g. A1) <= 0, the result is 1 ...
if my cell is <= 10, the result is 2 ...
if my cell> 20, the result will be 5.

I could use several if functions for this, but I want the user to not want to change the formula if the table changes. For example, if for some reason the first line changes to> 0, there is no need to change the formula.
I tried using concatenate(mycell;logical_operator;logical_value)the if function inside, but it doesn't seem to work, and I prefer a solution without VBA ...
Any suggestions?

Answer 1

, . , , , : <, < =, =, > =, >

. , .

. "$ C $2: $C $5", "$ D $2: $D $5", "$ E $2: $E $5"

, A1.

=INDEX(Scores,MATCH(TRUE,IF(Operators="<",A1<Values,(IF(Operators="<=",A1<=Values,IF(Operators="=",A1=Values,IF(Operators=">=",A1>=Values,A1>Values))))),0))

A3.

. . : , CTRL-SHIFT-ENTER. , , , . , , { }, .

, C, D. , . .

, . , # N/A. (, , 0.)

Answer 2

, , , , / .

, . vlookup (a1 ) , 1 0 ( Excel , , .

, <= 0 a > 0 , 0. - , 0, , .

, .

, :

, , , . , [x1, x2], [x3, x4] .. , .

Answer 3

A1, B1:

=IF(A1<=0,1,IF(A1<=10,2,IF(A1<=20,4,IF(A1>20,5))))

in my example, I filled in the values ​​from 0 to 27 in the Acells column A1through A28and copied this formula from B1to B28with the following test results: