index match array formula in VBA

Question

Can you point me in the right direction for this formula. I am trying to get a map name for my answer. I compare the current Eastings and Northings within the array to get a my answer

I have all the array data on a sheet called Map_Data This is:

Map name in col A
Min Easting in col B
Max Easting in Col C
Min Northing in Col D
Max Northing in Col E 

I can get the formula to work normally in excel, but am having error in VBA. I think it has something to do with Range.formula as a get a global error

Range(ActiveCell).FormulaArray = "=(INDEX(Map_Data!A2:A88,Match(1,(Map_Data!B2:B88<=""" & Eastings & """)*(Map_Data!C2:C88> """ & Eastings & """)*(Map_Data!D2:D88<""" & Northings & """)*(Map_Data!E2:E88>= """ & Northings & """),0),1)"

I have played with the input range That is

Range("A1").FormualArray

I want to use the ActiveCell instead of A1 I have used Selection.FormulaArray, but with no luck

Thanks Roogis

Answer 1

My opinion here is that if you're using Standalone purchase of Excel 2019 or later, you can use "xlookup" function using "Worksheetfunction" method. Also if you try to include this formula to a certain range then, you can use for or for each loop to execute the same. Thanks!