Prev: clean up code a little
Next: Excel
From: Kiran on 7 Apr 2010 10:48
Hi,

I need to lookup for a Loan Number from another sheet. However the problem
is sometimes one digit would be added to the end in the other worksheet. Is
there a way to find match for this digit.For the digits in below list I need
to get the match as true for 12345 and 12346 as there is 123451 and 123462 in
2nd Sheet.

List in 1st Sheet
Loan#
12345
12346
12347


List in 2nd Sheet
Loan#
123451
123462
234728


From: Stefi on 7 Apr 2010 11:21
=MATCH(A2&"*";Sheet2!A:A;0)

Format both column like text!

--
Regards!
Stefi



„Kiran” ezt írta:

> Hi,
>
> I need to lookup for a Loan Number from another sheet. However the problem
> is sometimes one digit would be added to the end in the other worksheet. Is
> there a way to find match for this digit.For the digits in below list I need
> to get the match as true for 12345 and 12346 as there is 123451 and 123462 in
> 2nd Sheet.
>
> List in 1st Sheet
> Loan#
> 12345
> 12346
> 12347
>
>
> List in 2nd Sheet
> Loan#
> 123451
> 123462
> 234728
>
>
From: Kiran on 8 Apr 2010 06:06
Thanks for your help. I also need to know If there is a extra digit in Sheet1
and one digit less in Sheet 2 can we find the match.

Kiran

"Stefi" wrote:

> =MATCH(A2&"*";Sheet2!A:A;0)
>
> Format both column like text!
>
> --
> Regards!
> Stefi
>
>
>
> „Kiran” ezt írta:
>
> > Hi,
> >
> > I need to lookup for a Loan Number from another sheet. However the problem
> > is sometimes one digit would be added to the end in the other worksheet. Is
> > there a way to find match for this digit.For the digits in below list I need
> > to get the match as true for 12345 and 12346 as there is 123451 and 123462 in
> > 2nd Sheet.
> >
> > List in 1st Sheet
> > Loan#
> > 12345
> > 12346
> > 12347
> >
> >
> > List in 2nd Sheet
> > Loan#
> > 123451
> > 123462
> > 234728
> >
> >
From: Stefi on 9 Apr 2010 06:55
Maybe you meant this:
=MATCH(LEFT(A2,LEN(A2)-1),Sheet2!A:A,0)

--
Regards!
Stefi



„Kiran” ezt írta:

> Thanks for your help. I also need to know If there is a extra digit in Sheet1
> and one digit less in Sheet 2 can we find the match.
>
> Kiran
>
> "Stefi" wrote:
>
> > =MATCH(A2&"*";Sheet2!A:A;0)
> >
> > Format both column like text!
> >
> > --
> > Regards!
> > Stefi
> >
> >
> >
> > „Kiran” ezt írta:
> >
> > > Hi,
> > >
> > > I need to lookup for a Loan Number from another sheet. However the problem
> > > is sometimes one digit would be added to the end in the other worksheet. Is
> > > there a way to find match for this digit.For the digits in below list I need
> > > to get the match as true for 12345 and 12346 as there is 123451 and 123462 in
> > > 2nd Sheet.
> > >
> > > List in 1st Sheet
> > > Loan#
> > > 12345
> > > 12346
> > > 12347
> > >
> > >
> > > List in 2nd Sheet
> > > Loan#
> > > 123451
> > > 123462
> > > 234728
> > >
> > >
 | 
Pages: 1
Prev: clean up code a little
Next: Excel