Formula for a how to check a check box based on two ranges in two sheets

ealexis

Hello,

Can someone please help me with a formula that will check a box when it finds the email in both sheet.

Master sheet has the Email Address and the box that needs to be check

if it matches an email address in the BUP Survey sheet

Bassam Khalil

Hi @ealexis,

please try the following:

1- Create a Helper Column in the Master Sheet: This column will be used to search for the email address in the "BUP Survey" sheet and return a specific value if the email is found. You can name this column something like "Email Found in BUP Survey".

2-Use a CROSS-SHEET VLOOKUP Formula: In the "Email Found in BUP Survey" helper column, you would use a VLOOKUP formula to search for the email address in the "BUP Survey" sheet. Since direct cross-sheet checkbox toggling isn't possible, this formula will return a value indicating the presence of the email address.

Assuming that the "BUP Survey" sheet has an "Email Address" column as well, your formula in the helper column on the "Master" sheet might look something like this:

=IFERROR(VLOOKUP([Email Address]@row, {BUP Survey Email Address Range}, 1, false), "Not Found")

3- for check box column called [Proof Of BUP Survey] use the following formula to check the box if the email is founded

=IF([Email Found in BUP Survey]@row = "Not Found", FALSE, TRUE)

Bassam Khalil

Hi @ealexis,

please try the following:

1- Create a Helper Column in the Master Sheet: This column will be used to search for the email address in the "BUP Survey" sheet and return a specific value if the email is found. You can name this column something like "Email Found in BUP Survey".

2-Use a CROSS-SHEET VLOOKUP Formula: In the "Email Found in BUP Survey" helper column, you would use a VLOOKUP formula to search for the email address in the "BUP Survey" sheet. Since direct cross-sheet checkbox toggling isn't possible, this formula will return a value indicating the presence of the email address.

Assuming that the "BUP Survey" sheet has an "Email Address" column as well, your formula in the helper column on the "Master" sheet might look something like this:

=IFERROR(VLOOKUP([Email Address]@row, {BUP Survey Email Address Range}, 1, false), "Not Found")

3- for check box column called [Proof Of BUP Survey] use the following formula to check the box if the email is founded

=IF([Email Found in BUP Survey]@row = "Not Found", FALSE, TRUE)

ealexis

thank you so much! that did the trick