Help with lookup formula to return "Available" or "Unavailable" based on last entry

Hi everyone,

I have an excel workbook that I am currently using for people who want to borrow a piece of equipment (please see attached). In the first worksheet (Status) I have a list of the equipment with individual ID numbers against each. In the second worksheet (Booking Out) I will have a list of entries of equipment booked in and out with different dates and times. I need a If and lookup formula to calculate firstly based on the Last Out Date/Time entry "Unavailable" and last In Date/Time Available and populate the Status column of the first worksheet against the correct asset ID what the current status is. Sorry if this sounds a bit confusing but I have attached the basic spreadsheet to show what I am trying to do.

Thanks

Antony

In E2 copied down

=IF(COUNTIF('Booking Out'!$A$2:$A$60, A2)=COUNTIFS('Booking Out'!$A$2:$A$60, A2, 'Booking Out'!$F$2:$F$60, ">0"),"Available", "Unavailable")

I'm not sure the solution given is quite enough. Consider the fact that an item of equipment might be new and not yet hired out - it won't be on the bookings sheet, so you need to account for that. Try this:

=IFERROR(IF(COUNTIF('Booking Out'!$A$2:$A$60, A2)=COUNTIFS('Booking Out'!$A$2:$A$60, A2, 'Booking Out'!$F$2:$F$60, ">0"),"Available", "Unavailable"),"Available")

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.