Excel IF function greater than x but less than y

Question

I'm trying to arrange a column so that I can essentially "score" results.. I am looking for a formula that will go "If greater than 100000 but less than 110000 = 10, if greater than 90000 but less than 99999 = 9, etc etc.. can someone help?

Answer 1

You can do this a few ways. Vlookup or summing if statements. Assuming your column you want to score is in column A and you are scoring in column B try these:

Vlookup

(assuming a table is on column c and d)

=VLOOKUP(A1,C1:D2,2)

Ifs

=IF(A1=10000,10,0)+IF(A1=9000,9,0)

Answer 2

We can use LOOKUP with ranges:

=LOOKUP(A2,{0,90000,100000,110000,120000},{"0-89999","90000-99999","100000-109999","110000-119999","120000-Inf"})

I used lookup output as ranges to show what lookup is trying to do, in your case use below:

=LOOKUP(A2,{0,90000,100000,110000,120000},{8,9,10,11,12})