Dynamic Search - Number of rows

[Exact]

Hi, I have a table with 2 columns (L & M), the number of rows in the table can change so I want to search using the current number of rows.

For a fixed number of rows (7) this formula works
Range("I1").Formula2 = "=IFERROR(LOOKUP(2,1/SEARCH($L$2:$L$7,$D1),$M$2:$M$7),$E1)"

But I can't figure out how to make $L$7 and $M$7 dynamic

I use this to get the current number of rows "x":
x = Cells(Rows.Count, "L").End(xlUp).Row

I've tried a number of ways but can't get the right syntax. e.g.
Range("I1").Formula2 = "=IFERROR(LOOKUP(2,1/SEARCH(range("$L$2:$L$" &x,$D1),range("$M$2:$M$" &x),$E1)"
Range("I1").Formula2 = "=IFERROR(LOOKUP(2,1/SEARCH(range("$L$2"),"$L$" & x,$D1),range("$M$2","$M$" &x),$E1)"
Range("I1").Formula2 = "=IFERROR(LOOKUP(2,1/SEARCH((range("L2","L" & x)).address,$D1),(range("M2","M" &x)).address,$E1)"
Range("I1").Formula2 = "=IFERROR(LOOKUP(2,1/SEARCH("("&range("L2","L" & x&")").address,$D1),"("&range("M2","M" &x&)")".address,$E1)"

I'd appreciate any help.

 

[kevin9999]

Please try the following:

Range("I1").Formula2 = "=IFERROR(LOOKUP(2,1/SEARCH($L$2:$L$" & x & ",$D1),$M$2:$M$" & x & "),$E1)"

 

[Peter_SSs]

I find it much easier and make less mistakes if i don't try to string together the formula parts and variable(s) but make the formula much more like the actual worksheet formula with symbols in place of the missing/unknown values and then replace those symbols with the relevant values.
So, for me it would be more like this where the red part is just like the final worksheet formula but with # in place of the unknown row number, then replace # with the value held in the variable x

Range("I1").Formula2 = Replace("=IFERROR(LOOKUP(2,1/SEARCH($L$2:$L$#,$D1),$M$2:$M$#),$E1)", "#", x)

 

[Exact]

Thank you!

 

[Peter_SSs]

Cheers. Glad you got a solution.