SOLVED

# INDEX MATCH #N/A Error

Highlighted
Occasional Contributor

# 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

14 Replies
Highlighted

# Re: INDEX MATCH #N/A Error

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)

Highlighted

# Re: INDEX MATCH #N/A Error

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
Highlighted

# Re: INDEX MATCH #N/A Error

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

Highlighted

# Re: INDEX MATCH #N/A Error

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.

Highlighted
Solution

# Re: 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], "" ) )

Highlighted

# Re: INDEX MATCH #N/A Error

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.

Highlighted

# Re: INDEX MATCH #N/A Error

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

Highlighted

# Re: INDEX MATCH #N/A Error

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

Highlighted

# Re: INDEX MATCH #N/A Error

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

Highlighted

# Re: INDEX MATCH #N/A Error

Nope, I was wrong - will play with the model

Highlighted

# Re: INDEX MATCH #N/A Error

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

Highlighted

# Re: INDEX MATCH #N/A Error

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

Highlighted

# Re: INDEX MATCH #N/A Error

@Saeedom , you are welcome

Highlighted

# Re: INDEX MATCH #N/A Error

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 )