formula to return next valid cell

I have two columns where column A could have repeated text/values and column B have a different corresponding value. Example: Column A has Toronto in rows 1 and 2 but column B has Harry and John in rows 1 and 2. I need a formula that can do a lookup on column A and return John in column B Row 2 if Harry has already been returned from a previous lookup. So the formula needs to return the next valid cell if the result is equal to another cell.
Hope this is clear.
All I have is if(vlookup(B5,sheet!1$A:$B,2,false)=sheet2!C10, and I haven't got any further with anything I have tried.
Let me know if further clarification is required. Any help is appreciated!
Thanks

You can use an array formula, entered using Ctrl-Shift-Enter:

Formula:

=INDEX(Sheet1!B:B,SMALL(IF(Sheet1!$A$1:$A$100=$B$5,ROW($A$1:$A$100)),ROW(A1)))

then copied down until it returns blanks.

Can you explain a little, I am trying to adjust your formula to match my spreadsheet and I can not get it to work......???

Post your workbook with an explanation of what you want, and I will modify the workbook and re-post it.

Hi
Attached is an example of what I am looking for.
Thanks again for the help!

The formula works! Brilliant! Thank you very much Tony... life saver

BL

I have one other question
I didn't add in the worksheet that I wanted the formula to skip columns so instead of the having the formula work in the column to the immediate right I need it to work 8 columns over? Would you be able to modify the formula to accomodate?
(After going back I realized I missed this impt criteria)... Thanks again...