IF function false - look at next row

[danilosvensson]

Hello,


I need some help to create an If-statement.
On one sheet I have names and a bunch of information.


I´ve made a simple example with names an colors.
On one sheet I have this information
On the other sheet i want to list all the names which meets the condition of blue or green color.

	A	B
1	Name	Color
2	Ted	red
3	Jody	blue
4	Tina	blue
5	Brody	green
6	Henrik	red
7	Layla	blue
8	Ben	red

The formula I´ve used is:
=IF(OR(Sheet1!B2="green";Sheet1!B2="blue");Sheet1!A2;"")


But then it looks like this:

	A
1	Name
2	Empty
3	Jody
4	Tina
5	Brody
6	Empty
7	Layla
8	Empty

I Want it to look like this:

	A
1	Name
2	Jody
3	Tina
4	Brody
5	Layla

So basicly what I want is to write a formula that looks at the next row if the statement is false and the next one after that and so on uyntil it finds a statement that is true.

If you could help me I would be forever grateful

[RobertMika]

Hello and welcome.
names.xlsx

[danilosvensson]

Tank you very much for your help!

I´ll try to apply it to my "real" document but it looks right to me!

/Danilo