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":
Question:
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
-
You can use an IFFERROR.
=IFERROR(1st_index_match, 2nd_index_match)
=IFERROR(INDEX({Inventory-System Group}, MATCH([Product Drop-Down]@row, {Inventory-Form Names}, 0)), INDEX({Inventory-System Group}, MATCH(Barcode@row, {Inventory Barcode}, 0)))
Answers
-
You can use an IFFERROR.
=IFERROR(1st_index_match, 2nd_index_match)
=IFERROR(INDEX({Inventory-System Group}, MATCH([Product Drop-Down]@row, {Inventory-Form Names}, 0)), INDEX({Inventory-System Group}, MATCH(Barcode@row, {Inventory Barcode}, 0)))
-
That's the ticket! Thank you so much @Paul Newcome :)
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 472 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!