Search Engine without using Macros or VBA

[Macacidic]

Hi All

I have a large list of data that I would like to add a search function to, what I am trying to do is type some or all information in the "Search for" boxes and have a list of results returned in the "Search results". I would prefer a formula rather than VBA/Macros.

Thanks for your help.

Mac

Search For
Item 1	Item 2	Item 3	Item 4	Item 5
Orange		Monday
Search Results
Orange	Cream	Monday	Red	2
Orange	Cream	Monday	Blue
Table of Information
Item 1	Item 2	Item 3	Item 4	Item 5
Orange	Cream	Monday	Red	2
Apple	Ice Cream	Monday	Red	5
Pear	Juice	Thursday		6
Apple	Juice	Friday	Green	9
Orange	Cream	Monday	Blue
Apple	Ice Cream	Friday	Pink	4

<colgroup><col width="101" style="width: 76pt; mso-width-source: userset; mso-width-alt: 3232;" span="5"> <tbody> </tbody>

 

[oldbrewer]

Search For

Item 1

Item 2

Item 3

Item 4

Item 5

Orange

Monday

Search Results

Orange

Cream

Monday

Red

2

Orange

Cream

Monday

Blue

Table of Information

Item 1

Item 2

Item 3

Item 4

Item 5

helper

Orange

Cream

Monday

Red

2

1

a simple formula in a helper column

Apple

Ice Cream

Monday

Red

5

0

labels selected rows with a 1

Pear

Juice

Thursday

6

0

Apple

Juice

Friday

Green

9

0

a fairly simple formula with large function

Orange

Cream

Monday

Blue

1

and offset then lists the rows below

Apple

Ice Cream

Friday

Pink

4

0

although the 2 1's look identical they have a tiny fraction

Orange

Cream

Monday

Blue

0

1

of the row number added to them to make them unique

Orange

Cream

Monday

Red

2

2

otherwise the large function would return the same row twice

3

4

5

formula for the first Orange

6

#num !

#num !

#num !

#num !

#num !

7

=IF(LARGE($F$13:$F$18,$F20)=0,"",OFFSET($F$12,MATCH(LARGE($F$13:$F$18,$F20),$F$13:$F$20,0),-5))

#num !

#num !

#num !

#num !

#num !

8

#num !

#num !

#num !

#num !

#num !

9

the -5 at end is reduced by 1 across the row of course

#num !

#num !

#num !

#num !

#num !

10

the #num ! Are because 7 to 10 are greater than the 6 rows of original data

the numbers 1 to 10 on the right can be hidden

if the 0 in item 5 column is a nuisance it can be error trapped out

<colgroup><col span="19"></colgroup><tbody>
</tbody>