How can my index/match formula include a column that has multiple values

Krystal Garcia

Ok so I downloaded an inventory management template that is awesome! When people submit a request form for a ppe item they need, it has index/match forumlas to show us what Current Inventory remains. Its great!

However, they are only able to select one ppe item from the drop down list. And I need them to be able to select more than one (sometimes up to 20+ items). Currently when they do that, the index/match formulas break and I cannot figure out how to make them work to show us the counts when multiple items are selected.

Is this possible? Please help

Here is my main intake sheet and these cells break:

This cell has a index/match formula telling us what category the PPE is from but if the PPE Requested column has multiple items in it, the category breaks

and when that breaks, the other formulas break:

KPH

Hi @Krystal Garcia

I think the HAS function will be your friend here. It will look through the multi-select options rather than evaluating the entire cell.

I tend to use that with INDEX and COLLECT.

I can't access smartsheet right now so can't work up an example for you, hopefully this nudge is all you need.