Help to Create a User defined Function with IF and VLOOK-UP and/or Match and COUNTIFS

Hello,

I am building a model, and I need some help creating a complex formula with IF, VLOOKUP and/Or Match and Countifs

My formula in excel is the following.

=IF($A53="Open",MAX((VLOOKUP($D53&$E53,Table Array,MATCH(AB$38,Lookup_Array,0),FALSE)-(COUNTIFS($E:$E,$E53)-1)),0)*

I appreciate any help or input building this formula in User Defined Functions

Thanks,
EM

i am not too sure what to look at but it would help if you try to recreate what you are trying to achieve on a smaller scale. upload a sample file with an input and output sheet.