Lookup() problem

[Wayman Bell]

I set up two lists, 200 employees names and 200 employee numbers. A couple
of the numbers match the correct names but the rest of the numbers all pull
the same employee name.

=LOOKUP(C4,List!C4:C204,List!B4:B204)

The first time I tried it seemed to pull the correct matching names and
numbers then kabui it gets stuck. I tried deleting the entire lists cells
and all and starting over but same thing. Anyone ever run into this before?

Wayman

 

[RagDyeR]

Don't forget!
When using Lookup(), the vector (second argument) must be sorted in
ascending order.

If it's not, you'll get what you called "kabui".

Probably better to use Vlookup(), but since you're lookup column is on the
right, try this:

=INDEX(List!B4:B204,MATCH(C4,List!C4:C204,0))
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


I set up two lists, 200 employees names and 200 employee numbers. A couple
of the numbers match the correct names but the rest of the numbers all pull
the same employee name.

=LOOKUP(C4,List!C4:C204,List!B4:B204)

The first time I tried it seemed to pull the correct matching names and
numbers then kabui it gets stuck. I tried deleting the entire lists cells
and all and starting over but same thing. Anyone ever run into this before?

Wayman

 

[Bob Phillips]

Your lookup data has to be in ascending order. Is this the case?

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Wayman Bell]

Thanks,
=INDEX(List!B4:B204,MATCH(C4,List!C4:C204,0)) seems to work great. I also
sorted the Data ascending and that seemed to make my old formula work okay
then I sorted descending and tried your formula and it appears to work even
if the data is not sorted.

I will be sending my form out to a number of people in the field. They will
have to enter their own list and I am sure if the data has to be sorted some
of them will not be able to do that.

Thanks, Wayman

 

[Wayman Bell]

Bob,

No it was not sorted. I will be sending the form out to a number of agents
in the field and I doubt it they will all be able to sort their lists. I
tried this formula from RD and it seems to work even with the data unsorted.

=INDEX(List!B4:B204,MATCH(C4,List!C4:C204,0))

Thanks for the information, I guess sorting is so basic I must have missed
that.

WB

 

[RagDyeR]

Thanks for the feed-back.
--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------

Thanks,
=INDEX(List!B4:B204,MATCH(C4,List!C4:C204,0)) seems to work great. I also
sorted the Data ascending and that seemed to make my old formula work okay
then I sorted descending and tried your formula and it appears to work even
if the data is not sorted.

I will be sending my form out to a number of people in the field. They will
have to enter their own list and I am sure if the data has to be sorted some
of them will not be able to do that.

Thanks, Wayman

 

[Bob Phillips]

Yes, that variation works sorted or not as you note.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Agung Widodo]

I have 2 columns contains Idx No. and Leave Qty of employee.
Idx No. sorted in ascending order
In same work sheets I have another "2 columns contains Idx No. and Leave Qty
of employee".
In the second Leave Qty Row contains lookup formula refer by Idx.No.

My lookup formula returns the nearest value if number i look up is none in
1st Idx No.
Data has sorted by ascending order.

What formula i can use to return error ,0,or false if the value in Idx No.
nothing.

 

[Bob Phillips]

=IF(ISNA(VLOOKUP(A1,H1:J100,2,FALSE)),0,VLOOKUP(A1,H1:J100,2,FALSE))

assuming the columns are A:B and H:J

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Agung Widodo]

Thanks for your help but i can paste your formula.

A B H J
Idx Leave Qty. Idx Leave Qty.
412 0 412 0
540 2 419 ?
897 1 540 2


What formula i can use to return error ,0,or false if the value in Idx
No.nothing.
My formula in J2:
=LOOKUP(H2;A:A;B:B)

 

[RagDyeR]

Try this:

=IF(ISNA(MATCH(H2,$A$2:$A$100,0)),0,VLOOKUP(H2,$A$2:$B$100,2,0))

And copy down as needed.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

Thanks for your help but i can paste your formula.

A B H J
Idx Leave Qty. Idx Leave Qty.
412 0 412 0
540 2 419 ?
897 1 540 2


What formula i can use to return error ,0,or false if the value in Idx
No.nothing.
My formula in J2:
=LOOKUP(H2;A:A;B:B)