Trouble Mixing IF & Vlookup to help manage inventory

Okay so this is for a student-run bike rental business. We don't have the budget for a professional inventory system so I am trying to set up something that will work in excel. The scope is not very big relatively so I think its possible. Essentially, the renters are split into categories based on how long they're renting (ex: tabs 1 & 2 on sample workbook). Then. they all choose from same pool of bikes. So I want to be able to list the bike number each person is renting on tabs 1 and 2. Then based on which tab each bike on, I want the status of the bike to show on tab 3. Here's what I have so far (Was not working, obviously):

=IF(VLOOKUP(A2,'Year Rentals'!D:D,1,FALSE)='Bike List'!A2,"Rented for Year",IF(VLOOKUP('Bike List'!A2,'1st Semester'!D:D,1,FALSE)='Bike List'!A2,"Rented for Semester","Not Rented"))

It spits out the right value if its rented for the year, but not if its rented for semester or not rented - gives me #N/A. I have included everything you should need in a sample workbook

Thanks for the help!