Forum Discussion

Saeedom's avatar
Saeedom
Copper Contributor
Mar 20, 2019
Solved

INDEX MATCH #N/A Error

Hi  i have a stock counter sheet to use it with barcode reader  i have the items in other sheet and wont to get the data when i input the barcode to get the  prodect ID, the prodect name and the UO...
excel
Excel Online
Formulas and Functions
  • PeterBartholomew1's avatar
    PeterBartholomew1
    Mar 21, 2019

    Saeedom 

    Sorry, I have only just understood what it is you are trying to do.  I believe you are trying to find a match in any one of 5 columns and to return the product name.  You would need 5 separate MATCH formulas for that and then take the maximum of the 5 row-indices before using INDEX.  

    Alternatively you could use non-standard formulas such as

    = LOOKUP( 1, 1 / MMULT(

    SIGN( Table_ProductList[[Product Barcode 1 BOX]:[Product Barcode 5 pallet]] = [@[Product Barcode]] ),

    {1;1;1;1;1} ),

    Table_ProductList[Item] )

    or

    = CONCAT(

    IF( Table_ProductList[[Product Barcode 1 BOX]:[Product Barcode 5 pallet]] = [Product Barcode],

    Table_ProductList[Item], "" ) )

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Mar 26, 2019

Hi Saeedom ,

 

Do I understand correctly what other words you'd like to return in which column of the list the barcode was found?

 

I didn't test, but Peter's formula shall work

= LOOKUP( 1, 1 / MMULT(
SIGN( Table_ProductList[[Product Barcode 1 BOX]:[Product Barcode 5 pallet]] = [@[Product Barcode]] ),
{1;1;1;1;1} ),
list!$C$2:$G$2 )

 

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Mar 26, 2019

Nope, I was wrong - will play with the model

Resources