# Thread: IF formula based on a value between 2 numbers, and also matching specific criteria Thanks:  1 Post #5325928 (1) Likes: 0

1. ## IF formula based on a value between 2 numbers, and also matching specific criteria

Hello,

I have a group of employees that have results for various metrics. I want to assign a Score for each of these metrics based on specific values that their result is between. For example, one metric might be quality, and I might have scores 1-5 where 5 is anything greater than 98%, 4 is anything greater than or equal to 94% and less than 98%, 3 is anything greater than or equal to 91% and less than 94% and so on.

Additionally, depending on how long that employee has been working, the values for each score might change. For example the above scores might be for employees that have been here longer than 1 month. Employees that have been here less than 1 month might have the following: 5 is anything greater than 95%, 4 is anything greater than or equal to 90% and less than 95%, and so on.

I track each metric for each employee monthly, and each month I assign them a setting based on how long they've been an employee. So I have one Excel row for July 2019 for Jane Smith who is New Hire Month 1 - Quality, and a 2nd row for August 2019 for Jane Smith who is New Hire Month 2 - Quality.

I'm trying set up an excel sheet for another group to manage, where they have to do as little manual entry as possible. I'd like for them to simply be able to select New Hire Month 1 or Month 2, enter the Quality result for that agent that month, and have a Score field that can look up the appropriate score using the appropriate ranges based on the New Hire Month 1 or Month 2 selection. I'm failing miserably at accomplishing this any help is appreciated!

2. ## Re: IF formula based on a value between 2 numbers, and also matching specific criteria

Hi,

Try this:

ABCDEFGHIJKLMNO
1012345
2Month 10%75%80%85%90%95%NameMonthAssignMetric ResultScore
3Month 20%85%88%91%94%98%Jane SmithJulyNew Hire Month 184%2
4Jane SmithAugustMonth 284%0

Sheet1

Worksheet Formulas
CellFormula
O3=LOOKUP(N3,(INDEX(\$B\$2:\$G\$3,MATCH(RIGHT(M3,7),\$A\$2:\$A\$3,0),0)),\$B\$1:\$G\$1)
O4=LOOKUP(N4,(INDEX(\$B\$2:\$G\$3,MATCH(RIGHT(M4,7),\$A\$2:\$A\$3,0),0)),\$B\$1:\$G\$1)

3. ## Re: IF formula based on a value between 2 numbers, and also matching specific criteria

This is exactly what I needed, thank you!

I have one new question now....for one of my metrics (efficiency) a higher score is given for a lower time (example below), so this formula isn't assigning the proper score for this metric. Thoughts on how can I adjust it so that for the majority of my metrics a higher score is given for a higher result, but for this one metric a higher score is given for a lower result?

 0 1 2 3 4 5 Month 1 12:00 7:00 6:30 6:00 5:30 5:00 Month 2 12:00 6:00 5:30 5:00 4:30 4:00

4. ## Re: IF formula based on a value between 2 numbers, and also matching specific criteria

Hi,

Something like this:
ABCDEFGHIJKLM
1012345NameMonthAssignMetric ResultScore
2Month 10%75%80%85%90%95%Jane SmithJulyNew Hire Month 184%2
3Month 20%85%88%91%94%98%Jane SmithAugustMonth 284%0
4
5
6
7543210NameMonthAssignMetric ResultScore
8Month 100:0005:0005:3006:0006:3007:00Jane SmithJulyNew Hire Month 105:154
9Month 200:0004:0004:3005:0005:3006:00Jane SmithAugustMonth 206:150

Sheet2

Worksheet Formulas
CellFormula
M2=LOOKUP(L2,(INDEX(\$B\$2:\$G\$3,MATCH(RIGHT(K2,7),\$A\$2:\$A\$3,0),0)),\$B\$1:\$G\$1)
M3=LOOKUP(L3,(INDEX(\$B\$2:\$G\$3,MATCH(RIGHT(K3,7),\$A\$2:\$A\$3,0),0)),\$B\$1:\$G\$1)
M8=LOOKUP(L8,(INDEX(\$B\$8:\$G\$9,MATCH(RIGHT(K8,7),\$A\$8:\$A\$9,0),0)),\$B\$7:\$G\$7)
M9=LOOKUP(L9,(INDEX(\$B\$8:\$G\$9,MATCH(RIGHT(K9,7),\$A\$8:\$A\$9,0),0)),\$B\$7:\$G\$7)