VBA Multiple Criteria Lookup
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!D1:D169=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=X7 4)*(Markups!D1:D169=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! |
VBA Multiple Criteria Lookup
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 wrote: 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!D1:D169=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=X7 4)*(Markups!D1:D169=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 |
VBA Multiple Criteria Lookup
Is there vba code that I can use to do a mutliple criterai lookup instead of
the sumif... or index/match? Thanks. "Dave Peterson" wrote: 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 wrote: 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!D1:D169=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=X7 4)*(Markups!D1:D169=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 |
VBA Multiple Criteria Lookup
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. Elle wrote: Is there vba code that I can use to do a mutliple criterai lookup instead of the sumif... or index/match? Thanks. "Dave Peterson" wrote: 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 wrote: 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!D1:D169=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=X7 4)*(Markups!D1:D169=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 -- Dave Peterson |
VBA Multiple Criteria Lookup
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--- -- RyGuy "Dave Peterson" wrote: 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. Elle wrote: Is there vba code that I can use to do a mutliple criterai lookup instead of the sumif... or index/match? Thanks. "Dave Peterson" wrote: 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 wrote: 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!D1:D169=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=X7 4)*(Markups!D1:D169=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 -- Dave Peterson |
All times are GMT +1. The time now is 05:22 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com