Hi

I'm sure this will be fairly easy for someone, but I'm stumped.

I have a score being input into cell N24. The format is number, no
decimal places. On the same sheet I also have the values (W27-W31)
99,98,97,96,20, and next to them (col X) a score 4,3,2,1,1. These 10
cells are a names range, called "PM6Table". Scores should always be
above 96, so the 20 is there to give a lowest figure (I could use 1, I
guess, but it doesn't affect my problem).

So then I have in cell Y27, "=VLOOKUP(N24,PM6Table,2,TRUE)"

Here's the problem. If I put a score of 96 in cell N24, I get the
correct result = 2. If I put in 99, however, I get a return of
"1" (should be 4); if I put in a score of any other number, I get "N/
A". The only return that is correctly produced is the 96 = 2.

Can anyone tell me why that should be? This format is used in 5 other
parts on the same sheet, and works perfectly. The formats are the same
throughout the sheet, and I've checked the VBE to see if there was
anything in there that might do it, but nada.

Any help would be very greatly appreciated.

Thanks

Steve

I'm not sure why this formula gives you 2 and that's a correct answer, but
try using false in your vlookup instead of true. Hope this helps.

-Chad

"Steve" wrote:

Hi

I'm sure this will be fairly easy for someone, but I'm stumped.

I have a score being input into cell N24. The format is number, no
decimal places. On the same sheet I also have the values (W27-W31)
99,98,97,96,20, and next to them (col X) a score 4,3,2,1,1. These 10
cells are a names range, called "PM6Table". Scores should always be
above 96, so the 20 is there to give a lowest figure (I could use 1, I
guess, but it doesn't affect my problem).

So then I have in cell Y27, "=VLOOKUP(N24,PM6Table,2,TRUE)"

Here's the problem. If I put a score of 96 in cell N24, I get the
correct result = 2. If I put in 99, however, I get a return of
"1" (should be 4); if I put in a score of any other number, I get "N/
A". The only return that is correctly produced is the 96 = 2.

Can anyone tell me why that should be? This format is used in 5 other
parts on the same sheet, and works perfectly. The formats are the same
throughout the sheet, and I've checked the VBE to see if there was
anything in there that might do it, but nada.

Any help would be very greatly appreciated.

Thanks

Steve

Hi Steve

It's because your table is not sorted ascending.
If you add the 4th parameter as False or 0, then provided the value
exists it will return the correct number.
=VLOOKUP(N24,PM6Table,2,0)

As you say all of your values should be above 96 anyway, you could do it
without any lookup table.

=MAX(1,N24-95)

--
Regards

Roger Govier


"Steve" wrote in message
oups.com...
Hi

I'm sure this will be fairly easy for someone, but I'm stumped.

I have a score being input into cell N24. The format is number, no
decimal places. On the same sheet I also have the values (W27-W31)
99,98,97,96,20, and next to them (col X) a score 4,3,2,1,1. These 10
cells are a names range, called "PM6Table". Scores should always be
above 96, so the 20 is there to give a lowest figure (I could use 1, I
guess, but it doesn't affect my problem).

So then I have in cell Y27, "=VLOOKUP(N24,PM6Table,2,TRUE)"

Here's the problem. If I put a score of 96 in cell N24, I get the
correct result = 2. If I put in 99, however, I get a return of
"1" (should be 4); if I put in a score of any other number, I get "N/
A". The only return that is correctly produced is the 96 = 2.

Can anyone tell me why that should be? This format is used in 5 other
parts on the same sheet, and works perfectly. The formats are the same
throughout the sheet, and I've checked the VBE to see if there was
anything in there that might do it, but nada.

Any help would be very greatly appreciated.

Thanks

Steve