Complex VLOOKUP that would return certain Percentage as a result

[privxls]

Good day MrExcel

First off, thank you for taking this time to open and read up until this line... now my problem st

I previously posted a question similar to this here in MrExcel and it was resolved by user <strike>AliGW. </strike>Now, I have encountered a project which has a different requirement however almost the same result should be expected.

Please view the table below:

ACW: >180 means that agent is in the Low level
ACW: =180.1 to 60 means that agent is in the medium level
ACW: <15 means that agent is in high level.

*** Same goes for Talk Time and Hold Time ***

What I aim to achieve in this is to get the certain percentage of the agent's score which will base the result from the 'Score Reference' table.

For an instance, if the agent gets a score of 180 or below for ACW, it will fall under low. Or if the agent gets a score of 98 it will fall under medium and so and so forth.

My problem now, is that, I have these so called 'Weight' per Score Item (like ACW and Hold Time makes up 30% each and Talk Time makes up 40% of the scorecard) -- how do I go properly manage this table in order for my VLOOKUP to work (as I was planning to make it like this:

	A	B	C	D	E	F	G
Score	ACW
John

<tbody>
[TD="align: center"]7[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"](return value here)[/TD]
[TD="align: right"]98[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]>180[/TD]
[TD="align: right"]0% - 79.99%[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]=60[/TD]
[TD="align: right"]80%[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]<15[/TD]
[TD="align: right"]100%[/TD]

[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

​

[TABLE="width: 882"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 874"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]B8[/TH]
[TD="align: left"]=VLOOKUP(C8,$F$8:$G$10,2,)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



Or what formula should I be using?

Kind regards,
Priv

 

[StephenCrump]

Q1: Based on your table, there are four possibilities, not three, e.g. for ACW you can have:

180 <= ACW
60 <= ACW < 180
15 <= ACW < 60
ACW < 15

Is this Low, Medium, High and Very High perhaps?

Q2: Suppose you rate ACW Low, Hold Time Medium and Talk Time High.

What answer do you want to see, given your weightings of 30%, 30% and 40%?

What about other possibilities, e.g. ACW Low, Hold Time Low and Talk Time High. What should the answer be?