Use index to look up and return multiple unique values

[dleonard78]

Hi,

I need to be able to find multiple values where the same thing occurs. This also needs to be able to use subtotal so that if I filter it only returns the names shown. Poor English so I'll use this table to explain:

I need to fins any pupils name where they have particular values. So for example, if the value Im looking for a pupil who has a 4B in column 7 and a C in column 8, this needs to create a list which in this case would be:

Steve
Charlie
Rosie

Im assuming this is an index formula but I may very well be wrong!!
thanks
[TABLE="class: cms_table, width: 461"]
<tbody>[TR]
[TD]Ben[/TD]
[TD]WBRI[/TD]
[TD]M[/TD]
[TD]N[/TD]
[TD]N[/TD]
[TD]P[/TD]
[TD]N[/TD]
[TD]F[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Dan[/TD]
[TD]WBRI[/TD]
[TD]M[/TD]
[TD]N[/TD]
[TD]N[/TD]
[TD][/TD]
[TD]5B[/TD]
[TD]B[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Steve[/TD]
[TD]MWOE[/TD]
[TD]M[/TD]
[TD]N[/TD]
[TD]N[/TD]
[TD][/TD]
[TD]4C[/TD]
[TD]C[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Paul[/TD]
[TD]WBRI[/TD]
[TD]M[/TD]
[TD]N[/TD]
[TD]N[/TD]
[TD][/TD]
[TD]4B[/TD]
[TD]C[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sarah[/TD]
[TD]ABAN[/TD]
[TD]M[/TD]
[TD]N[/TD]
[TD]N[/TD]
[TD][/TD]
[TD]4C[/TD]
[TD]C[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Joe[/TD]
[TD]MWBC[/TD]
[TD]M[/TD]
[TD]N[/TD]
[TD]N[/TD]
[TD]P[/TD]
[TD]4C[/TD]
[TD]D[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Craig[/TD]
[TD]MWBC[/TD]
[TD]M[/TD]
[TD]N[/TD]
[TD]N[/TD]
[TD][/TD]
[TD]5B[/TD]
[TD]B[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Rachel[/TD]
[TD]WBRI[/TD]
[TD]M[/TD]
[TD]N[/TD]
[TD]N[/TD]
[TD]A[/TD]
[TD]4B[/TD]
[TD]D[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Charlie[/TD]
[TD]WBRI[/TD]
[TD]M[/TD]
[TD]N[/TD]
[TD]N[/TD]
[TD][/TD]
[TD]4B[/TD]
[TD]C[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Finn[/TD]
[TD]WBRI[/TD]
[TD]M[/TD]
[TD]N[/TD]
[TD]N[/TD]
[TD][/TD]
[TD]5B[/TD]
[TD]B[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Oli[/TD]
[TD]WBRI[/TD]
[TD]M[/TD]
[TD]N[/TD]
[TD]N[/TD]
[TD][/TD]
[TD]5C[/TD]
[TD]B[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Rosie[/TD]
[TD]APKN[/TD]
[TD]F[/TD]
[TD]N[/TD]
[TD]N[/TD]
[TD][/TD]
[TD]4b[/TD]
[TD]C[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Reta[/TD]
[TD]REFU[/TD]
[TD]F[/TD]
[TD]N[/TD]
[TD]N[/TD]
[TD][/TD]
[TD]4B[/TD]
[TD]B[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Ben[/TD]
[TD]WBRI[/TD]
[TD]M[/TD]
[TD]N[/TD]
[TD]N[/TD]
[TD][/TD]
[TD]5A[/TD]
[TD]B[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Dan[/TD]
[TD]WBRI[/TD]
[TD]M[/TD]
[TD]N[/TD]
[TD]N[/TD]
[TD][/TD]
[TD][/TD]
[TD]D[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Steve[/TD]
[TD]BCON[/TD]
[TD]F[/TD]
[TD]Y[/TD]
[TD]N[/TD]
[TD][/TD]
[TD]3C[/TD]
[TD]E[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Paul[/TD]
[TD]MWAS[/TD]
[TD]F[/TD]
[TD]N[/TD]
[TD]N[/TD]
[TD][/TD]
[TD]4B[/TD]
[TD]D[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sarah[/TD]
[TD]WBRI[/TD]
[TD]F[/TD]
[TD]N[/TD]
[TD]N[/TD]
[TD][/TD]
[TD]5A[/TD]
[TD]A*[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Joe[/TD]
[TD]WBRI[/TD]
[TD]M[/TD]
[TD]N[/TD]
[TD]N[/TD]
[TD][/TD]
[TD]3B[/TD]
[TD]D[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Craig[/TD]
[TD]WBRI[/TD]
[TD]F[/TD]
[TD]N[/TD]
[TD]N[/TD]
[TD][/TD]
[TD]5B[/TD]
[TD]B[/TD]
[/TR]
</tbody>[/TABLE]

 

[ChrisR]

Have a look at this..........From Microsoft's site

[h=2]Look up one value and return multiple corresponding values[/h]The formula to look up one value and return multiple corresponding values can be stated as this: Identify the row numbers that contain the name "Ashish" in column A, return the corresponding values from column B, and remove possible error values.
Top of Page
[h=2]Identify row numbers[/h]Enter "Ashish" in cell A10. Then, enter the following array formula (CTRL+SHIFT+ENTER) in cell B10 to determine the row number of a corresponding value:

<code>=SMALL(IF($A$1:$A$7=$A$10,ROW($A$1:$A$7)),ROW(1:1))</code></pre>When you enter or fill this formula in subsequent cells, the formula returns the row numbers for each subsequent corresponding value, in this case, 4 and 7.
Top of Page
[h=2]Return corresponding values from column B[/h]Now, modify the preceding formula to return the corresponding value (and not the row number) and enter the formula as an array formula (CTRL+SHIFT+ENTER):

<code>=INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=$A$10,ROW($A$1:$A$7)),ROW(1:1)),2)</code></pre>The first corresponding value is 234, the value that corresponds to the first occurrence of the name "Ashish".
When you enter or fill this formula in subsequent cells, the formula returns the subsequent corresponding values of 534 and 834.