Having strange problem with LOOKUP function

[littlebob]

HI,
I'm having a strange problem. I have 2 worksheets that are used to track our golf group's scores. The roster worksheet is used to store player's current quota of points. When I started using this there were 54 players in our group. Now there are only 50, so I deleted 4. Now any player who was initially below number 50 in the roster will not have his quota found. Here is the formula:

=IF(B6=" "," ",LOOKUP(B6,[ROSTER.XLSM]SHEET1!$B$3:$B$77,[ROSTER.XLSM]SHEET1!$C$3:$C$77))

Cell B6 in this case would contain the player's name, and
in the roster worksheet the player's name is in column B and the quota is in column C. I just cannot figure why this works until I remove a player. If I go to the bottom of the roster list and put in any players (i.e. zzz), then the formula works again. Any ideas?
Thanks in advance.

 

[Andrew Poulsom]

Is your list of players sorted ascending as LOOKUP expects?

 

[T. Valko]

HI,
I'm having a strange problem. I have 2 worksheets that are used to track our golf group's scores. The roster worksheet is used to store player's current quota of points. When I started using this there were 54 players in our group. Now there are only 50, so I deleted 4. Now any player who was initially below number 50 in the roster will not have his quota found. Here is the formula:

=IF(B6=" "," ",LOOKUP(B6,[ROSTER.XLSM]SHEET1!$B$3:$B$77,[ROSTER.XLSM]SHEET1!$C$3:$C$77))

Cell B6 in this case would contain the player's name, and
in the roster worksheet the player's name is in column B and the quota is in column C. I just cannot figure why this works until I remove a player. If I go to the bottom of the roster list and put in any players (i.e. zzz), then the formula works again. Any ideas?
Thanks in advance.

Try using VLOOKUP instead.

=IF(B6=" "," ",VLOOKUP(B6,[ROSTER.XLSM]SHEET1!$B$3:$C$77,2,0))

On a side note...

It's better to return a blank rather than a space character:

=IF(B6="","",VLOOKUP(B6,[ROSTER.XLSM]SHEET1!$B$3:$C$77,2,0))

 

[crook_101]

Hi,

Are the names the same, try:

=IF(B6="","",VLOOKUP(B6,[ROSTER.XLSM]SHEET1!$B$3:$C$77),2,0)

if I picture your layout correctly....

Beaten to if by Biff

HTH
Ian

 

[littlebob]

Yes, the table of players is sorted. ALso, I did try VLOOKUP with the same results. After posting, I tried something new. Before, when I removed a player, I changed his name to a string of ZZZ, then sorted the list to get them to the bopttom of the list. (This is because I need to keep the total length of the roster sheet the same due to absolute addresses I use in some of the formulas.) Then I changed the ZZZ to spaces. I just tried a different approach. I deleted the 4 rows, then added 4 rows at the bottom of the list. This time everything works. I'll use this method of removing players in the future even though it requires more work because I need to copy formulas. But I would sure like to understand why my original approach didn't work.

Thanks.

 

[T. Valko]

Yes, the table of players is sorted. ALso, I did try VLOOKUP with the same results. After posting, I tried something new. Before, when I removed a player, I changed his name to a string of ZZZ, then sorted the list to get them to the bopttom of the list. (This is because I need to keep the total length of the roster sheet the same due to absolute addresses I use in some of the formulas.) Then I changed the ZZZ to spaces. I just tried a different approach. I deleted the 4 rows, then added 4 rows at the bottom of the list. This time everything works. I'll use this method of removing players in the future even though it requires more work because I need to copy formulas. But I would sure like to understand why my original approach didn't work.

Thanks.

When you use LOOKUP and the lookup_vector IS sorted in ascending order, if there is not an EXACT match of the lookup_value then the function will match the closest value that is less than the lookup_value.

For example:

A..1
B..2
C..3
Z..4

=LOOKUP("D",A1:B4)

Returns the correct result of 3.

There is not an exact match of D and C is the closest value that is less than than D.