Matching row of SOURCE table against TARGET table (array)

Hi all, I have two tables combined onto one sheet for simplicity; one is Source and the other is Target. I'm trying to see if each row from the Source table is found in the Target table. If possible, flag the rows of each table upon a match.

(e.g. the first match will have a corresponding "1" by each row on each table that matches, the next match will have a corresponding "2" or some other way to identify the line of each table that matches.)

NOTE: the actual files are two different spreadsheets.

Any help would be greatly appreciated!

tmwcarl,

Attached is a modified version of your example workbook. I inserted a row (column I) for the matching numbers for the TARGET table.

In cell D6 and copied down is this formula for the SOURCE table:

=IF(SUMPRODUCT(--($J$6:$J$25=E6),--($K$6:$K$25=F6),--($L$6:$L$25=G6),ROW($L$6:$L$25)-5)>0,COUNT($D$5:D5)+1,"")

In cell I6 and copied down is this formula for the TARGET table:

=IF(SUMPRODUCT(--($E$6:$E$19=J6),--($F$6:$F$19=K6),--($G$6:$G$19=L6))>0,INDEX($D$6:$D$19,SUMPRODUCT(--($E$6:$E$19=J6),--($F$6:$F$19=K6),--($G$6:$G$19=L6),ROW($D$6:$D$19)-5)),"")

Note that I used excel an 2003 and lower compatible formula because you uploaded a .xls file, even though you have excel 2007 listed as your excel version. You could use a countifs formula instead of the sumproduct, but that is only excel 2007 and higher.

Is something like that what you were looking for?

Hi tmwcarl,

Welcome to the forum.
See the attached file and let me know if this is what you were loooking for. Thanks.


Regards,
DILIPandey

<click on below 'star' if this helps>

You are welcome tmwcarl . .
Cheers . .

Regards,
DILIPandey

<click on below star if this helps>