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

edited 03/19/24

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.

Tags:

• ✭✭✭✭✭✭

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)`

Hope this helps and that you have a great day,

Jason Albrecht MBA, MBus(AppFin), DipFinMgt

LinkedIn profile - Open to work

• ✭✭✭✭✭

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.

Be well

• ✭✭✭✭✭

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

• ✭✭✭✭✭✭

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)`

Hope this helps and that you have a great day,

Jason Albrecht MBA, MBus(AppFin), DipFinMgt

LinkedIn profile - Open to work

• @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:

• ✭✭✭✭✭

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.