Adding a secondary match option if the first one is blank

Currently we have a new inventory system which consists of a Master Sheet (contains stock counts for 3 office locations) & a Check In/Out sheet (which has a form for each location). We were excited to utilize the Barcode Scanning feature but unfortunately have discovered not all products have barcodes. Due to this we have implemented an option for users to enter a barcode or select their product from a dropdown list.

Check In/Out sheet:

Master Sheet (displaying only one location) - aka "Inventory":


How can I combine these 2 formulas, so if the barcode cell is empty it will match with the Product Drop-Down and vice-versa?

1) =INDEX({Inventory-System Group}, MATCH([Product Drop-Down]@row, {Inventory-Form Names}, 0))

2) =INDEX({Inventory-System Group}, MATCH(Barcode@row, {Inventory Barcode}, 0))

These formulas are used to autofill the System Group & Product Group. TIA!

Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!