ExcelBanter - List of Qualifying Values

Here's one way to model it up for automated multiple line returns
dynamic to specifiable multiple criteria in another sheet ...

Illustrated in this sample:
http://freefilehosting.net/download/43j46
AutoList multiple results by criteria in another sheet.xls

Construct:
Source data assumed in sheet: x, data from row2 down,
Name, Region in cols A & B,
with subject area headers in C1 across

In another sheet: y,
Create a DV in D1 to select the subject area,
eg: Subject Area 2

Assume the "from-to" number inputs
for the subject scores range will be made in F4:F5

Place
In A2:
=IF($D$1="","",IF(AND(OFFSET(x!A2,,MATCH($D$1,x!$1 :$1,0)-1)=$F$4,OFFSET(x!A2,,MATCH($D$1,x!$1:$1,0)-1)<=$F$5),ROW(),""))
Leave A1 empty.

In B2:
=IF(ROWS($1:1)COUNT($A:$A),"",INDEX(x!A:A,SMALL($ A:$A,ROWS($1:1))))
Copy B2 to C2

In D2:
=IF(ROWS($1:1)COUNT($A:$A),"",INDEX(OFFSET(x!$A:$ A,,MATCH($A$1,x!$1:$1,0)-1),SMALL($A:$A,ROWS($1:1))))
Select A2:D2, copy down to cover the max expected extent of source data in
x, say down to D100? Minimize/hide col A. Cols B to D returns the required
results from x (Name-Region-Subject Area), with all lines neatly packed at
the top, dynamic to the specified criteria, ie the subject area selected in
D1, and the scores range in F4:F5.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,000 Files:370 Subscribers:66
xdemechanik
---
"abergman" wrote:
I have a list of Data as follows (commas indicate a new column)
Name, Region, Subject Area1, Subject Area 2, Subject Area 3

Under each of the Subject areas is a value (1-4) that indicates this persons
ability.
Is there a way that I can an automated list for each of the subject areas
that shows all individuals that are above a certain level (ie 3-4):
Subject Area 2:
Name, Region, 4
Name, Region. 4
Name, Region, 3

Vlookup can only return the first individual.
Thank you for any help you can give!