Ensuring Data included acroos two Workbooks

**kiktuo** · 04-04-2011, 04:44 AM

I have two workbooks and i have to ensure that i have included all the people from Workbook1 are included in workbook 2.

In column A of both workbooks is the current employer.
In column B of both workbooks is the unique reference number.

In workbook 1 i want to indicate whether the data is present in Workbook 2.

One thing to note is the unique reference may be duplicated as a different employer may have used it for thier staff thus only unique to that employer.

I need workbook 1 to look at the columns A & B in workbook 2 and indicate somehow that is included.

I would upload the sheets but the information is sensitive.

Thanks in advanced.

**Kafrin** · 04-04-2011, 05:44 AM

OK, having done my own search and testing I've found you can use this formula:

Please Login or Register  to view this content.

This returns a 1 if there's a match, or a #N/A if there's not a match. Alternatively, to not show an error, use this:

Please Login or Register  to view this content.

If you want it to show blank or the word MISSING, change the final 1 to "" and the final 0 to "MISSING".

The downside with this approach is that you can't just search on a whole column (e.g. B:B), yu have to use a set range. If you know you have a max number of rows (I've used 2000 in my example), or you're happy to set it to the max possible number of rows, then this isn't a problem.

There are also a number of approaches using SUMPRODUCT, this site has some examples, but be sure to read the comments at the bottom to get the right version of the formula.