Thread: ALMOST SOLVED Vlookup, offset, match & countif

Hi

I have a workbook (attached)

The "result" sheet is the Student Sheet in the workbook


=IFERROR(VLOOKUP(O$13,OFFSET(Membership_No,MATCH(MembNo,Membership_No,0),4,COUNTIF(Membership_No,Mem bership_No),6),6,0),"Null")

is the formula used in the cells for the results - this is from the worksheet Records.


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

As a test, the Student is Robert Nutbush and has an entry for all the codes and a date of training as 16 March and the trainer is again Robert Nutbush. As you can see from the Student Sheet only half of the information is returning a result, the rest is coming back NULL and the trainer is not always Robert Nutbush.

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

Thank you for your help so quickly, I did have an issue with just a couple of cells, but that was due to a zero instead of an O and two members having the same membership number.

Thanks again

Julie

Originally Posted by Bob Phillips

Instead of using

COUNTIF(Membership_No,MembershipNo)

in your formulae, try

COUNTIF(Membership_No,MembNo)

Attached is a updated Student Record & Database.xlsx document.

Previous thread Re: SOLVED Vlookup, offset, match & countif

I have a still 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 Explaination
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")

Bob Phillips came to my rescue with this issue

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.

Help and inspiration would be very much appreciated.

Attached is a updated Student Record & Database.xlsx document.

Previous thread Re: SOLVED Vlookup, offset, match & countif

I have a still 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 Explaination
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")

Bob Phillips came to my rescue with this issue

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.

Help and inspiration would be very much appreciated.


Thanks

Ju