(Re-sent: response sent earlier via OE, but didn't get thru')
One play using non-array formulas ..

Sample construct available at:
http://cjoint.com/?mdctMl4MQJ
Creating A Search Database_ajaffer_misc.xls

In Sheet1, assume the source table is in cols A to E,
data in row2 down, with the key col "Season" in col B

Put in F2:
=IF(OR(Sheet2!$A$1="",B2=""),"",IF(Sheet2!$A$1=B2, ROW(),""))
Copy F2 down to say, F20 to cover the max expected extent of data
(Leave F1 empty)

In Sheet2,
Cell A1 will be reserved for input, e.g. : Season 3

A2:E2 contains the same labels
Title, Season, Air Date, Star Date, Synopsis

Put in A3:
=IF(ISERROR(SMALL(Sheet1!$F:$F,ROWS($A$1:A1))),"", INDEX(Sheet1!A:A,MATCH(SMALL(Sheet1!$F:$F,ROWS($A$ 1:A1)),Sheet1!$F:$F,0)))
Copy across to E3, fill down to E21
(cover the same extent as was done in Sheet1's col F)

Sheet2 will return the required search results from Sheet1 for the season
input in A1, with all results neatly bunched at the top

--
And instead of manual input into A1, we could set up a Data Validation
droplist there for easy selection

In a new sheet: DV
put in A1: Season 1, fill down to A10 (say)

Then create a defined range "Season" via:
Click InsertNameDefine
Names in workbook: Season
Refers to: =DV!$A$1:$A$10

Select Sheet2'sA1 and click Data Validation
Set it as
Allow: List
Source: =Season
Click OK

--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"ajaffer" wrote in
message ...

Hi, i need help creating a search database using excel lookup functions:
vlookup, match and index. The criteria is below:

I have a sheet filled with data. Data Sheet:
Title, Season, Air Date, Star Date, Synopsis (Respectively. Data is
sorted alphabetically by Title)

I have to create another worksheet, in which a user types a Season
number, and the formula will display the related results. For example:
User types in Season 1
Sheet displays:

Title Air Date Star Date Synopsis
Pilot xxx xxxx yyyyy
New aaa bbb zzzz
etc..etc..

Thanks


--
ajaffer
------------------------------------------------------------------------
ajaffer's Profile:
http://www.excelforum.com/member.php...o&userid=29316
View this thread: http://www.excelforum.com/showthread...hreadid=490340