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
Saeedom , you are welcome
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