How do i look up a time in a row data that is specific to a name

[john dalton]

Hi,

I am trying to display a date and time from a list that uses 2 criteria to lookup the required values (Name and Appointment Nunber). Please see attached sheet,

Thanks in advance.

[Special-K]

in C2
=INDEX(Data!A$2:D$11,MATCH(B2,IF(A2=Data!A$2:A$11,Data!B$2:B$11,""),0),3)
in D2
=INDEX(Data!A$2:D$11,MATCH(B2,IF(A2=Data!A$2:A$11,Data!B$2:B$11,""),0),4)

NOTE: These are Array formulas, enter using Ctrl-Shift-Enter

[john dalton]

That works great but how would i incorporate it into the following formula?

=IF(VLOOKUP($F$4,client_list2,30,FALSE)="","",VLOOKUP($F$4,client_list2,29,FALSE)&" "&VLOOKUP($F$4,client_list2,30,FALSE)&" "&$X$4&" "&TEXT($AC$4,"dddd dd mmmm yyyy"&" "&"at"))

I need the result to follow the formulas actions soI would need it to look something like this

=IF(VLOOKUP($F$4,client_list2,30,FALSE)="","",VLOOKUP($F$4,client_list2,29,FALSE)&" "&VLOOKUP($F$4,client_list2,30,FALSE)&" "&$X$4&" "&TEXT($AC$4,"dddd dd mmmm yyyy"&" "&"at"&" "&INDEX(Appointments!B5:H1233,MATCH(2,IF($F$4=Appointments!B5:B1233,Appointments!H5:H1233,""),0),5)

It wont accept the new longer formula even as a array formula any ideas?

Cheers

JD

[john dalton]

Also is there a shorter way to get the same result?

[azumi]

in C2
=VLOOKUP($A$2,IF(Data!$B$2:$B$11=$B$2,Data!$A$2:$D$11,""),3,FALSE)

in D2
=VLOOKUP($A$2,IF(Data!$B$2:$B$11=$B$2,Data!$A$2:$D$11,""),4,FALSE)

NOTE: These are Array formulas, enter using Ctrl-Shift-Enter