Forum Discussion
INDEX MATCH #N/A Error
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], "" ) )
HiSaeedom , your barcodes are texts or numbers? It looks like texts based on length. Use concatenate then (&). And in your case it will be array formula (Ctrl+Shift+Enter)
All the items I have in the list have 3 to 5 numbers barcodes
Depends on the unit of measurement each unit have a special barcode and when I use the barcode reader in the stock counter I need the item ID and the product name and the unit of measurement fill up in the table
Is there another way or I use the wrong index and match function
Can you please write down the formula that I need if you need more picture I will happily send
It is not the INDEX/MATCH that is in error.
It is the way in which you are treating the bar-codes as numbers. You say the bar-codes are 3 to 5 digits long but your picture shows 16 digit codes which is more than can be stored as an Excel number. Your formula also shows bar-codes being added '+' which does not make much sense. SergeiBaklan's suggestion of concatenation '&' would provide a better basis for comparison.
p.s. If you really wish to combine bar-codes as arithmetic values then you would need
= codeA * 10^LEN(codeB) + codeB
but care needs to be taken that the resulting number must not go beyond 15 digits.