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=490341

On second sheet, assuming Season number in A2 on sheet 2, and Season is
in column B in sheet1, in B2 enter

=INDEX(Sheet1!A:A,MATCH($A$2,Sheet1!$B:$B,0))

and drag across as many columns as you need. This will pull everything
fron the matching row in sheet1, including the Season. You can delete
this column on sheet 2 if you wish.

If this is a frequent occurrence and you have thousands of rows in
sheet1, you could separateout the MATCH function into its own cell and
then refer to it from the INDEX formulas. Say you put it in X1,

X1: =MATCH($A$2,Sheet1!$B:$B,0)

B2: =INDEX(Sheet1!A:A,$X$1)

That will be a little more efficient, at least a few milliseconds.
Heck, a millisecond here and milllisecond there and soon you're talking
big time ...


HTH

Declan


--
DOR
------------------------------------------------------------------------
DOR's Profile: http://www.excelforum.com/member.php...o&userid=29088
View this thread: http://www.excelforum.com/showthread...hreadid=490341

I have just re-read your post and realize you may be looking for for
multiple rows from sheet 1, all having the same season number. If
that is true, you need a different approach.

For efficiency purposes, I would use a helper column in the second
sheet, say column A. Set aside a cell, say A1 to carry a count of the
instances of season number in the first sheet. Assume B1 contains
season number

A1: =COUNTIF(Sheet1!B:B,B1)

A2:
=IF(ROW(1:1)$A$1,"",SMALL(IF(Sheet1!$B$2:$B$1000= $B$1,ROW(INDIRECT("1:"&ROWS(Sheet1!$B$2:$B$1000))) ,""),ROW(1:1)))

Enter as array formula Ctl+Shift+Enter. Drag down as far as you think
you need to extract all shows from one season.

Then in

C2: =IF($A2="","",INDEX(Sheet1!A:A,$A2))

Drag down and across as far as necessary. Yo can delete the season
column from this range afterwards.

This should do it.

Sorry about the bum steer.

Declan O'R


--
DOR
------------------------------------------------------------------------
DOR's Profile: http://www.excelforum.com/member.php...o&userid=29088
View this thread: http://www.excelforum.com/showthread...hreadid=490341

OOOPS!

I gave you the wrong formula for A2: it should be

=IF(ROW(1:1)$A$1,"",SMALL(IF(Sheet1!$B$2:$B$1000= $B$1,ROW($2:$1000),""),ROW(1:1)))

enterd as array - Ctl+SHift+enter and dragged down.

It looked OK, but it wasn't. It should be OK now.

Sorry about that.

Declan O'R


--
DOR
------------------------------------------------------------------------
DOR's Profile: http://www.excelforum.com/member.php...o&userid=29088
View this thread: http://www.excelforum.com/showthread...hreadid=490341