match multiple fields in records

**prawer** · 08-06-2009, 01:27 PM

Hi All,

I know how to see if one value exists in an arrray, but is there a way to see if a combination of fields in one array exist in the same combination in another array?

In other words, if I have table A:

Name City
Alyssa Cleveland, OH
Derek Pittsburgh, PA
Larry San Francisco, CA
Merl Provo, UT

and table B:

Name City
Alyssa Pittsburgh, Pa
Derek Cleveland, OH
Larry San Francisco, CA
Merl Philadelphia, Pa

I would like to add a column to table B containing a function that would indicate, in this instance, that only Larry-San Francisco, CA is a record shared by both tables.

Can anyone help me with what that function would like?

**NBVC** · 08-06-2009, 01:31 PM

=Sumproduct(--($A$1:$A$100=X1),--($B$1:$B$100=Y1))>0

copied down

This returns TRUE if a Match is found.

where A1:B100 contains Table A and Table B begins as X1 and Y1

**prawer** · 08-06-2009, 01:51 PM

Thanks so much for the quick reply!

Quick follow-up:

I've used sumproduct before to count in this way -- and I've always omitted the double dashes before parentheses. Are they still necessary in Excel 2003/2007?

**NBVC** · 08-06-2009, 01:54 PM

There are other ways... but something is needed to coerce the True/False arrays to 1/0 arrays for Sumproduct to do the math...

... other ways:

=Sumproduct(($A$1:$A$100=X1)*($B$1:$B$100=Y1))>0

=Sumproduct(($A$1:$A$100=X1)+0,($B$1:$B$100=Y1)+0)>0

=Sumproduct(($A$1:$A$100=X1)*1,($B$1:$B$100=Y1)*1)>0