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]

	A	B	C
1	patient ID	DOS	Doc name
2	123456	2/2/2017	Doc3
3	987456	3/7/2017	Doc8
4	123489	3/18/2017	Doc24
5	963741	7/7/2017	Doc10
6	667795	9/1/2017	Doc23

<tbody>
</tbody>

main​

Array Formulas Cell Formula C2 {=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)),"")} C3 {=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)),"")} C4 {=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)),"")} C5 {=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)),"")} C6 {=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)),"")} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

	A	B	C
1	patient ID	DOS	Doc name
2	123456	1/1/2016	Doc1
3	852741	1/9/2017	Doc2
4	123456	2/2/2017	Doc3
5	789465	2/5/2017	Doc4
6	494	2/6/2017	Doc5
7	496	2/6/2017	Doc6
8	984	2/20/2017	Doc7
9	987456	3/7/2017	Doc8
10	949	4/7/2017	Doc9
11	963741	7/7/2017	Doc10
12	4984	8/5/2017	Doc11

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><tbody>
</tbody>

doc1​

	A	B	C
1	patient ID	DOS	Doc name
2	45685	6/4/2017	Doc20
3	45644	8/20/2017	Doc21
4	71968	8/25/2017	Doc22
5	667795	9/1/2017	Doc23
6	123489	3/18/2017	Doc24
7	4242	4/1/2017	Doc25
8	4242	4/19/2017	Doc26
9	242	5/27/2017	Doc27
10	51252	6/17/2017	Doc28
11	412	6/22/2017	Doc29
12	41	11/11/2017	Doc30

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><tbody>
</tbody>

doc2​