# A different kind of VLookup

D

#### 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

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

thanks in anticipation

Pete

Try this array formula

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.

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
better illustrate your problems and get help directly with them. Also if
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

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

Try this array formula

--

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

A2 B2 C2
-30 -10 A
Player A's handicap of -17 fits in this bracket or range, therefore

Player B is on 12

A5 B5 C5
10 19 D
Player B's handicap of 12 fits in this bracket or range, therefore

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

make sense?

cheers
Pete

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:
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

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:

(this didn't:

(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

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

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

--

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

A2 B2 C2
-30 -10 A
Player A's handicap of -17 fits in this bracket or range, therefore

Player B is on 12

A5 B5 C5
10 19 D
Player B's handicap of 12 fits in this bracket or range, therefore

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

make sense?

cheers
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

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

A2 B2 C2
-30 -10   A
Player A's handicap of -17 fits in this bracket or range, therefore

Player B is on 12

A5 B5 C5
10  19   D
Player B's handicap of 12 fits in this bracket or range, therefore

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