Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Multiple Lookup with Criteria | Excel Worksheet Functions | |||
Lookup by multiple criteria? | Excel Worksheet Functions | |||
Lookup using multiple sheets and multiple criteria, sorry if 2 pos | Excel Worksheet Functions | |||
Lookup using multiple sheets and multiple criteria | Excel Discussion (Misc queries) | |||
Lookup with Multiple Criteria | Excel Discussion (Misc queries) |