Hi,
I have 2 sheets (First One with
Col A = Serv Date
Col B = Patient
Col C = Service (PT, OT or ST)
Col D = Authorization
Second Sheet
Col A = Patient
Col B = Begin Date + First Cell of the Column shows "PT"
Col C = End Date
Col D = Authorization
Col E = Begin Date + First Cell of the Column shows "OT"
Col F = End Date
Col G = Authorization
Col H = Begin Date + First Cell of the Column shows "ST"
Col I = End Date
Col J = Authorization
How could I combined the 3 separate formulas in one cell?:
To return PT Authorization Number:
=INDEX(Sheet2!$D$1:$D$200,MATCH(1,(A2>=Sheet2!$B$1:$B$200)*(A2<=Sheet2!$C$1:$C$200)*(B2=Sheet2!$A$1:$A$200)*(C2="PT"),0))
To return OT Authorization Number:
=INDEX(Sheet2!$G$1:$G$200,MATCH(1,(A2>=Sheet2!$E$1:$E$200)*(A2<=Sheet2!$F$1:$F$200)*(B2=Sheet2!$A$1:$A$200)*(C2="OT"),0))
To return ST Authorization Number:
=INDEX(Sheet2!$J$1:$J$200,MATCH(1,(A2>=Sheet2!$H$1:$H$200)*(A2<=Sheet2!$I$1:$I$200)*(B2=Sheet2!$A$1:$A$200)*(C2="ST"),0))
Note (Authorization value is alphanumeric so I don’t think I can use SUMPRODUCT or SUMIF)…
Thanks!
Bookmarks