Adding a secondary match option if the first one is blank

04/07/21
Accepted

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([email protected], {Inventory Barcode}, 0))

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

Best Answer

  • Paul NewcomePaul Newcome ✭✭✭✭✭
    Accepted 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([email protected], {Inventory Barcode}, 0)))

Answers

  • Paul NewcomePaul Newcome ✭✭✭✭✭
    Accepted 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([email protected], {Inventory Barcode}, 0)))

  • H.ArsenaultH.Arsenault ✭✭✭✭✭

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

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Happy to help. 👍️

Sign In or Register to comment.