Struggling with looking up date

[m_13]

I'm still working on my appointments system with bells on and am making good progress.

With the help of some great articles online I've been able to create a range of appointments that once selected from the drop down list disappear so they can't be selected twice

However, I now want to populate the record for that appointment with the first name and surname of the person who the appointment was just booked out to.

The sheet that needs to lookup the firstname and surname is called Appointments. The sheet that has the drop down on is called BH Appointment Booking. I want cell B2 on Appointments to look at the appointment time in the cell B1 next to it and then go to BH Appointment Booking, look at column G, find the row that contains the appointment that matches and then put the first name of the person who booked that appointment (held in column B of that same row) into cell B2 on Appointments. I've tried to code this and have written the following (based on http://www.cpearson.com/excel/TablesAndLookups.aspx):

=OFFSET('BH Appointment Booking'!A1:G10,MATCH(Appointments!A2('BH Appointment Booking'!A1:G10,0,1,ROWS('BH Appointment Booking'!A1:G10),1),0)-1,0,1,1)

However, it brings up #REF!. When I try to amend any of the values in the last set of four (-1,0,1,1) it tries to change the first digit (-1) to * and then the number.

I want to also do this for cell B3 on appointments to find the last name of the person.

Can anybody help me understand where I am going wrong? Thanks

 

[Richard Schollar]

This snippet:

MATCH(Appointments!A2('BH Appoin

will fail because of the bit in red - what are you trying to do here? - there should be a comma after the first argument in match

 

[m_13]

Oops, don't know what happened to my text. It should have been like this:

=OFFSET('BH Appointment Booking'!A1:G10,MATCH(Appointments!A2, OFFSET('BH Appointment Booking'!A1:G10,0,1,ROWS('BH Appointment Booking'!A1:G10),1),0)-1,0,1,1)

The coding on the site I've been using as a reference is:

=OFFSET(LLTable,MATCH(F67,OFFSET(LLTable,0,1,ROWS(LLTable),1),0)-1,0,1,1)

 

[m_13]

Now showing #N/A which I'm guessing is telling me that there isn't any data that matches.

I do know however that on the BH Appointments Booking sheet a certain Test Tester has booked that appoinment.

 

[m_13]

Thanks to an example at www.ozgrid.com I've now got this working:

=INDEX('BH Appointment Booking'!$A$2:$G$200,MATCH(A2,'BH Appointment Booking'!$G$2:$G$201,0),2)

pulls in the first name perfectly and by changing 2 to 3 I get the surname.

It's lovely when things work