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

Try this array formula

=INDEX(GRADES!C:C,MAX(IF((GRADES!A1:A100<=-17)*(GRADES!B1:B100=-17),ROW(GRADES!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

See if this works for you. Assume the following table and D2 is the handicap
that you put in and E2 is the result of the lookup:

A B C D E
1
2 -30 -10 A -17 A
3 -9 0 B
4 1 10 C
5 11 20 D
6 21 30 E
7 31 40 F
8 41 50 G


In E2, paste =LOOKUP(D2,A2:B8,C2:C8)
change the range as required
If it works, click yes below.

"DubboPete" wrote:

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
.

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

On Mar 15, 8:00*pm, ck13 wrote:
See if this works for you. Assume the following table and D2 is the handicap
that you put in and E2 is the result of the lookup:

* * A * * * * * *B * * * * * * C * * * D * * *E
1
2 *-30 *-10 * * A * * *-17 * * A
3 *-9 * 0 * * * B
4 *1 * *10 * * *C
5 *11 * 20 * * *D
6 *21 * 30 * * *E
7 *31 * 40 * * *F
8 *41 * 50 * * *G

In E2, paste =LOOKUP(D2,A2:B8,C2:C8)
change the range as required
If it works, click yes below.



"DubboPete" wrote:
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 -

ck13, didn't quite get to your answer, becos p45cal came through with
a solution, but thanks for the input!

Pete

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

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