Adding a secondary match option if the first one is blank

H.Arsenault

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!

Paul Newcome

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)))

Paul Newcome

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)))

H.Arsenault

That's the ticket! Thank you so much @Paul Newcome :)

Paul Newcome

Happy to help. 👍️