I'm trying to create a formula that will look for a binary yes/no across multiple data collection events, each in its own sheet, with each record identified by a student ID.

The structure I have is: Sheet 1, list of student IDs, location of Vlookup statement. The other sheets, two of them for now, are a list of student IDs in the rows with a column of binary yes/no (0/1) data. These sheets of data are defined as range1 and range2. An important characteristic of my formula is that it must honor the fact that the student ID lists between all of the sheets may be different due to population mobility.

What I'd like my formula to do is vlookup the student ID. If there's an ID match andanyof the ranges that match have a 1 in the binary column, then the master sheet would report a 1. If there's a match andallof the ranges that match have a 0, then the master sheet would report a 0. If there's no ID match to any of the sheets, then there would simply be a blank on the master.

The formula below is the closest I've come. It works in every scenario except when there's a match in range1 but not in range2 or vice versa. The problem is that the vlookup nested in the sum() in the second half of the formula returns an error, causing the entire formula to report an error.

Formula:Please Login or Register to view this content.

Basically, my question boils down to this: how do I contend with a sum(vlookup(1),vlookup(2)), if one of the vlookups might possibly return an N/A?

Thanks and sorry for the text wall...

## Bookmarks