Find matching records in two tables using mulitiple criteria
 

--
RCIn this case there are two tables of data of different sizes copied into
the same worksheet. What is needed is to find each row of records in the
first table that contains matching data to the rows of records in the second
table by using multiple criteria. Here is an example below where the first
table is located in columns A through D and the second table in columns F
through I. The idea is to put a function in the cells in column E that will
return a value (such as 1 or 0, or Yes or No, or True or False) to indicate
the record on the particular row in the first table has a match, or does not
match, a record contained in any row in the second table.

A B C D E F G
H I
1 ID# LN FN Color Match? ID# LN
FN Color
2 321 Adams Alan Green No 321 Adams Alan
Red
3 321 Adams Alan Red Yes 432 Allen
Bob Red
4 432 Allen Bob Red Yes 543 Baker
Dan Green
5 432 Allen Bob Green No 765 Collins
John Green
6 654 Barber Sam Green No
7 765 Collins John Red No
8 765 Collins John Green Yes



Thanks,
--
RC

Find matching records in two tables using mulitiple criteria
 

In column E, use the following formula:

IF (A2&B2&C2&D2 = F2&G2&H2&I2, 1, 0)

Results will be 1 if they match and 0 if they don't.

"RC" wrote:


--
RCIn this case there are two tables of data of different sizes copied into
the same worksheet. What is needed is to find each row of records in the
first table that contains matching data to the rows of records in the second
table by using multiple criteria. Here is an example below where the first
table is located in columns A through D and the second table in columns F
through I. The idea is to put a function in the cells in column E that will
return a value (such as 1 or 0, or Yes or No, or True or False) to indicate
the record on the particular row in the first table has a match, or does not
match, a record contained in any row in the second table.

A B C D E F G
H I
1 ID# LN FN Color Match? ID# LN
FN Color
2 321 Adams Alan Green No 321 Adams Alan
Red
3 321 Adams Alan Red Yes 432 Allen
Bob Red
4 432 Allen Bob Red Yes 543 Baker
Dan Green
5 432 Allen Bob Green No 765 Collins
John Green
6 654 Barber Sam Green No
7 765 Collins John Red No
8 765 Collins John Green Yes



Thanks,
--
RC

Find matching records in two tables using mulitiple criteria
 

Thanks Bob, and I apologize for the table in the example turning out like it
did where the columns were not in alignment. The table example looked like it
should until after I posted the question. I've realigned the table in this
reply in hope that it will show a better example.
The IF function appears to be comparing only the data on the same row for
the first and second table, and returning zeroes for each row of records. Is
there a modification, or is there another function, or nested functions, that
will use as the lookup value the data in a range of cells on a row, like the
IF function does, and then use more than one column as the lookup array?

Thanks again,
--
RC


"BobT" wrote:

In column E, use the following formula:

IF (A2&B2&C2&D2 = F2&G2&H2&I2, 1, 0)

Results will be 1 if they match and 0 if they don't.

"RC" wrote:


--
RCIn this case there are two tables of data of different sizes copied into
the same worksheet. What is needed is to find each row of records in the
first table that contains matching data to the rows of records in the second
table by using multiple criteria. Here is an example below where the first
table is located in columns A through D and the second table in columns F
through I. The idea is to put a function in the cells in column E that will
return a value (such as 1 or 0, or Yes or No, or True or False) to indicate
the record on the particular row in the first table has a match, or does not
match, a record contained in any row in the second table.

A B C D E F G
H I
1 ID# LN FN Color Match? ID# LN
FN Color
2 321 Adams Alan Green No 321 Adams Alan
Red
3 321 Adams Alan Red Yes 432 Allen
Bob Red
4 432 Allen Bob Red Yes 543 Baker
Dan Green
5 432 Allen Bob Green No 765 Collins
John Green
6 654 Barber Sam Green No
7 765 Collins John Red No
8 765 Collins John Green Yes



Thanks,
--
RC