Index Match Multiple Criteria Code for VBA

[Babasinski]

I'm trying to write a code into VBA for Index Match and I can't seem to get it to work. I also can't figure out how to attach files or print screens so hopefully the information that I provide is sufficient.

I used Name Manager to identify all information in Column A as Reviews; Column B as What; and Column C as When. In cell F1 I have the column titled as "Review". In cell G1 I have the column titled as "Analyst sent final report to Reviewed Manager & Director".

Starting in cell F2 I have unique Review numbers. I have entered into cell G2, {=IFERROR(INDEX($C$2:$C$257,MATCH(F2,IF($B2:$B$257=$G$1,$A$2:$A$257),0)),"")}

How would I code this formula in VBA to work?

 

[Domenic]

Since we have an array formula, you'll need to first enter the formula into your desired cell using the FormulaArray property of the Range object, and then you can copy the formula to your other cells in the column.

    Dim LastRow As Long
    
    LastRow = Cells(Rows.Count, "F").End(xlUp).Row
    
    Range("G2").FormulaArray = "=IFERROR(INDEX($C$2:$C$257,MATCH(F2,IF($B$2:$B$257=$G$1,$A$2:$A$257),0)),"""")"
          
    Range("G2").Copy Range("G3:G" & LastRow)

If you only want to copy the formula, try the following instead...

    Range("G2").Copy
    Range("G3:G" & LastRow).PasteSpecial xlPasteFormulas
    Application.CutCopyMode = False

Note that FormulaArray has a 255 character limit, after which it will return an error.

Hope this helps!