Checking a box in a sheet when searching a person and if they are checked in another sheet

sophiaashepard

Hello,

We have a data input sheet where we would like to signify who are key personnel using a checkbox (star). In the data input sheet, we have staff names (Person) as well. We also have a rollup sheet in which I would like to have a box checked if the staff (Person) is checked in the data input sheet. I know I need to do some sort of INDEX/MATCH formula but I cannot get a formula to work.

Data Input sheet:

Rollup Sheet:

Example: I would like for the Key Personnel box to be checked in the Rollup sheet since it is checked in the Data Input sheet using the staff members name to lookup.

Jason Albrecht

Hi @sophiaashepard

I had the same concern as Scott regarding the mismatched column type, so I took the time to replicate your question and found the following formula to work:

=IF(VLOOKUP(Person@row, {Data Input Sheet Person to Key Person columns}, 2, false) = 1, 1, 0)

Scott Orsey

Hi @sophiaashepard and @Jason Albrecht ,

A couple of thoughts:

While VLOOKUP() definitely can work, the general consensus I've seen in the Community is to use INDEX(MATCH). This would look something like:

=INDEX({Checkbox Column from Input Sheet},MATCH(Person@row,{Person Column from Input Sheet},0))

Even with VLOOKUP, you could eliminate the IF() statement since both the source and the destination are checkboxes and you are simply transferring the data. So this should also work:

=VLOOKUP(Person@row, {Data Input Sheet Person to Key Person columns}, 4, false)

Note that I bolded the "4". My bet is that the expression in braces "{Data Input Sheet Person to Key Person columns}" is not recognized by SS as a cross sheet reference or the reference isn't set up properly. This may be an area you'll need to do some independent investigation/learning. If you are using the vlookup function when you set up your cross-sheet reference, you need it to include the four columns from your original sheet starting with "Person" on the left and "Key Personnel" as the the right column. VLOOKUP always searches in the leftmost column and then returns the value from whatever column number you specify.

The reason INDEX/MATCH is preferred is that if you ever add or move the columns around in your Input sheet, you will need to update VLOOKUP to pull the correct column. You won't need to change the INDEX/MATCH if things change in your input sheet.

I hope this is helpful

Be well

Scott Orsey

Hi @sophiaashepard , That should be fairly straightforward. Please share your formula and perhaps we can see what the issue is. I don't know how critical it will be, but is the "Person" column type set to "Contact" in both sheets?

Be well

Jason Albrecht

Hi @sophiaashepard

I had the same concern as Scott regarding the mismatched column type, so I took the time to replicate your question and found the following formula to work:

=IF(VLOOKUP(Person@row, {Data Input Sheet Person to Key Person columns}, 2, false) = 1, 1, 0)

sophiaashepard

@Scott Orsey Yes, The column type is contact in both.

@Jason Albrecht Thank you for providing me with a formula to try. When I put this in the sheet, I get an #unparseable error. see below.

Rollup sheet:

Scott Orsey

Hi @sophiaashepard and @Jason Albrecht ,

A couple of thoughts:

While VLOOKUP() definitely can work, the general consensus I've seen in the Community is to use INDEX(MATCH). This would look something like:

=INDEX({Checkbox Column from Input Sheet},MATCH(Person@row,{Person Column from Input Sheet},0))

Even with VLOOKUP, you could eliminate the IF() statement since both the source and the destination are checkboxes and you are simply transferring the data. So this should also work:

=VLOOKUP(Person@row, {Data Input Sheet Person to Key Person columns}, 4, false)

Note that I bolded the "4". My bet is that the expression in braces "{Data Input Sheet Person to Key Person columns}" is not recognized by SS as a cross sheet reference or the reference isn't set up properly. This may be an area you'll need to do some independent investigation/learning. If you are using the vlookup function when you set up your cross-sheet reference, you need it to include the four columns from your original sheet starting with "Person" on the left and "Key Personnel" as the the right column. VLOOKUP always searches in the leftmost column and then returns the value from whatever column number you specify.

The reason INDEX/MATCH is preferred is that if you ever add or move the columns around in your Input sheet, you will need to update VLOOKUP to pull the correct column. You won't need to change the INDEX/MATCH if things change in your input sheet.

I hope this is helpful

Be well

sophiaashepard

@Scott Orsey Thank you! That formula worked. I added an IFERROR to the beginning for the #NO MATCH outputs. I appreciate your insights into why an INDEX/MATCH formula would work better. I ensured that the two sheet reference columns were next to each other when trying the VLOOKUP and I was still getting an error. INDEX/MATCH will work better.