Muliple criteria lookup using VBA

[housh47]

Gurus,

I have been struggling with this problem for several hours... and I am guessing someone has a very basic script that takes care of my problem and is much cleaner.

To this point I am able to generate a snippet of what I am trying to create with the following script...

Sub LookUp1()

Dim Topic As Long
Topic = Range("B" & Rows.Count).End(xlUp).Row
Range("C3:C" & Topic).FormulaArray = _
"=INDEX('Clarification Log'!R1C3:R100C3,MATCH(RC[-1]&""X"",'Clarification Log'!R1C2:R100C2&'Clarification Log'!R1C4:R100C4,0))"


End Sub


This array gets copied down the length of my column, but the formula is not dynamic and pastes the exact same formula in all cells.

Ideally, I'd have a mutiple criteria lookup that does not use an array...

Any thoughts?

 

[Colin Legg]

To get the array formula down the column as you want it: as far as I know there are 2 main ways to do this, each with their own pros and cons.

Option 1
Put the array formula in the first cell and then copy/paste or fill down the column. This is the usual option. There are examples of both copying and filling in posts #4, #8 and #13 here.

Option 2
Apply the array formula to the full range (as you are doing now) and then convert the references using the Range.FormulaR1C1 property. Example here in post #6 plus a few points in post #8 discussing the pros and cons of each option.

[Option 3 Loop through the range applying one formula at a time]


Hope that helps...