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