VBA Multiple Criteria Lookup

[Elle]

I am trying to do a mutiple critera search. If I were using a function it
would either look like this:

=SUM(IF((Markups!A1:A169=C75)*(Markups!B1:B169=X75)*(Markups!D1169=AR75),Markups!E1:E169,0))

or

=INDEX(Markups!$E$2:$E$320,MATCH(AR75,IF(Markups!$A$2:$A$320=Actuals!C75,Markups!$D$2:$D$320),0))

I've tried to create a custom function to put the formula in the active cell
with no luck.

ActiveCell.FormulaArray =
"=SUM(IF((Markups!A1:A169=C74)*(Markups!B1:B169=X74)*(Markups!D1169=AR74),Markups!E1:E169,0))"

(In case you are asking why I am using a custom function for a perfectly
good existing function, it's because the custom function evaulates variables
passed and performs a different multiple criteria search based on that).

Any help would be appreciated!

 

[Dave Peterson]

Do you mean you want a UDF that is located in a cell on a worksheet that plops
that formula in the cell?

If yes, then that won't happen. Functions can return values to cells--they
can't change the formula or anything else (almost).

But you could have a sub that populates a bunch of cells with that array
formula.

 

[Elle]

Is there vba code that I can use to do a mutliple criterai lookup instead of
the sumif... or index/match?

Thanks.

 

[Dave Peterson]

If you want to stay away from index(match()), then I guess you could loop
through the range looking for matches in each of the fields.

 

[ryguy7272]

Put everything into a Pivot Table and use =getpivotadata().
If you have never used this function, be forewarned, it takes a little
getting used to, but one you do get acquainted with it, you will find that it
is a very powerful function in Excel's library of many powerful functions.

Regards,
Ryan---