Check box selection - referring multiple sheets

Joseph Aloysias

I have multiple sheets(sheet1,sheet2,sheet3,sheet4,sheet5) with email id's and I want to cross check in one master sheet in one check box column, if the match found then it should check the checkbox.

can we use index match formula to refer multiple sheets?

I'm trying to delete the master sheet email address if any of the email address are deleted from any of the 5 sheets. please help me with the workaround and formula

Paul Newcome

You would use something like this:

=IF(OR(MATCH([Email Address]@row, {Sheet 1 Email Address}, 0)> 0, MATCH([Email Address]@row, {Sheet 2 Email Address}, 0)> 0, MATCH([Email Address]@row, {Sheet 3 Email Address}, 0)> 0, MATCH([Email Address]@row, {Sheet 4 Email Address}, 0)> 0, MATCH([Email Address]@row, {Sheet 5 Email Address}, 0)> 0), 1)

The above will check the box if the email address is found on at least one of the other sheets.

Joseph Aloysias

Hi @Paul Newcome I tried doing that with two sheets but its not working, it says #no Match even if we have email address in the other sheets,

=IF(OR(MATCH(Email@row, {Deleted Range 1}, 0) > 0, MATCH(Email@row, {New Sheet Range 3}, 0) > 0), 1)

Please help!

Paul Newcome

Try this:

=IF(OR(IFERROR(MATCH(Email@row, {Deleted Range 1}, 0), 0) > 0, IFERROR(MATCH(Email@row, {New Sheet Range 3}, 0), 0) > 0), 1)

What is happening is you are getting the error from one of the MATCH functions. So we wrap each in an IFERROR to output zero. Since zero is not greater than zero, it shouldn't throw a false positive.

Joseph Aloysias

Thank you @Paul Newcome its working now.

Joseph Aloysias

@Paul Newcome can we add another two criteria like start date, end date in the same formula? please help!

Paul Newcome

You would use a MATCH/COLLECT combo.

MATCH(COLLECT(Email@row, {Start Date}, start date criteria, {End Date}, end date criteria), {Deleted Range 1}, 0)