Using Speed Formulas: Lookups

[Jonathan Stearns]

I have this formula:

=XLOOKUP(1,(EURUSD!$A$3:INDEX(EURUSD!$A:$A,$U$1)<=$Y3)*(EURUSD!$A$3:INDEX(EURUSD!$A:$A,$U$1)>=$C3)*(IF($H3="Buy",EURUSD!$L$3:INDEX(EURUSD!$L:$L,$U$1)<BA3,EURUSD!$K$3:INDEX(EURUSD!$K:$K,$U$1)>BA3)),EURUSD!$A$3:INDEX(EURUSD!$A:$A,$U$1),"Not Hit",0,)

Basically, for 

* lookup values, it has two expressions against two constants.

* for return values, it has one expression against a constant before returning it

What Lookup formula do I use to replace the above formula?  I thought Rgx.AVLOOKUP2, but from reading the help I could not figure out how (e.g. it says to use an expression or array of constants....neigher apply, I think to my case).

I appreciate the guidance!

[Jonathan Stearns]

If it helps, here is the formula in Index/Match:

=IFERROR(INDEX(EURUSD!$A$3:INDEX(EURUSD!$A:$A,$U$1),MATCH(1,(EURUSD!$A$3:INDEX(EURUSD!$A:$A,$U$1)<=$Y3)*(EURUSD!$A$3:INDEX(EURUSD!$A:$A,$U$1)>=$C3)*(IF($H3="Buy",EURUSD!$L$3:INDEX(EURUSD!$L:$L,$U$1)<BA3,EURUSD!$L$3:INDEX(EURUSD!$K:$K,$U$1)>BA3)),0)),"Not Hit")

[Charles Williams]

I don't think using Regex would be a good idea.

There are lots of alternatives for this formula. Try experiments to see which one wins.

Don't see why you cannot use AVLOOKUP - you have lots of expressions in your formula.

One approach would be to use SLICES to return a contiguous array of Cols A K & L {1,11,12) for the rows from 3 to $U$1 (HSTACK(,3,0,$U$1)) and then filter that for your other conditions using a LOOKUP or FILTER or something.

If your data is sorted on column A you could use FILTER.SORTED on the A:K subset range (Sadly can't use FILTER.SORTED against an array).

[Jonathan Stearns]

I hate to say this, but Is there someone I can pay to convert the formula?  I have been working on these for months, and now just need it done.  Don't have the time for trial and error to figure it out the new UDFs.