If the IF function result is false then check the next cell until get a true result

Hello,

I am trying to use the IF function to return names from column A where column B ="R". I need to remove the false or blank cells where column B does not = "R" so I just have the appropriate names (I know I could just filter but I need this to be automated in another sheet)

eg
Original Data
Column A Column B
1 Mike R
2 Stephen
3 Lisa R
4 Graham
5 Sarah
6 Nicola R


What If Returns
Column A
1 Mike
2
3 Lisa
4
5
6 Nicola


What I want
Column A
1 Mike
2 Lisa
3 Nicola


Hope this makes some sense to someone!!!

Thanks in advance

In D2, an array-entered formula, copied down:

=IFERROR(INDEX(A:A,SMALL(IF($B$2:$B$7="R",ROW($B$2:$B$7)),ROWS(D$2:D2))),"")

Array Formulae are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...

datasheet:
c1="R"&countif($B$1:$B1,$B1) and drag down


sheet result:
a1=IFERROR(INDEX(datasheet!$A$1:$C$6,Match("R"&ROW(),datasheet!$C$1:$C$6,0),1),"") and drag down

Brilliant, this has done exactly what I was looking for!

Thank you very much Glenn Kennedy!

You're welcome and thanks for the rep.