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?

[TABLE="width: 0"]
<tbody>[TR]
[/TR]
[TR]
[TD]RESULT FINAL[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]
[/TD]
[TD]I type in a name and enter the date of service and the correct authorization number is returned into the cell[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]
[/TD]
[TD]NAME[/TD]
[TD]DATE OF APPT[/TD]
[TD]AUTH #[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]
[/TD]
[TD]NICOLE P[/TD]
[TD="align: right"]1/1/2018[/TD]
[/TR]
</tbody>[/TABLE]





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)))
[TABLE="width: 0"]
<tbody>[TR]
[TD]LOOKUPTABLE[/TD]
[TD]
[/TD]
[TD]auth number can be used for as long as appts are avail, Date is not a limiting factor.[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]NAME[/TD]
[TD]DATE NEW AUTH ISSUED[/TD]
[TD]STATUS[/TD]
[TD]AUTH[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]NICOLE P[/TD]
[TD="align: right"]1/1/18[/TD]
[TD]ACTIVE[/TD]
[TD]0166-000-004-2226-1[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]NICOLE P[/TD]
[TD="align: right"]10/1/18[/TD]
[TD]COMPLETED[/TD]
[TD]0166-000-004-1112-1[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]LOOKUP FORMULA[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]DIDNT WORK BC DATE NOT LIMITING[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]
[/TD]
[TD]DATE OF APPT[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]CORRECT AUTH BASED ON DATE OF SERVICE[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]NICOLE P[/TD]
[TD="align: right"]1/1/2018[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]0166-000-004-1112-1[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]NICOLE P[/TD]
[TD="align: right"]1/15/18[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]0166-000-004-1112-1[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]NICOLE P[/TD]
[TD="align: right"]12/01/2018[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]#N/A[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]NICOLE P[/TD]
[TD="align: right"]10/30/18[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]#N/A[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]NICOLE P[/TD]
[TD="align: right"]9/15/18[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]0166-000-004-1112-1[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD="align: center"]

---

[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
</tbody>[/TABLE]

The Answers I need

<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>[TABLE="width: 0"]
<tbody>[TR]
[TD]BILLING SHEET[/TD]
[TD]RESULT FINAL[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]I type in a name and enter the date of service and the correct authorization number is returned into the cell[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]NAME[/TD]
[TD]DATE OF APPT[/TD]
[TD]AUTH #[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]NICOLE P[/TD]
[TD="align: right"]1/1/2018[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
</tbody>[/TABLE]
<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>
[TABLE="width: 0"]
<tbody>[TR]
[TD]Color Code[/TD]
[TD]This is my tracking sheet that would be my actual lookup table[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] "]
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] "]
[/TD]
[TD]2 appts left[/TD]
[TD]Request Auth soon[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD="bgcolor: #FF00FF"]
[/TD]
[TD="bgcolor: #FF00FF"]
[/TD]
[TD]1 appt left[/TD]
[TD]Request Auth Right Away if PINK in FREQ and APPTS LEFT is less than 3[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD="bgcolor: #FF0000"]
[/TD]
[TD="bgcolor: #FF0000"]
[/TD]
[TD]0 appts left[/TD]
[TD]Should have auth if not, follow up[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD="bgcolor: #FF0000"]
[/TD]
[TD="bgcolor: #FF0000"]
[/TD]
[TD="align: right"]-1[/TD]
[TD]Enter new info into that comp appt follow keep note[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD="bgcolor: #3C78D8"]
[/TD]
[TD="bgcolor: #3C78D8"]
[/TD]
[TD]Auth Requested[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]
[/TD]
[TD]ALL GOOD[/TD]
[TD]
[/TD]
[TD]AUTHORIZATIONS[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]NAME[/TD]
[TD]
[/TD]
[TD]Freq[/TD]
[TD]APPTS LEFT[/TD]
[TD]STATUS[/TD]
[TD]AUTH #[/TD]
[TD]START DATE[/TD]
[TD]#AUTH[/TD]
[TD]#COMP[/TD]
[TD]DATES OF COMPLETED APPTS I FIGURED OUT this formula but it is matching auth # in billing sheet with date of appts I enter[/TD]
[/TR]
[TR]
[TD]NICOLE P[/TD]
[TD]
[/TD]
[TD]1xwk[/TD]
[TD="align: right"]5[/TD]
[TD]ACTIVE[/TD]
[TD="bgcolor: #00FF00"]0166-000-004-1233-1[/TD]
[TD="align: right"]1/3/19[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]1[/TD]
[TD="align: center"]=ArrayFormula(TEXTJOIN(",",TRUE,IF(F9='PACE COMP'!$A:$A,TEXT('PACE COMP'!$B:$B,"mm/dd/yyyy"),"")))[/TD]
[/TR]
[TR]
[TD]NICOLE P[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]0[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD="align: right"]12/19/18[/TD]
[TD="align: right"]1[/TD]
[TD="align: center"]#N/A[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]BOB SMITH[/TD]
[TD]
[/TD]
[TD]1xwk30min[/TD]
[TD="align: right"]7[/TD]
[TD]ACTIVE[/TD]
[TD="bgcolor: #00FF00"]0166-000-004-7435-1[/TD]
[TD="align: right"]12/19/18[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]1[/TD]
[TD="align: center"]
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]JANE SMITH[/TD]
[TD]
[/TD]
[TD]HOLD[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: right"]2[/TD]
[TD]ACTIVE[/TD]
[TD="bgcolor: #00FF00"]0166-000-003-9785-1[/TD]
[TD="align: right"]7/25/18[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]1[/TD]
[TD="align: center"]
[/TD]
[/TR]
[TR]
[TD="bgcolor: #4A86E8"]JACK FROST[/TD]
[TD="bgcolor: #4A86E8"]
[/TD]
[TD]1xwk[/TD]
[TD="align: right"]3[/TD]
[TD]ACTIVE[/TD]
[TD="bgcolor: #00FF00"]0166-000-004-1814-1[/TD]
[TD]10/08/18[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]1[/TD]
[TD="align: center"]
[/TD]
[/TR]
[TR]
[TD]JACK FROST[/TD]
[TD]
[/TD]
[TD="bgcolor: #FF00FF"]2xwk[/TD]
[TD="align: right"]8[/TD]
[TD]COMPLETED[/TD]
[TD="bgcolor: #F4CCCC"]0166-000-004-5343-1[/TD]
[TD="align: right"]11/13/18[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]8[/TD]
[TD="align: center"]dates of 8appts[/TD]
[/TR]
</tbody>[/TABLE]