# Struggling with looking up date

#### m_13

##### New Member
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):

Code:
``=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

### Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

#### Richard Schollar

##### MrExcel MVP
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

##### New Member
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

##### New Member
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

##### New Member
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

Replies
4
Views
290
Replies
9
Views
176
Replies
17
Views
562
Replies
0
Views
324
Replies
0
Views
91

1,190,646
Messages
5,982,114
Members
439,755
Latest member
nicos18

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back