Retrieving lines from a dataset that match more than one criteria using array formula

[Sztofts]

Hi

I am having trouble getting this simple task completed. I want to retrieve the lines from the dataset which match the criteria in the criteria range as there's more than one criteria, the lines retrieved must match just one of the criteria to appear in the result.

The formula I am having trouble with is:

'=SMALL(IF($C$3:$C$8=$H$11,ROW($B$3:$B$8)-2,IF($C$3:$C$8=$H$12,ROW($B$3:$B$8)-2,IF($C$3:$C$8,$H$13,ROW($B$3:$B$8)-2))),ROW(1:1))

where
C3:C8 is the data set
H11:H13 is the criteria (three cells)

There are curly brackets around my working formula which do not appear in the formula above.

Any help greatly appreciated.

 

[AhoyNC]

Try one of these formulas. Formula in D1 if you have Excel 2010 or later or formula in F1 which must be enter with CTRL-SHIFT-ENTER. Drag formulas down as needed.

Excel Workbook
	C	D	E	F	G
2	Data
3	test1	1		1
4	test2	4		4
5	test3	7		7
6	test4	8		8
7	test5
8	test6
9	test4
10	test8
11	test9				test1
12					test4
13					test8
Sheet

 

[Sztofts]

Many thanks, the formula worked first time