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)
HI
can you please help with UOM i need it to just take the header name back to the count list sheet in the UOM column
Saeedom , that will be like
=INDEX(list!$C$2:$G$2, SUMPRODUCT((Table_ProductList = [@[Product Barcode]] )*COLUMN(Table_ProductList))-COLUMN(Table_ProductList[[#Headers],[Product Barcode 1 BOX]])+1)
thank you from the heart
it works with some change
I'm really cant't thank you enough
and also can't forget Mr.@Peter Bartholomew
all the best for you
my regards
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 )Nope, I was wrong - will play with the model
Hi
I try like this (&) and it didn't work
also (:) and not working
only work if I choose one column
is there a way to fix this or there is another formula
please help
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], "" ) )
Hi
can you please help with the UOM so when i reed a box barcode i can see the unit there or roll and get that data from the header barcode that i have
i try the formula you put and try to change it but i get wrong answer
thanks from the heart
my regards
saeed
- It’s number
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 sendIt 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.
