LOOKUP Error

ssjerauld

New Member
Joined
Mar 27, 2002
Messages
14
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!
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
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.

<MARQUEE/> :cool: Hope This Helps :cool: </MARQUEE>
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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??
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,941
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top