# LOOKUP formula returns only negatives

#### Keyrookie

##### New Member
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!!

### 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
use VLOOKUP with the last argument equal to FALSE:

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

#### Keyrookie

##### New Member
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
Try...

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

Hope this helps!

#### Keyrookie

##### New Member
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
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...

Replies
5
Views
465
Replies
0
Views
483
Replies
2
Views
273
Replies
9
Views
560
Replies
9
Views
142