creating a search model

[ajaffer]

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

[DOR]

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]

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]

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