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

Let's assume that value of 8 (role duration) in F2 and G2 has 40 (number of new accounts). You can use following formula -

=SUMPRODUCT((A2:A20=F2)*(B2:B20<=G2)*(C2:C20>=G2)*(D2:D20))

Answer 4

Hi,

I am not clear with your question.  Please clarify your inputs and expected result.  Also, there is no column showing Names in your screenshot.

Answer 5

I'm not sure I quite understand.

I think what you are asking to do is 

First Lookup the months as one lookup then

Next Lookup the number of new accounts 

And come up with the KPI rating from those two individual lookup results?

Also, I see 2 columns of New Account values - which are we supposed to be looking at (B or C) to find the value in the lookup of new accounts.

By the way - check your post for accuracy:  you say Jane has been in her role for 10 months and has 40 (???) new accounts?  Should that be 20 or 44?

Personally, at this point I'm thinking that in the long run the easiest thing to do is create that huge table you're dreading on setting up and use either a SUMIFS() or older SUMPRODUCT() formula to pull data from it.

It might help if you can show us the 'scale' chart you mention and explain how that works.  Best might be if you can upload a sample file to a file sharing site or to your OneDrive and share it and provide a link to it in a posting here.