Using INDEX/MATCH to create sheet with subset of data from another sheet

lhadduck · 08/14/18

I have a sheet that lists hundreds of inventory items. The sheet has columns with checkboxes indicating if a given item is included in a specific shipment type. I'd like to create a new sheet that populates a list of the inventory item numbers for every instance that checkbox is checked for a given row on the original sheet.

Using INDEX and MATCH I figured out how to grab the first instance (formula below), but I think because the match value is not unique, when I drag the formula down, it's repeating the first instance of the checked box instead of every instance the box is checked.

=INDEX({Inventory List Range 1},MATCH(true,{Inventory List Range 4},0))

Any ideas how to make it pull in all instances when the checkbox is checked?

Shaine Greenwood · 08/17/18

Hello,

MATCH finds the first exact match when you use the 0 search type (your last parameter). When referencing the range of an entire column, it's going to return the first instance of "true" in that column and return that.

You're correct in assuming that this is likely a result of the MATCH search value not being unique.

If you just want to gather the included shipments in another view (and don't need to perform further calculations) you might consider creating a report: https://help.smartsheet.com/articles/522214-creating-reports

Otherwise, you'll need to explore using more unique combinations of values: Date ranges, shipment names, etc. to get what you need.

Another function to look at is COLLECT, which can pull in a range of data that meets multiple criteria: https://help.smartsheet.com/function/collect

Using INDEX/MATCH to create sheet with subset of data from another sheet

Comments

Help Article Resources

Categories