I have a sheet with raw materials used to manufacture a product. Currently, I am looking up the raw material available units by using an INDEX MATCH formula to match the Inventory ID on this sheet to the Inventory ID on a sheet that shows available units.

Here's a portion of the sheet used to produce the product:

The formula in the RM Available Units column is:

=INDEX({3 Corum RM Available}, MATCH([RM Inventory ID]@row, {3 Corum RM Inv ID}, 0))

The sheet that has the availability data looks like this:

As you can see, I'm currently showing the units available of that particular batch purchased using the Inventory ID, but I want to show the availability of all batches - the PARENT. For example, in this case, I actually have available 407 units of Tamanu Oil.

What formula do I need to use in the RM Available Units column to get this information?

Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!