A different kind of VLookup

[DubboPete]

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

 

[teylyn]

Pete,

this might be much easier to solve if we could look at your workbook

HOW TO GET FURTHER HELP WITH A WORKBOOK
For further help with it why not join our forums (shown i
the link below). It's completely free. If you do join you will have th
opportunity to add attachments to your posts so you can add workbooks t
better illustrate your problems and get help directly with them. Also i
you do join please post in this thread (link found below) so that peopl
who have been following or helping with this query can continue to d
so.

cheers, teylyn

'The Code Cage - Microsoft Office Help - Microsoft Office Discussion
(http://www.thecodecage.com)

 

[Bob Phillips]

Try this array formula

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

 

[p45cal]

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

This is an easy one.
Ditch the second column, you only need the
-30
-9
1
11
21
(the thresholds). For this example, put them in cells A2:A6. (It is
important that they are in ascendng order)(I haven't used A2:A8 because
you've only supplied values to fill A2:A6)
Now put A,B,C,D,E iin B2:B6
Put your -17 (or any other value in any cell on the other sheet, I used
B2), now in cell C2 of that other sheet put in this formula:
=VLOOKUP(B2,GRADES!$A$2:$B$6,2)
Now change the value in cell B2 to test the result in C2.

Is this what you were looking for?

 

[ck13]

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]

Pete,

this might be much easier to solve if we could look at your workbook

HOW TO GET FURTHER HELP WITH A WORKBOOK
For further help with it why not join our forums (shown in
the link below). It's completely free. If you do join you will have the
opportunity to add attachments to your posts so you can add workbooks to
better illustrate your problems and get help directly with them. Also if
you do join please post in this thread (link found below) so that people
who have been following or helping with this query can continue to do
so.

cheers, teylyn

'The Code Cage - Microsoft Office Help - Microsoft Office Discussion'
(http://www.thecodecage.com)

--
teylyn

Teylyn  --  'teylyn.posterous.com' (http://teylyn.posterous.com)
------------------------------------------------------------------------
teylyn's Profile: 983
View this thread:http://www.thecodecage.com/forumz/showthread.php?t=187523

http://www.thecodecage.com/forumz/chat.php

Teylyn,

Much as I appreciate your invitation, I'd rather stay here with this
group. It provides the solutions, evereyone gets to see them, and i
don't want to diversify.

Thanks anyway

 

[DubboPete]

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














- 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

 

[DubboPete]

DubboPete;671250 Wrote:







Hi all,









This is an easy one.
Ditch the second column, you only need the
-30
-9
1
11
21
(the thresholds). For this example, put them in cells A2:A6. (It is
important that they are in ascendng order)(I haven't used A2:A8 because
you've only supplied values to fill A2:A6)
Now put A,B,C,D,E iin B2:B6
Put your -17 (or any other value in any cell on the other sheet, I used
B2), now in cell C2 of that other sheet put in this formula:
=VLOOKUP(B2,GRADES!$A$2:$B$6,2)
Now change the value in cell B2 to test the result in C2.

Is this what you were looking for?

--
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: 558
View this thread:http://www.thecodecage.com/forumz/showthread.php?t=187523

http://www.thecodecage.com/forumz/chat.php- Hide quoted text -

- Show quoted text -

Yep, but with a slight change to the formula, which helped me get the
end result!

This worked:

=VLOOKUP(C2,GRADES!A:B,2)

(this didn't:
(> =VLOOKUP(B2,GRADES!$A$2:$B$6,2)

(Notice I selected the whole columns, not just the range? and what I
was looking up, which I didn't explain enough in the first post )

and came up with the absolute values that I wanted. I have applied it
to the full sheet, double checked, and it works perfectly!

thanks mate
Pete

 

[DubboPete]

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.













- Show quoted text -

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

Pete

 

[Bob Phillips]

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

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














- 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

 

[DubboPete]

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





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