Vlookup WITHOUT Ascending Order (no, FALSE will not work_
Posted by Rick on February 28, 2001 12:28 PM
I am (trying) to use the Vlookup function to return values for a scorecard application. The scorecard compares actuals against a 1-5 rating scale. The problem is that it works fine for those metrics that are in ascending order (i.e. 10 = 1 15 = 2 20 = 3 etc.) BUT for thosse metrics with desending order (i.e. 5 = 1.95; 4 = 2.0; 3 = 2.05; 2 = 2.1; 1 = 2.15) I can not get a correct value.
For example in the following range
I would want a rating of 3 to be returned for a score of 80.0 as a 4 is not earned until the actual value FALLS to 79.96.
I know WHY this is, I was just hopeful that someone here has encountered the same "opportunity" and resolved it, either with a trick with vlookup or some other function.
Any help MOST welcome!
PS: Please post answer here - my email is down!