Forum Discussion

DCBanks's avatar
DCBanks
Copper Contributor
Nov 20, 2019

AGGREGATE function, criteria ignores wildcards

I am using AGGREGATE function and trying to get one of the criteria do do a wildcard lookup, this is the code:

=IFERROR( INDEX(C$5:C$38, AGGREGATE( 15, 6, 1/(**bleep**=$O$10)/(Budget=$P$10)/(ProjectCode="*"&$Q$10&"*")* (ROW($A$5:$A$38)-ROW($A$4)), (ROW()-ROW($O$10)) ) ), "")

 

In this case I want to enter PRO for Budget but get all ProjectCode (P1, P2, P4 etc) listed with a budget code of PRO by entering blank. If I enter P4 for Q10 it still lists only P4 but the ProjectCode column is blank. If I put space in Q10 I get nothing at all. Love AGGREGATE but can it do wildcards similar to SUMIF, COUNTIF etc?

Many thanks.

excel
Formulas and Functions

2 Replies

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Nov 20, 2019

    DCBanks 

    You may change this your criteria

    (ProjectCode="*"&$Q$10&"*")

    on something like

    ((ProjectCode=$Q$10)+($Q$10=""))

    or use instead of blank whatever text you wish. With it in Q10 it will show all project codes.

  • PReagan's avatar
    PReagan
    Bronze Contributor
    Nov 20, 2019

    Hello DCBanks,

     

    Unfortunately, AGGREGATE() does not allow wildcards. You may see which functions do allow wildcards here:

    http://www.allerdrops.com/excel_help/functions/guide-to-excel-wildcards.htm

Resources