Forum Discussion
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 UOM
i have onle one Id for the items and 4 to 5 barcode depends on the UOM
I need the index understand the barcode and get the data for me
i try but no success
how can i do that
thank for the help
best regards
saeedom
there is 2 Pic. of the sheets
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], "" ) )
14 Replies
This is a problem where a sample Excel file would have helped immensely. Earlier I assumed that each barcode you seek could appear within any one of 5 columns, setting it outside the normal VLOOKUP scenario. I now believe you also wish to return the header for the column in which you find the barcode. My approach is first to focus upon these 5 columns of Table_ProductList. Because the headings are so long I defined the name 'TableData' to represent the array of reference barcodes. This refers to:
=Table_ProductList[[Product Barcode 1 Box]:[Product Barcode 5 Pallet]]
The barcode I am seeking to match is a single (relative) value from the other table that I called 'Barcode':
=Table2[@[Product Barcode]]
The next step is to compare each element of the array for a match to the barcode, using 1 to indicate the matching code and 0 for all others. That I called 'arrayMatches' which refers to the formula
= SIGN( TableData=Barcode ).
This table is the basis by which both the record (as above) and the heading may be returned. Unfortunately spreadsheets are so focussed upon interactive use that there is a dearth of operators that act upon a 2D array by row or by column. MMULT may be unique in that regard. To calculate the sum over columns for each row, one has 'columnMatches'
= 1 / MMULT( arrayMatches, {1;1;1;1;1} )
This column has 1 for the row containing the matched barcode and a #DIV/0! error for all other rows. LOOKUP can be used to return the corresponding value from any other column
= LOOKUP( 1, columnMatches, Table_ProductList[Item] )
To return the heading from the array of matches is a very similar process except it is each column that has to be summed, returning a row with a single 1 for the match and #DIV/0! error for other columns, namely 'rowMatches'
= 1 / MMULT( U, arrayMatches )
The other thing one needs to evaluate this formula is the row array of 1s, 'U', the is the same length and the table has rows. 'U' is given by
= TRANSPOSE( SIGN( ROW(TableData) ) )
The appropriate table header is returned by what is, in effect, an HLOOKUP, namely
= LOOKUP( 1, rowMatches, HeadingUIC )
where 'HeadingUIC' are the headings of the 5 columns
=Table_ProductList[[#Headers],[Product Barcode 1 Box]:[Product Barcode 5 Pallet]]
I appreciate this is heavy-going and involves approaches that are alien to normal spreadsheet development but I hope it will provide some insight.
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)
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], "" ) )
- 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.
