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:

Eric Law

https://community.smartsheet.com/discussion/114390/how-can-my-index-match-formula-include-a-column-that-has-multiple-values

So, the issue you are running into with INDEX/MATCH is that it can only grab 1 piece of data at a time.

In addition, if there are multiple items in the PPE Requested, you would need multiple order qty if they are not the same qty. There's some ways to do this but it will take a lot of work and adding of either multiple columns, or a filtering sheet that you'll need to set up yourself.

Krystal Garcia

Understood. What is the best way to do this then ? I started to add columns but that will be like 72 additional columns on this sheet 😯