Vlookup, offset, match & countif

[jujuwillis]

Attached is a Student Record & Database.xlsx workbook and a document explaining the purpose and structure of it.



I have a slight problem, I think I know what the problem is, but I don't know to fix it, I know that it will be a der moment but reconciling the records to the sheet has left me a bit brain dead.

Short Explanation
The Student Sheet has a data validation list next to Member's Name, this populates the membership no and changes the picture.

This also populates all the rows from the Records sheet = to the member using the formula this is

=IFERROR(VLOOKUP(G$146,OFFSET(Membership_No,MATCH( MembNo,Membership_No,0),4,COUNTIF(Membership_No,Me mbNo),6),6,0),"Null")

This works fine. The problem I have is:

When selecting the member the records return the first record from the row above, which is a different member and omits the last record.

The Record sheet is sorted by Membership Name. I have tried to sort by membership no in the record sheet but still get the same result.

i.e Student Sheet is showing Barry Gibson, but the code AO1 is returning the result from Bob Ajob.

All names are completely false so there is no Data Proctection issues.

I have created this workbook following Mr Excel and Excelisin Trick 42 for the pictures in the Student Sheet which works fine, and Excel Magic Trick #158 Vlookup w 2 Variables as criteria on the Youtube channel. I have used VLOOKUP several times before, by this is my first time using OFFSET, MATCH & COUNTIF.
Thank you in advance.

I would appreciate some help or advice to correct this issue