LOOKUP formula returns only negatives

Keyrookie

New Member
Joined
Oct 26, 2006
Messages
3
Hey all! I'm having trouble with a LOOKUP function. I have a range of numbers (Percentages in 1 column, Numbers in the adjacent right column) that go from 677 to -677. I am wanting to match the Percentage and return the Number in the column next to it (which is what LOOKUP is supposed to do). The problem I'm having is the formula is returning the lowest negative number (-677) even if the result should be a positive number. The only thing I can assume is that LOOKUP doesn't like negative numbers and will default to the lowest value in the range. What is the solution???

=IF(ISERROR(LOOKUP(V131,S133:T231,T133:T231)),"",LOOKUP(V131,S133:T231,T133:T231))

"S" column starts with .99 and goes down to .01
"T" column starts with 677 and decreases by 7 till 0, then continues with -7,-14, -21, etc. [50% (S column) = "0" (T column)]

Thanks for the help!!
 

Some videos you may like

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

lozzablake

Well-known Member
Joined
Dec 15, 2005
Messages
818
use VLOOKUP with the last argument equal to FALSE:

=VLOOKUP(V131,S133:T231,2,FALSE)
 

Keyrookie

New Member
Joined
Oct 26, 2006
Messages
3
Thank you lozzablake, but it still isn't working. Is this what you mean??

=IF(ISERROR(VLOOKUP(V131,S133:T231,T133:T231)),"",VLOOKUP(V131,S133:T231,T133:T231,2,FALSE))
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,379
Try...

=IF(ISNA(VLOOKUP(V131,S133:T231,2,0)),"",VLOOKUP(V131,S133:T231,2,0))

Hope this helps!
 

Keyrookie

New Member
Joined
Oct 26, 2006
Messages
3
YES! Thanks Domenic, that fixed the problem. I have another question, if that's all right? Is there a way to lock an Excel file so that it can only be used by one person and not shared? I'm wanting to sell the file but I have no idea on how to prevent it being shared after it's purchased.
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,379
YES! Thanks Domenic, that fixed the problem.

You're very welcome!

I have another question, if that's all right? Is there a way to lock an Excel file so that it can only be used by one person and not shared? I'm wanting to sell the file but I have no idea on how to prevent it being shared after it's purchased.

I don't know how that can be accomplished. However, others here on the board should be able to help...
 

Watch MrExcel Video

Forum statistics

Threads
1,114,037
Messages
5,545,665
Members
410,697
Latest member
srishtijain0708
Top