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

Attached Files

	Student Records & Database.xlsx (319.7 KB, 27 views)
	Student Record Sheet.docx (50.3 KB, 16 views)

Hi Julie,

First, let's try to understand the problem with the formulae by looking at a specific example. Changing the member's name in G3 to Daniel Richman, I'll pick the formula in cell Y168 as an example - this one is pulling pack data for David Saying ADVD02 when you don't want it to be.

The formula is this:
Let's break it down into it's component parts.
This is fine - it represents the number of records for that member on the records sheet.


This is because the Membership_No named range is defined as Records!$B$2:$B$2850.
The first row of data for Daniel Richman begins on row 202, which is the 201st value in B2:B2850.


We can now simplify the OFFSET() part of the formula to this:




The 201 component is the row offset. Let's think about what the row offset returns.

• A row offset of 1 from B2:B2850 would return B3:B2851.
• A row offset of 2 from B2:B2850 would return B4:B2852.
• A row offset of 100 from B2:B2850 would return B102:B2950.
• So a row offset of 201 from B2:B2850 would return B203:B3051

So this is where the problem lies in the formula. You want it to return a range starting from row 202 when it is actually returning a range starting from row 203.

Accounting for the other arguments in OFFSET (column/width/height):
We can confirm this with a quick check using some VBA:
So the problem is effectively that MATCH() operates off a 1-based index whereas OFFSET() operates off a 0-based index. By that, I mean that if the first cell in a given range is a match, then MATCH() returns 1, whereas to return the first cell in a range using OFFSET() you would use 0.

To compensate for this, you just need to adjust the return value of MATCH() by subtracting 1.

__________________
Colin

RAD Excel Blog

Thank you Colin (and Tony).

Did a find and replace of the code and it works beautifully.

I have spent many a frustrating hour looking at it. Checking ranges etc, but I don't think I would of ever figured it out.

But you only know what you know and I know the more I learn the less I know.

But I also know that I won't forget it as we learn by our mistakes (or lack of knowledge).

Much appreciated, I'll mark the problem solved, if I can't find out how to that simple thing can you do it for me and mark it as an EXCELlent response from an EXCELlent forum.