Vlookup WITHOUT Ascending Order (no, FALSE will not work_

Hi Rick

Care to provide the table of values and ratings with descending order. I assume that you don't have problems with ratings that are in an ascending order. I also assume that you have 2 separate tables and 2 separate series of scores for which you want to lookup the ratings.

Aladin

TRIPLE VLOOKUP, I'm betting, is what you need

RICK, I think you were talking about ascending and descending to better expose your problem. I believe you have just descending rates. So I will use the eample you used (instead of, alas, your actual rating-values).

Your example of scores and ratings: {1.95,5; 2.0,4; 2.05,3; 2.1,2; 2.15,1}
You want to assign 5 for 1.98 ... If so, I propose rewriting this ratings table as follows:

Enter in A from A1 on:

0
2
2.05
2.1
2.15

Enter in B from B1 on:

2
2.05
2.1
2.15
100 [ This is an arbitrarily big score ]

Enter in C from C1 on:

5
4
3
2
1

Name the range A1:C5 RATINGS via the Name Box.

I'll assume actual scores are in D from D1 on:

In E1 enter: =IF(AND(D1>=VLOOKUP(D1,RATINGS,1),D1<VLOOKUP(D1,RATINGS,2)),VLOOKUP(D1,RATINGS,3),"") 

[ Copy down as far as needed ]

Recall that RATINGS is the range $A$1:$C$5.

I reckon you can rebuild your table the way that the above method requires.

Note. This formula is copy-righted.

Is this what you are looking for?

Aladin

Re: TRIPLE VLOOKUP, I'm betting, is what you need (again that pesky script)

That pesky script is messing things up when use a less-than symbol. You want to assign 5 for 1.98 ... If so, I propose rewriting this ratings table as follows: 2 2.05 2.1 2.15 2.05 2.1 2.15 100 [ This is an arbitrarily big score ] 4 3 2 1

Substitute the appropriate symbol for LessThan.

Re: TRIPLE VLOOKUP, I'm betting, is what you need (again that pesky script)

G'Morning Aladin!

Thanks for the reply. I have not had a chance to look closely at your script yet, but will later in the AM. (Groan....I have a 2 hr meeting starting in aprox. 35 seconds!)

Thx!

Rick

Re: Reviewed Formula

G'Afternoon Aladin,

Whew! Another long meeting with TONS of action items. Oh well.....

I had a chance to review your suggestion and wanted to thank you for your time and effort. Indeed, this will work. If fact, it is very close, but much clearner, than a solution that I came up with last night after taking a brief break. What I did was use the AND statement in five different cells titled 1 - 5 combined with an IF comparing the result of the AND to TRUE. Where TRUE existed I set it to the value of the column heading (1,2,3,4 or 5) where it was FALSE I set the value to 0. I then summed across the colunns. Of course I would only get one value and so the sum would be my score of either 1,2,3,4 or 5.

Messy, but it worked! Yours needless to say is more efficient.

By the way, I also had to compute a "fractional value" to add to the rating, i.e. in the range of values between a 3 and a 4 was the rating a 3.1, 3.01, 3.47? etc. To do this I expanded the vlookup tables to include the variance between the ranges, for example the variance between the start of rating 4 and rating 3 in your example is .05, looked up the variance for the awarded rating and computed the variance between the actual value and the start of the next rating and divided the result. This gave me the fraction to add to the whole number rating.

I had to go to through the above, because for !35! metrics that I set up the variance between the ratings was not always the same. In fact there wasn't even the same variance between the RATINGS within a metric most of the time!

Well, again thanks for your help! IF in the future I have to either update this spreadsheet OF encounter this issue again I will look to your's as the better solution!

Thx!

Rick