On Mar 15, 9:29*pm, "Bob Phillips" wrote:
Nonsense, I have tried it and it always comes back with a letter not a
number. If you array enter it it comes back with the correct number.

--

HTH

Bob

"DubboPete" wrote in message

...
On Mar 15, 7:47 pm, "Bob Phillips" wrote:





Try this array formula

=INDEX(GRADES!C:C,MAX(IF((GRADES!A1:A100<=-17)*(GRADES!B1:B100=-17),ROW(GR**ADES!A1:A100))))

--

HTH

Bob

"DubboPete" wrote in message

....

Hi all,

I have a column of cells that I want to VLookup the answer for. It's
for a snooker tournament. It could be for a golf tournament too!

I have a sheet (GRADES) with three columns. A2 to B8 contain values
that I want compared. In C2:C8 are the results of the comparison.

It's a rolling handicap down from A2 to A8, B2 to B8, and C2 to C8.
These are the values :

-30 -10 A
-9 0 B
1 10 C
11 20 D
21 30 E

If a player's handicap is -17 (between -30 and -10, (A2 and B2)), then
his grade is 'A' (C2)

I just cannot figure how to do the VLookup to display C2 in the other
sheet...

thanks in anticipation

Pete- Hide quoted text -

- Show quoted text -

Hi Bob,

Didn't 'quite' get the result I wanted LOL

It came back with a value of -10!

Here's another couple of examples to try and simplify what my result
should be.

Player A is on -17

Grades Sheet says in:
A2 B2 C2
-30 -10 * A
Player A's handicap of -17 fits in this bracket or range, therefore
he's 'A' grade

Player B is on 12

Grades Sheet says in:
A5 B5 C5
10 *19 * D
Player B's handicap of 12 fits in this bracket or range, therefore
he's 'D' grade

So, when the lookup works properly, my cells should show 'A' for
Player A, and 'D' for player B

make sense?

cheers
Pete- Hide quoted text -

- Show quoted text -

Bob,

Thanks for the nonsense comment?

quote 1:

<Nonsense, I have tried it and it always comes back with a letter not
a
number.

quote 2:

< If you array enter it it comes back with the correct number

What do I want, a letter or a number?

It's been answered already, let's just drop it at that - but once
again, thanks for your help

Pete