Struggling on Criteria to INDEX Match correct Authorization number for tracking and billing

[HMM2019]

<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>Hello I would love any assistance on this, I've spent hours trying to solve this. Thank you in advance for taking any time to help.

The result I would like

I type in a name and date of appointment and the correct auth # to use for that appt is returned in the next cell.

Challenges, I don't know what parameters to tell excel so that the authorization number is a correct match to the client name and appointment date. There are 80 so clients and authorizations are completed at different times. I bill about 100 appts at a time so it would be so helpful for the correct auth# to be found using a formula based on a table of info. I would like my billing sheet to maintain the correct authorization matches ongoing forever. This way the tracking sheet would keep the history and count of visits, dates, of completed and active authorizations.

Am I dreaming?

RESULT FINAL
		I type in a name and enter the date of service and the correct authorization number is returned into the cell
		NAME	DATE OF APPT	AUTH #
		NICOLE P	1/1/2018

<tbody>
</tbody>

I am given an Authorization # for a client that are good for, an example 8 appts and once 8 appts are completed I have to have a new authorization number that is used for given # of appts usually 4,6, or 8 appts. How can I have a correct billing sheet and tracking record like the table's below?


I tried EXCEL MAGIC TRICK 1487 MAXIFS to see if I could have the date be the parameter here is my attempt below, date didn't work since it is not limiting, the number of appts(maybe date of appts) and client name are the only limiting factors to the authorization number.

ATTEMPT AT EXCEL MAGIC TRICK MAXIFS, I was able to figure it out and get the formula to work but it didn't give me the answer I actually need
=ArrayFormula(INDEX($E$3:$E$15,MATCH(B19&MAXIFS($C$3:$C$15,$B$3:$B$15,B19,$C$3:$C$15,">="&C19),INDEX($B$3:$B$15&$C$3:$C$15,0),0)))

LOOKUPTABLE		auth number can be used for as long as appts are avail, Date is not a limiting factor.
	NAME	DATE NEW AUTH ISSUED	STATUS	AUTH
	NICOLE P	1/1/18	ACTIVE	0166-000-004-2226-1
	NICOLE P	10/1/18	COMPLETED	0166-000-004-1112-1
LOOKUP FORMULA
							DIDNT WORK BC DATE NOT LIMITING
		DATE OF APPT					CORRECT AUTH BASED ON DATE OF SERVICE
	NICOLE P	1/1/2018					0166-000-004-1112-1
	NICOLE P	1/15/18					0166-000-004-1112-1
	NICOLE P	12/01/2018					#N/A
	NICOLE P	10/30/18					#N/A
	NICOLE P	9/15/18					0166-000-004-1112-1

<tbody>
</tbody>

The Answers I need

<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>

BILLING SHEET	RESULT FINAL
	I type in a name and enter the date of service and the correct authorization number is returned into the cell
	NAME	DATE OF APPT	AUTH #
	NICOLE P	1/1/2018

<tbody>
</tbody>

<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>

Color Code	This is my tracking sheet that would be my actual lookup table
		2 appts left	Request Auth soon
		1 appt left	Request Auth Right Away if PINK in FREQ and APPTS LEFT is less than 3
		0 appts left	Should have auth if not, follow up
		-1	Enter new info into that comp appt follow keep note
		Auth Requested
		ALL GOOD		AUTHORIZATIONS
NAME		Freq	APPTS LEFT	STATUS	AUTH #	START DATE	#AUTH	#COMP	DATES OF COMPLETED APPTS I FIGURED OUT this formula but it is matching auth # in billing sheet with date of appts I enter
NICOLE P		1xwk	5	ACTIVE	0166-000-004-1233-1	1/3/19	8	1	=ArrayFormula(TEXTJOIN(",",TRUE,IF(F9='PACE COMP'!$A:$A,TEXT('PACE COMP'!$B:$B,"mm/dd/yyyy"),"")))
NICOLE P			0			12/19/18	1	#N/A
BOB SMITH		1xwk30min	7	ACTIVE	0166-000-004-7435-1	12/19/18	8	1
JANE SMITH		HOLD	2	ACTIVE	0166-000-003-9785-1	7/25/18	6	1
JACK FROST		1xwk	3	ACTIVE	0166-000-004-1814-1	10/08/18	6	1
JACK FROST		2xwk	8	COMPLETED	0166-000-004-5343-1	11/13/18	8	8	dates of 8appts

<tbody>
</tbody>