trying to return value based on more than 1 criteria

[steven179]

Hi everyone!
On my Excel workbook, I am attempting to determine doctor's name based on patient ID and the Date of Service(DOS).
I have three sheets at the moment: main, Doc1 and Doc2
All three sheets contain patient ID and DOS. They may appear multiple times , especially on Doc1 and Doc 2 tabs. A patient may have been seen by different doctors, depending on DOS.
My objective is determine the doctor by matching patient IDs and DOS , based on what is on the main tab. Not sure if this is accomplished by IF statement somehow? Any help is greatly appreciated! Thanks very much.

 

[Scott T]

[TABLE="class: grid, width: 300"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD]patient ID[/TD]
[TD]DOS[/TD]
[TD]Doc name[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: right"]123456[/TD]
[TD="align: right"]2/2/2017[/TD]
[TD]Doc3[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: right"]987456[/TD]
[TD="align: right"]3/7/2017[/TD]
[TD]Doc8[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: right"]123489[/TD]
[TD="align: right"]3/18/2017[/TD]
[TD]Doc24[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: right"]963741[/TD]
[TD="align: right"]7/7/2017[/TD]
[TD]Doc10[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: right"]667795[/TD]
[TD="align: right"]9/1/2017[/TD]
[TD]Doc23[/TD]
[/TR]
</tbody>[/TABLE]

main​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="class: grid, width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TD]Cell[/TD]
[TD="align: left"]Formula[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C2[/TH]
[TD="align: left"]{=IFERROR(INDEX('doc1'!$C$2:$C$12,MATCH(A2&B2,'doc1'!$A$2:$A$12&'doc1'!$B$2:$B$13,0)),"")&IFERROR(INDEX('doc2'!$C$2:$C$12,MATCH(A2&B2,'doc2'!$A$2:$A$12&'doc2'!$B$2:$B$12,0)),"")}[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C3[/TH]
[TD="align: left"]{=IFERROR(INDEX('doc1'!$C$2:$C$12,MATCH(A3&B3,'doc1'!$A$2:$A$12&'doc1'!$B$2:$B$13,0)),"")&IFERROR(INDEX('doc2'!$C$2:$C$12,MATCH(A3&B3,'doc2'!$A$2:$A$12&'doc2'!$B$2:$B$12,0)),"")}[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C4[/TH]
[TD="align: left"]{=IFERROR(INDEX('doc1'!$C$2:$C$12,MATCH(A4&B4,'doc1'!$A$2:$A$12&'doc1'!$B$2:$B$13,0)),"")&IFERROR(INDEX('doc2'!$C$2:$C$12,MATCH(A4&B4,'doc2'!$A$2:$A$12&'doc2'!$B$2:$B$12,0)),"")}[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C5[/TH]
[TD="align: left"]{=IFERROR(INDEX('doc1'!$C$2:$C$12,MATCH(A5&B5,'doc1'!$A$2:$A$12&'doc1'!$B$2:$B$13,0)),"")&IFERROR(INDEX('doc2'!$C$2:$C$12,MATCH(A5&B5,'doc2'!$A$2:$A$12&'doc2'!$B$2:$B$12,0)),"")}[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C6[/TH]
[TD="align: left"]{=IFERROR(INDEX('doc1'!$C$2:$C$12,MATCH(A6&B6,'doc1'!$A$2:$A$12&'doc1'!$B$2:$B$13,0)),"")&IFERROR(INDEX('doc2'!$C$2:$C$12,MATCH(A6&B6,'doc2'!$A$2:$A$12&'doc2'!$B$2:$B$12,0)),"")}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="class: grid, width: 300"]
<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD]patient ID[/TD]
[TD]DOS[/TD]
[TD]Doc name[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: right"]123456[/TD]
[TD="align: right"]1/1/2016[/TD]
[TD]Doc1[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: right"]852741[/TD]
[TD="align: right"]1/9/2017[/TD]
[TD]Doc2[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: right"]123456[/TD]
[TD="align: right"]2/2/2017[/TD]
[TD]Doc3[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: right"]789465[/TD]
[TD="align: right"]2/5/2017[/TD]
[TD]Doc4[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: right"]494[/TD]
[TD="align: right"]2/6/2017[/TD]
[TD]Doc5[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: right"]496[/TD]
[TD="align: right"]2/6/2017[/TD]
[TD]Doc6[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: right"]984[/TD]
[TD="align: right"]2/20/2017[/TD]
[TD]Doc7[/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: right"]987456[/TD]
[TD="align: right"]3/7/2017[/TD]
[TD]Doc8[/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: right"]949[/TD]
[TD="align: right"]4/7/2017[/TD]
[TD]Doc9[/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD="align: right"]963741[/TD]
[TD="align: right"]7/7/2017[/TD]
[TD]Doc10[/TD]
[/TR]
[TR]
[TD="align: center"]12[/TD]
[TD="align: right"]4984[/TD]
[TD="align: right"]8/5/2017[/TD]
[TD]Doc11[/TD]
[/TR]
</tbody>[/TABLE]

doc1​

[TABLE="class: grid, width: 300"]
<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD]patient ID[/TD]
[TD]DOS[/TD]
[TD]Doc name[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: right"]45685[/TD]
[TD="align: right"]6/4/2017[/TD]
[TD]Doc20[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: right"]45644[/TD]
[TD="align: right"]8/20/2017[/TD]
[TD]Doc21[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: right"]71968[/TD]
[TD="align: right"]8/25/2017[/TD]
[TD]Doc22[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: right"]667795[/TD]
[TD="align: right"]9/1/2017[/TD]
[TD]Doc23[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: right"]123489[/TD]
[TD="align: right"]3/18/2017[/TD]
[TD]Doc24[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: right"]4242[/TD]
[TD="align: right"]4/1/2017[/TD]
[TD]Doc25[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: right"]4242[/TD]
[TD="align: right"]4/19/2017[/TD]
[TD]Doc26[/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: right"]242[/TD]
[TD="align: right"]5/27/2017[/TD]
[TD]Doc27[/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: right"]51252[/TD]
[TD="align: right"]6/17/2017[/TD]
[TD]Doc28[/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD="align: right"]412[/TD]
[TD="align: right"]6/22/2017[/TD]
[TD]Doc29[/TD]
[/TR]
[TR]
[TD="align: center"]12[/TD]
[TD="align: right"]41[/TD]
[TD="align: right"]11/11/2017[/TD]
[TD]Doc30[/TD]
[/TR]
</tbody>[/TABLE]

doc2​