Index match with multiple criteria

[Mal4131]

Hey All- I believe I am looking for a simple index match function based on two criteria, but I must be missing something basic (typically rely on vlookups). I want to return the ticket number for a specific NPI that the Patient Services team is working on. The intent is to combine this weekly report into a master file, with other weekly reports received. Unfortunately, I keep receiving an error message. Any help would be greatly appreciated. ('New Master'!A4=NPI in the master file).

=INDEX('New IS- Open'!B3:B7,MATCH('New Master'!A4,'New IS- Open'!A3:A7,0),MATCH({"Patient Services"},'New IS- Open'!E3:E7,0))

[TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]NPI[/TD]
[TD]Ticket #[/TD]
[TD]State[/TD]
[TD]Date Opened[/TD]
[TD]Assignment Group[/TD]
[/TR]
[TR]
[TD]12345[/TD]
[TD]SCTASK0300001[/TD]
[TD]In Progress[/TD]
[TD]10/12/2018[/TD]
[TD]Epic Scheduling Cadence[/TD]
[/TR]
[TR]
[TD]12345[/TD]
[TD]SCTASK0300002[/TD]
[TD]In Progress[/TD]
[TD]10/11/2018[/TD]
[TD]Epic Ambulatory[/TD]
[/TR]
[TR]
[TD]12345[/TD]
[TD]SCTASK0300003[/TD]
[TD]Closed[/TD]
[TD]10/11/2018[/TD]
[TD]Epic SER[/TD]
[/TR]
[TR]
[TD]12345[/TD]
[TD]SCTASK0300004[/TD]
[TD]In Progress[/TD]
[TD]10/11/2018[/TD]
[TD]Patient Services[/TD]
[/TR]
[TR]
[TD]67890[/TD]
[TD]SCTASK0300005[/TD]
[TD]Closed[/TD]
[TD]11/10/2018[/TD]
[TD]Epic Scheduling Cadence[/TD]
[/TR]
[TR]
[TD]67890[/TD]
[TD]SCTASK0300006[/TD]
[TD]Closed[/TD]
[TD]11/10/2018[/TD]
[TD]Epic Ambulatory[/TD]
[/TR]
[TR]
[TD]67890[/TD]
[TD]SCTASK0300007[/TD]
[TD]In Progress[/TD]
[TD]11/9/2018[/TD]
[TD]Patient Services[/TD]
[/TR]
</tbody>[/TABLE]

 

[gaz_chops]

Try

=INDEX('New IS- Open'!B3:B7,MATCH('New Master'!A4&"Patient Services",'New IS- Open'!A3:A7&'New IS- Open'!E3:E7,0))

Enter as an array, Ctrl Shift & Enter

 

[James006]

Hi,

With your data in cells A1 to E8 ...

say you have 12345 in cell G1 and Patient Services in cell H1 ...

then you can have the following Array Formula ... for example in cell I1

=INDEX($B$2:$B$8,MATCH(G1&H1,$A$2:$A$8&$E$2:$E$8,0))

Hope this will help

 

[Mal4131]

gaz_chops this works!! Thank you so much! I really appreciate it!

 

[gaz_chops]

You're welcome.