Array lookup for text field when lookup value can be in one of many columns

[j-excel]

Hi, everyone - I've been searching for an answer to my problem for a long time, and nothing I find can help.

I am using excel 2007 on Windows XP.
I have a file with two tabs - one includes an array where each row contains between 1 to 8 unique values in up to 8 different columns, followed by a results cell. All of these are text. The results column never changes, but the unique values that go with that result can be in any of 8 columns.

The destination sheet has all of the unique values in one column, so if a Result on the first tab has 8 unique values, there will be 8 rows on the destination tab for that result. These are not in the same order as the first tab, and the order can change daily, so I don't want to do a simple linking to the first tab.

Does anyone have any ideas of how to return the Results from the first tab into the destination tab? I have tried everything I could find online, and nothing worked.

I don't want to use a nested vlookup because I have well over 5,000 rows on the destination sheet, each one doing 8 iferror vlookups.

I cannot change the format of the file; I update the file daily; I cannot use VBA, but I have used array formulas a bit.

Please let me know if there's any other data you need to help answer my question. Any help would really be greatly appreciated!!

Here is some more detail on the layout of my file:

<table style="border-collapse: collapse; width: 507pt;" width="678" border="0" cellpadding="0" cellspacing="0"><col style="width: 56pt;" width="75" span="8"> <col style="width: 11pt;" width="14"> <col style="width: 48pt;" width="64"> <tbody><tr style="height: 15pt;" height="20"> <td style="height: 15pt; width: 56pt;" width="75" height="20">Value 1</td> <td style="width: 56pt;" width="75">Value 2</td> <td style="width: 56pt;" width="75">Value 3</td> <td style="width: 56pt;" width="75">Value 4</td> <td style="width: 56pt;" width="75">Value 5</td> <td style="width: 56pt;" width="75">Value 6</td> <td style="width: 56pt;" width="75">Value 7</td> <td style="width: 56pt;" width="75">Value 8</td> <td style="width: 11pt;" width="14">
</td> <td style="width: 48pt;" width="64">Result</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Unique 1</td> <td>Unique 2</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>Result 1</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Unique 3</td> <td>Unique 4</td> <td>Unique 5</td> <td>Unique 6</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>Result 2</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Unique 7</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>Result 3</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Unique 8</td> <td>Unique 9</td> <td>Unique 10</td> <td>Unique 11</td> <td>Unique 12</td> <td>Unique 13</td> <td>Unique 14</td> <td>Unique 15</td> <td>
</td> <td>Result 4</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Unique 16</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>Result 5</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td colspan="2" style="height: 15pt;" height="20">Unique values = text</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>Result = text</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">On tab 1</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>On tab 1</td> </tr> </tbody></table>



Here is the layout of the destination tab:

<table style="border-collapse: collapse; width: 110pt;" width="146" border="0" cellpadding="0" cellspacing="0"><col style="width: 62pt;" width="82"> <col style="width: 48pt;" width="64"> <tbody><tr style="height: 15pt;" height="20"> <td style="height: 15pt; width: 62pt;" width="82" height="20">Value</td> <td style="width: 48pt;" width="64">Result</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Unique 4</td> <td>??</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Unique 1</td> <td>??</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Unique 10</td> <td>??</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Unique 16</td> <td>??</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Unique 2</td> <td>??</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Unique 11</td> <td>??</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Unique 3</td> <td>??</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Unique 5</td> <td>??</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Unique 15</td> <td>??</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Unique 14</td> <td>??</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Unique 6</td> <td>??</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Unique 7</td> <td>??</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Unique 13</td> <td>??</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Unique 8</td> <td>??</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Unique 12</td> <td>??</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Unique 9</td> <td>??</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">On tab 2</td> <td>
</td> </tr> </tbody></table>

 

[T. Valko]

Try this...

In the formula:

Table refers to your 5 row x 8 column table
Result refers to the column of results

=INDEX(Result,MATCH(1,MMULT(--(Table=A2),{1;1;1;1;1;1;1;1}),0))

Copy down as needed.

 

[j-excel]

Thank you so much!! That worked perfectly.
Thank you also for the fast reply....

 

[T. Valko]

You're welcome!

 

[drsarao]

#NAME?,
That was an elegant insightful solution.
I need a bit of explanation with the working.

I found this about MMULT in help:<link href="mkmsitstore:msohlp11.chm::/html/office10.css" type="text/css" rel="stylesheet">

1. The number of columns in array1 must be the same as the number of rows in array2, and both arrays must contain only numbers.
2. Array1 and array2 can be given as cell ranges, array constants, or references.
3. If any cells are empty or contain text, or if the number of columns in array1 is different from the number of rows in array2, MMULT returns the #VALUE! error value.
4. Formulas that return arrays must be entered as array formulas.
5. The result is an array with the same number of rows as array1 and the same number of columns as array2.

What I (mis)understand:

1. --(Table=A2) returns a 8x5 array of TRUE/FALSE converted to 1/0.
2. MMULT matrix multiplies this array with {1;1;1;1;1;1;1;1} and returns a ?8x5 array consisting of 1 (if Unique n found) and 0 otherwise.
3. This is searched by MATCH for exact match 1.
4. Somehow match returns 1 to 5 or #N/A if no match.
5. This is of course used by INDEX to return the Result n.

On going thru Formula Evaluation I found that upto step 2 it was as guessed. But the return array by MMULT was just 5! Though array 2 is only 5 elements, as per help the return array must have same number of rows as array1 and the same number of columns as array2. ??

Another question:
Can MATCH use an array for search? How is the returning number to be interpreted? Rows then columns?

Thanks.