3 INDEX Formulas in One Cell

[Amarilis Abreu]

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!

[NBVC]

Does this work?

Please Login or Register  to view this content.

[Amarilis Abreu]

Hi,
Thanks for responding so quickly!
Sorry, but I couldn't make it work Could you take a look at the test sheet attached)? Thanks!!

[NBVC]

Ok, thanks for the workbook...

Try this:

Please Login or Register  to view this content.

Note: This formula can be entered with ENTER only.... you don't need CTRL, SHIFT, ENTER to evaluate it.

[Amarilis Abreu]

OMG!! It works. You just made my Friday! Thank you thank you thank you Have a good one!!!

[NBVC]

You are very welcome

[NBVC]

If you are satisfied with the solution provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

[Amarilis Abreu]

How to mark Solved: Thanks for the tip! Now I know for the next time