Need help sorting for formulas

[52hell]

I have a long list of items names on A column, the items are for rental purposes, and in order to keep track of its rent history, the item returned will have a returned date on the R column, and have strikethrough. But then a new row of the returned item will be added to indicate that it's open for rental, so the item with the same name can have multiple duplicates, but only 1 active row. These are on sheet 1.

In addition to that, new items will be purchased and stored up as an replacement, backups or as a new product for rental. These are on sheet 2.

So on sheet 2, I use Vlookup function to search whether the new product is rented out, if it is, I want it to return the person/company that rented it, the formula is =IFERROR(VLOOKUP($A2,'sheet 1!'$A$4:$R$10875,9,FALSE,"")
But it only listed the first item match of the search table, and that item is a returned item.

I want to know if I want to only match the current active one, how should the formula be? I used IF function, but then I realized I don't know how to set the criteria if column R = "", then Vlookup, because not every item on sheet 2 is listed on sheet 1. So please help for a easy formula. And I'm on EXCEL 2003, so, no fancy new functions, thank you very much.

 

[jarjarbingie]

please post sample data..

I have a long list of items names on A column, the items are for rental purposes, and in order to keep track of its rent history, the item returned will have a returned date on the R column, and have strikethrough. But then a new row of the returned item will be added to indicate that it's open for rental, so the item with the same name can have multiple duplicates, but only 1 active row. These are on sheet 1.

In addition to that, new items will be purchased and stored up as an replacement, backups or as a new product for rental. These are on sheet 2.

So on sheet 2, I use Vlookup function to search whether the new product is rented out, if it is, I want it to return the person/company that rented it, the formula is =IFERROR(VLOOKUP($A2,'sheet 1!'$A$4:$R$10875,9,FALSE,"")
But it only listed the first item match of the search table, and that item is a returned item.

I want to know if I want to only match the current active one, how should the formula be? I used IF function, but then I realized I don't know how to set the criteria if column R = "", then Vlookup, because not every item on sheet 2 is listed on sheet 1. So please help for a easy formula. And I'm on EXCEL 2003, so, no fancy new functions, thank you very much.

 

[Takae]

I think this is wrong.
=IFERROR(VLOOKUP($A2,'sheet 1!'$A$4:$R$10875,9,FALSE,"")


=IFERROR(VLOOKUP($A2,'sheet 1'!$A$4:$R$10875,9,FALSE),"")
(And the sheet name has a space: sheet_1.)

 

[52hell]

=IFERROR(VLOOKUP($A2,'2013-2014'!$A$4:$R$65536,9,FALSE),"")
???this was the exact formula I used, but I want to know how to set the criteria that it will eliminate the other returned items and retrieve the only active item. Thank you!

 

[52hell]

I'm sorry I don't know how. Could you help me with a correct formula, please!