IF And False then next row

[mardimus]

I have a table of data:

Value	Function	ID	Current Returned List	Expected Returned List
3	Create	1000	1000	1000
2	Do Not Create	1200		1111
3	Create	1111	1111	1456
1	Do Not Create	2222
2	Create	3333
3	Do Not Create	4444
3	Create	1456	1456

<tbody>
</tbody>

The objective: is to check if column A has a value of 3 andthen if B has text “Create” and then list the ID in order with no blanks inbetween (rows), a list of True values, skipping False.

My current formula works but displays blanks:=IF(AND(A:A=3,B:B=”Create”),C2,””)

Thanks for your help!

 

[sheetspread]

Excel 2010
	A	B	C	D	E
1	Value	Function	ID		Expected Returned List
2	3	Create	1000		1000
3	2	Do Not Create	1200		1111
4	3	Create	1111		1456
5	1	Do Not Create	2222
6	2	Create	3333
7	3	Do Not Create	4444
8	3	Create	1456
Sheet2

Cell Formulas
Range	Formula
E2	{=INDEX($C$1:$C$8,SMALL(IF($A$2:$A$8=3,IF($B$2:$B$8="Create",ROW($C$2:$C$8))),ROW(A1)))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Marcelo Branco]

Assuming data in A1:C8, headers in row 1, try

Array formula in D2 copied down
=IFERROR(INDEX($C$2:$C$8,SMALL(IF(A$2:A$8=3,IF(B$2:B$8="Create",ROW(C$2:C$8)-ROW(C$2)+1)),ROWS(D$2:D2))),"")
confirmed with Ctrl+Shift+Enter, not just Enter

M.