LOOKUP Error

[ssjerauld]

Hello Again,
I am stumbling through what I thought to be a simple LOOKUP function,but am having some trouble with 1 number. I reference 2 columns H - Employee Numbers and I - Employee Name. When employye Number is enrered into B3 Employee's Name comes up in C4. Some numbers start with 0 and I had trouble with these at first, but corrected that with cell formatting, which is CUSTOM-000. The name that is giving trouble is 119 whenever this number is entered it gives me the name in the cell above the correct one.??? My function is =LOOKUP(B3,H6:H39,I6:I39) any help would be great.
THANKS!

[s-o-s]

On 2002-05-03 14:57, ssjerauld wrote:
Hello Again,
I am stumbling through what I thought to be a simple LOOKUP function,but am having some trouble with 1 number. I reference 2 columns H - Employee Numbers and I - Employee Name. When employye Number is enrered into B3 Employee's Name comes up in C4. Some numbers start with 0 and I had trouble with these at first, but corrected that with cell formatting, which is CUSTOM-000. The name that is giving trouble is 119 whenever this number is entered it gives me the name in the cell above the correct one.??? My function is =LOOKUP(B3,H6:H39,I6:I39) any help would be great.
THANKS!

I think from you message When you say you are using lookup, you are actually using vlookup in which case the cause may be the following:

Your list is probably not quite incremental in which case it picks up the nearest in incremental blocks add the following bit and Excel will only accept Exact matches.

=VLOOKUP(B3,H6:H39,I6:I39, FALSE)

If this hasn't done it let us know.

Hope This Helps

[Aladin Akyurek]

On 2002-05-03 14:57, ssjerauld wrote:
Hello Again,
I am stumbling through what I thought to be a simple LOOKUP function,but am having some trouble with 1 number. I reference 2 columns H - Employee Numbers and I - Employee Name. When employye Number is enrered into B3 Employee's Name comes up in C4. Some numbers start with 0 and I had trouble with these at first, but corrected that with cell formatting, which is CUSTOM-000. The name that is giving trouble is 119 whenever this number is entered it gives me the name in the cell above the correct one.??? My function is =LOOKUP(B3,H6:H39,I6:I39) any help would be great.
THANKS!

If you mean by corrected that with cell formatting, which is CUSTOM-000, corrected it via Format|Cells|Custom, your problem is not over.

The underlying format of B3 must be the same as that of the lookup range H6:H39. You need to resolve that first. Moreover, The values in H6:H39 must be in ascending order: ...,-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE; otherwise, LOOKUP may not give the correct value.

An alternative would be:

=INDEX(I6:I39,MATCH(B3,H6:H39,0)

which also requires that the format issue is resolved.

[ This Message was edited by: Aladin Akyurek on 2002-05-03 16:21 ]

[ssjerauld]

The INDEX function worked exactly as mine did previously, all numbers work fine except for 119. Both B3 and H6:H39 are formatted the same.
Scott J.

[ssjerauld]

Addition to: all numbers in H6:H39 are in ascending order, but 119 is the first true 3 digit numer, all previous numbers begin with 0. Any Help??

[Aladin Akyurek]

On 2002-05-03 15:55, ssjerauld wrote:
Addition to: all numbers in H6:H39 are in ascending order, but 119 is the first true 3 digit numer, all previous numbers begin with 0. Any Help??

Yep. 119 has as underlying format number, the rest not. Make all text and be done with it.

You can make that 119 if it's a value that shows up in B3 text by using

B3&""

Aladin

[ This Message was edited by: Aladin Akyurek on 2002-05-03 16:22 ]