Looking for comparable data records between Sheet1 and Sheet2

In both sheet1 and sheet2 I have hundreds of records.
60% of the records in sheet1 are also in sheet2.
If I concatenate 2 of the fields "LastName" and "Amt",
can I insert a formula in a helper column (Sheet2)
that will search sheet1 and bring back a "yes" if found,
"no" if not...?
=if(sheet2!B13&G13 = vlookup(sheet1!$B$5:$K$200, "yes","no")???
ANy help appreciated..

Hi!

No need for concatenation and a helper. You can
concatenate the fields in the formula.

Assume the last name and amount are in the same columns in
Sheet1 as they are in Sheet2. Entered as an array:

=IF(ISNUMBER(MATCH(B13&G13,Sheet1!B1:B100&Sheet1!
G1:G100,0)),"Yes","No")

Biff

>-----Original Message-----
>In both sheet1 and sheet2 I have hundreds of records.
>60% of the records in sheet1 are also in sheet2.
>If I concatenate 2 of the fields "LastName" and "Amt",
>can I insert a formula in a helper column (Sheet2)
>that will search sheet1 and bring back a "yes" if found,
>"no" if not...?
>=if(sheet2!B13&G13 = vlookup(sheet1!
$B$5:$K$200, "yes","no")???
>ANy help appreciated..
>
>
>.
>

Biff:

Thanks, this was exactly what I was looking for (a CSE type formula).

Jim


"Biff" <[email protected]> wrote in message
news:[email protected]...
> Hi!
>
> No need for concatenation and a helper. You can
> concatenate the fields in the formula.
>
> Assume the last name and amount are in the same columns in
> Sheet1 as they are in Sheet2. Entered as an array:
>
> =IF(ISNUMBER(MATCH(B13&G13,Sheet1!B1:B100&Sheet1!
> G1:G100,0)),"Yes","No")
>
> Biff
>
> >-----Original Message-----
> >In both sheet1 and sheet2 I have hundreds of records.
> >60% of the records in sheet1 are also in sheet2.
> >If I concatenate 2 of the fields "LastName" and "Amt",
> >can I insert a formula in a helper column (Sheet2)
> >that will search sheet1 and bring back a "yes" if found,
> >"no" if not...?
> >=if(sheet2!B13&G13 = vlookup(sheet1!
> $B$5:$K$200, "yes","no")???
> >ANy help appreciated..
> >
> >
> >.
> >

You're welcome! Thanks for the feedback.

Biff

>-----Original Message-----
>Biff:
>
>Thanks, this was exactly what I was looking for (a CSE
type formula).
>
>Jim
>
>
>"Biff" <[email protected]> wrote in message
>news:[email protected]...
>> Hi!
>>
>> No need for concatenation and a helper. You can
>> concatenate the fields in the formula.
>>
>> Assume the last name and amount are in the same columns
in
>> Sheet1 as they are in Sheet2. Entered as an array:
>>
>> =IF(ISNUMBER(MATCH(B13&G13,Sheet1!B1:B100&Sheet1!
>> G1:G100,0)),"Yes","No")
>>
>> Biff
>>
>> >-----Original Message-----
>> >In both sheet1 and sheet2 I have hundreds of records.
>> >60% of the records in sheet1 are also in sheet2.
>> >If I concatenate 2 of the fields "LastName" and "Amt",
>> >can I insert a formula in a helper column (Sheet2)
>> >that will search sheet1 and bring back a "yes" if
found,
>> >"no" if not...?
>> >=if(sheet2!B13&G13 = vlookup(sheet1!
>> $B$5:$K$200, "yes","no")???
>> >ANy help appreciated..
>> >
>> >
>> >.
>> >
>
>
>.
>