creating a search model

  • Thread starter Thread starter ajaffer
  • Start date Start date
A

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
 
On second sheet, assuming Season number in A2 on sheet 2, and Season i
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 everythin
fron the matching row in sheet1, including the Season. You can delet
this column on sheet 2 if you wish.

If this is a frequent occurrence and you have thousands of rows i
sheet1, you could separateout the MATCH function into its own cell an
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 talkin
big time ...


HTH

Decla
 
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
 
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
 
Back
Top