Searching & returning

**acoger** · 06-08-2012, 03:18 AM

Hello everybody!

I need a solution for my next problem.

I have two sheets. In the first one I want to have the data from the second one, like this:

I write the seraching value in one field of the first sheet:

f.e.: "car"

In the second sheet I have the following datas f.e.:

car	new	red	3
car	new	blue	2
bycicle	old	white	10
bycicle	new	pink	1

I want that after typing the "car" or " bycicle " in the field of the first sheet, a little bit under this field, give me all the data's from car, liket this:

car	new	red	3
car	new	blue	2

How can I do this?

Thx in advance!!

**Steffen Thomsen** · 06-08-2012, 05:05 AM

A quick question... is the list on sheet 2 alfabetized, or could car appear in row 2 and 40?

**jraj1106** · 06-08-2012, 05:19 AM

Hi

Type "car" in B1
Try this:

Please Login or Register  to view this content.

searchval.xlsm

**pike** · 06-08-2012, 05:30 AM

A1 type Car

B1 =IF(ISERROR(INDEX(Sheet2!$B$1:$C$7,SMALL(IF(Sheet2!$A$1:$A$7=$A$1,ROW(Sheet2!$A$1:$A$7)),ROW(1:1)),1)),"",INDEX(Sheet2!$B$1:$C$7,SMALL(IF(Sheet2!$A$1:$A$7=$A$1,ROW(Sheet2!$C$1:$C$7)),ROW(1:1)),1)) with control shift enter and fill down

C1=IF(ISERROR(INDEX(Sheet2!$B$1:$C$7,SMALL(IF(Sheet2!$A$1:$A$7=$A$1,ROW(Sheet2!$A$1:$A$7)),ROW(1:1)),2)),"",INDEX(Sheet2!$B$1:$C$7,SMALL(IF(Sheet2!$A$1:$A$7=$A$1,ROW(Sheet2!$C$1:$C$7)),ROW(1:1)),2)) with control shift enter and fill down

ect.....