Lookup() problem

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

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!
==============================================


"Wayman Bell" <[email protected]> wrote in message
news:[email protected]...
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

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" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>

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

"RagDyeR" <[email protected]> wrote in message
news:[email protected]...
> 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!
> ==============================================
>
>
> "Wayman Bell" <[email protected]> wrote in message
> news:[email protected]...
> 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,

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

"Bob Phillips" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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
>>
>>
>
>

Thanks for the feed-back.
--

Regards,

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

"Wayman Bell" <[email protected]> wrote in message
news:[email protected]...
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

"RagDyeR" <[email protected]> wrote in message
news:[email protected]...
> 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!
> ==============================================
>
>
> "Wayman Bell" <[email protected]> wrote in message
> news:[email protected]...
> 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
>
>
>

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

--

HTH

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


"Wayman Bell" <[email protected]> wrote in message
news:[email protected]...
> 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
>
> "Bob Phillips" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > news:[email protected]...
> >> 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
> >>
> >>
> >
> >
>
>

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.

=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" <[email protected]> wrote in message
news:[email protected]...
> 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.
>
>

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)






"Bob Phillips" <[email protected]> wrote in message
news:[email protected]...
> =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" <[email protected]> wrote in message
> news:[email protected]...
> > 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.
> >
> >
>
>

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!
==============================================

"Agung Widodo" <[email protected]> wrote in message
news:[email protected]...
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)






"Bob Phillips" <[email protected]> wrote in message
news:[email protected]...
> =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" <[email protected]> wrote in message
> news:[email protected]...
> > 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.
> >
> >
>
>

I am having similar trouble with a lookup function.
My problem is that I can't sort the vector in ascending order. I am trying to get data from a given date. This may get even more complicated when two of the same dates appear.
But if anyone can point me in the right direction I would be grateful.

e.g.
Column A Column B
33 11/02/2005
24 20/12/2146
52 20/02/2005
154 03/03/2005
23 13-Mar

So if I lookup 03/03/2005 I should get 154.

I'm also finding excel is making things complicated when entering dates. I am trying to get every cell formatted the same, but it is causing havoc on my spreadsheet.