Looking for comparable data records between Sheet1 and Sheet2

J

Jim May

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..
 
B

Biff

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
 
J

Jim May

Biff:

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

Jim
 
B

Biff

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





.
Click to expand...
 
Top