SOLVED

INDEX MATCH #N/A Error

Copper Contributor

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 

 

14 Replies

Hi@Saeedom , 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)

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 send

@Sergei Baklan 

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

@Saeedom 

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.  @Sergei Baklan'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.

best response confirmed by Saeedom (Copper Contributor)
Solution

@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], "" ) )

@Peter Bartholomew 

Thanks for the help 

yes that what I need to do 

the lookup formula work for me the other one not working but thats ok. i need only one

 

Now i try to use the formula for the product barcode UOM (Unit Of Measurement) to the same table, but it gives wrong answer. How i can fix  the formula to get only  the header name of the specified barcode column. 

 

@Peter Bartholomew 

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 

@Sergei Baklan 

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 

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

@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)

 

@Sergei Baklan 

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 

@Saeedom , you are welcome

@Saeedom 

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.

1 best response

Accepted Solutions
best response confirmed by Saeedom (Copper Contributor)
Solution

@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], "" ) )

View solution in original post