Using Index/Match in Many to One search to Check a Checkbox

Yanina Kupava

I am trying to use an Index/Match function to connect two sheets, a Fleet Task sheet and a Fleet Inventory. I would like the function to search through the Task sheet and it finds tasks with a status of "Not Started" or "In Progress" to mark a checkbox associated with that vehicle in the Inventory.

I ran into an issue when trying to use just an IF function because I think when it matches the vehicle ID once, it stops cycling through the rows. I think I need some combination of the IFERROR and IF functions.

This function is successful only if all the tasks for a vehicle are the same status (ie. completed), but not if there's a mix of completed and pending work.

=IF((INDEX({Fleet Task Assignments - Status}, MATCH([CPC fleet #]@row, {Fleet Task Assignments - Vehicle ID}, 0)) = "In Progress"), true, false)

I also tried to use the IFERROR, but I'm not sure how to add the true/false piece that will mark the checkbox once matched.

=IFERROR(IFERROR(INDEX({Fleet Task Assignments - Status}, MATCH([CPC fleet #]@row, {Fleet Task Assignments - Vehicle ID}, 0)) = "Not Started"), INDEX({Fleet Task Assignments - Status}, MATCH([CPC fleet #]@row, {Fleet Task Assignments - Vehicle ID}, 0)) = "In Progress")), INDEX({Fleet Task Assignments - Status}, MATCH([CPC fleet #]@row, {Fleet Task Assignments - Vehicle ID}, 0)) = "Extended"))

Zachary Hall

@Yanina Kupava ,

Sorry about that. I missed the part where it needed to cycle through. You are right in that it matches the first value and doesn't continue. The way to solve for this sort of issue in Smartsheet is to use a helper column to combine the values you are looking to find and then searching for that.

For instance, you could have a helper column (on the source sheet if it is different) with the following formula:

• =IF(OR([Fleet Task Assignments - Status]@row="Not Started", [Fleet Task Assignments - Status]@row="In Progress"),[Fleet Task Assignments - Vehicle ID]@row+" Incomplete","")

Then you could have your search column look like this:

=IFERROR(IF(MATCH([CPC fleet #]@row + " Incomplete", {Fleet Task Assignments - Vehicle ID}, 0)>0, true, false), false)

I hope this helps!

Zachary Hall

This should do it:

• =IFERROR(IF(OR(INDEX({Fleet Task Assignments - Status}, MATCH([CPC fleet #]@row, {Fleet Task Assignments - Vehicle ID}, 0)) = "Not Started"), INDEX({Fleet Task Assignments - Status}, MATCH([CPC fleet #]@row, {Fleet Task Assignments - Vehicle ID}, 0)) = "In Progress", INDEX({Fleet Task Assignments - Status}, MATCH([CPC fleet #]@row, {Fleet Task Assignments - Vehicle ID}, 0)) = "Extended"), True, False), False)

Hope this helps!

Yanina Kupava

Hi Zack!

Thanks for the quick reply. I removed an extra parenthesis, and now it looks like this:

=IFERROR(IF(OR(INDEX({Fleet Task Assignments - Status}, MATCH([CPC fleet #]@row, {Fleet Task Assignments - Vehicle ID}, 0)) = "Not Started", INDEX({Fleet Task Assignments - Status}, MATCH([CPC fleet #]@row, {Fleet Task Assignments - Vehicle ID}, 0)) = "In Progress", INDEX({Fleet Task Assignments - Status}, MATCH([CPC fleet #]@row, {Fleet Task Assignments - Vehicle ID}, 0)) = "Extended"), true, false), false)

But all my values still come back as False. I added a test record, one dummy vehicle with one associated dummy task "Not Started", and that comes back true. I think it's still not cycling through all the values in the task sheet.

Zachary Hall

@Yanina Kupava ,

Sorry about that. I missed the part where it needed to cycle through. You are right in that it matches the first value and doesn't continue. The way to solve for this sort of issue in Smartsheet is to use a helper column to combine the values you are looking to find and then searching for that.

For instance, you could have a helper column (on the source sheet if it is different) with the following formula:

• =IF(OR([Fleet Task Assignments - Status]@row="Not Started", [Fleet Task Assignments - Status]@row="In Progress"),[Fleet Task Assignments - Vehicle ID]@row+" Incomplete","")

Then you could have your search column look like this:

=IFERROR(IF(MATCH([CPC fleet #]@row + " Incomplete", {Fleet Task Assignments - Vehicle ID}, 0)>0, true, false), false)

I hope this helps!

Yanina Kupava

Thank you! That helped. I implemented a similar approach with a COUNTIF on the statuses plus Match@row and then used an IF function to identify rows with counts greater than 1 to activate my checkbox.