Return Value Based on a Range & Constant

Question

I would appreciate your assistance.

I need to return a value based on 2 criteria - one of which looks at a range.

So, for example, Bob is in the role for 8 months and has opened up 40 new accounts. His rating should be a 3 (row 4, column D). Jane is in the role 10 months and opened up 40 new accounts. Her rating is only a 2 (row 13, column D).

How can I do this? I know how to do a simple vlookup if I wanted to use just the number of months in the role. I know how to do a lookup based on 2 criteria. I also know the formula to do a vlookup based on a range. But, I can't figure out how to combine them into one.

This scale changes every year, so I don't want to list 8-1, 8-2, 8-3 all the way to 900. I actually have the whole year set-up that once I determine the full year scale, the rest of the scale adjusts automatically.

Thank you!

Cathy

Answer 1

Hi Vijay,

IMO you better write your formula as:

=IFERROR(SUMPRODUCT((('KPI Ratings'!$A$2:$A$100=F2)/(F2<>""))*('KPI Ratings'!$B$2:$B$100<=G2)*('KPI Ratings'!$C$2:$C$100>=G2)*('KPI Ratings'!$D$2:$D$100)),"")

Answer 2

If your first table is in sheet1, put following formula and drag down -

=SUMPRODUCT((Sheet1!$A$2:$A$1000=F53)*(Sheet1!$B$2:$B$1000<=I53)*(Sheet1!$C$2:$C$1000>=I53)*(Sheet1!$D$2:$D$1000))

Answer 3

Thank you and Vijay! This is exactly what I was looking for.

Cathy

Answer 4

I apologize that I wasn't as clear as I thought.

So, let's start over.

On Sheet1, I have the following, which has the names, when they started in the role, number of months in the role & the number of accounts they have opened up:

I need a formula for column J (YTD KPI Ranking) that looks at column F (the number of months) and column I (Total Accts) and based on those 2 factors, returns a value from the table on Tab 2 (see above).

So, the formula should first look at the number of months the person has been in the position. Then, look at the number of accounts opened and using the range/scale (in Columns B & C in the above Tab 2 chart), return the value/KPI Ranking. Here are the expected results:

So Bob & Jane both opened 40 accounts. But since Jane has been around 2 months longer, her rating isn't as high as Bob's. To be rated a "3" she had to open 44-55 accounts in 10 months.

I know that if I wanted to just look up the KPI Ranking/value based on new accounts, I could use formula to look up based on the range/scale:

=VLOOKUP($I53,'KPI Ratings'!$B:$D,3)

The problem is the range changes based on the number of months in the position. The annual goal is 48. But, if you are in the role only 8 months, you aren't expected to open the same number since you weren't around for 4 of those months. So, I need to be able to scale back from a full year goal according to the number of months they are in the position.

If I need to change the format of the table (the chart in the original post), I can. I'm just not sure what it should look like.

Thank you so much!!