Excel formula to identify unreturned uniform

[DChinery]

Dear community

I work in a school and through attempting to get more processes digitised, I am adapting my spreadsheet for passes from school and borrowed uniform. Sometimes students forget to return uniform and I don't always have the time to chase this as the rest of my job is very busy.

I have a sheet for the borrowed uniform and a sheet for passes, both with drop-down lists to select the correct student etc. 

What I now want is a formula to flag up a uniform issue when I am issuing a pass from school. I need it to tell me if an item of uniform has not been returned if the registration group and student name match exactly, anywhere in the table.

My current formula is 

=IF(AND([Reg Group]=Borroweduniform[Reg Group],[Full Name]=Borroweduniform[Full Name],Borroweduniform[Item returned?]="No"),"Yes","")

But this only seems to work if the details are on the same row in both sheets (which is highly unlikely to be the case). I need a way for excel to check the reg group and name from the pass outs sheet against the table for borrowed uniform and flag it if the item returned column says No.

I've attached a working copy with randomised names for reference. I just can't quite get my head around which functions I need to make this work.

Any help would be greatly appreciated.

Regards

Daniel

[#2]

@DChinery 

In general one of lookup functions shall be used, depends on your Excel version (XLOOKUP, VLOOKUP, INDEX/MATCH).

An issue is that you have no unique records in Borrowed uniform, same combination of Reg Group and Full Name could return bot Yes and No, e.g.

Something else (Date?) is required to identify exact combination.

[#3]

Hi Sergei

Yes there won't be any unique values, and some students may appear more than once on the borrowed uniform list. I need something to identify if there are any items that the student has not returned. I can't specify a date to look up as I won't know the date that the issue will be from.

The information provided in the sample file is all the relevant information. Nothing else matters regarding identifying an issue.

[#4]

@DChinery 

You better know business logic, but without such identification you can't link data in both tables. More exactly you can, but in some cases result will be incorrect.

[#5]

@DChinery 

1. Create SKU numbers for the items:

How to Create an SKU Number for a Product? Here's An Ideal Guide - Business Zeal

2. Create a Receipt form with an associated receipt number for them to sign what they borrowed

(Complete with prices that they have to pay if the item is damaged) with a check out and a check in Date and Signature (theirs and yours) and Time Stamp, with the SKU numbers of the items listed within the receipt

3. Scan these receipts so you can print them and not lose them.

4. Print another receipt when they return the items (with a different receipt number)

5. record the check out receipt numbers as well as the check in receipt numbers in your spreadsheet

You can create a spreadsheet receipt for that automatically assigns receipt number based on year, month day, hour, minute that way just by looking at the receipt number it will already inform you what to look for and where to look.

You can save these receipt as a csv file into a folder where you can do a power query by folder so all your records are dynamically created and integrated when following the process so there's no extra work, when you're done with the check out process so will your recording process.

The process is they email the request to you by filling out the form (they will do most of the work for you and you have a record for the request, and when they have returned the items you will email them their signed receipt so you have a record of the check out process as well.

You can email them once their request has been fulfilled to come pick up their items.  Set a timeline when they should make the request either 2 days from the time they need the items or whatever timeline you decide.