Filter Xmatch with Wildcard

Question

I want to filter an array (a single line) using XMATCH to return columns based on wildcard criteria.

I am able to create the formula without XMATCH wildcards.

XMATCH with wildcard (exchanged order of parameters compared to later more complex examples), which works:

=XMATCH({"*FYE*FYE*","*Budget'*"},INDEX(B1:O1,1,),2,-1)

I want to adjust the formula to be compatible with Filter to return only columns that match criteria:

=LET(myArr,B1:O1,output,FILTER(myArr,(ISNUMBER(XMATCH(INDEX(myArr,1,),{"*FYE*FYE*","*Budget'*"},2,-1)))),output)

=LET(myArr,B1:O1,output,FILTER(myArr,(ISNUMBER(XMATCH(INDEX(myArr,1,),{"*FYE*FYE*","*Budget'*"},2,-1)))),output)

Your logic is off in the latest formulas. The formula uses wildcards in the input values. Also you may want to use XLOOKUP which returns a range. PS are you looking to get the last found "*FYE*FYE*" AND "*Budget'*", or not the last from both, but the last from either one of them (whichever comes last)? — P.b
– P.b, Commented Feb 5 at 17:47
I want to return last element that meet the conditions for both — barszczula
– barszczula, Commented Feb 6 at 17:25
Both functions without and with are identical (?) - are lookup ranges differing? See below includes simplified approach (with / w/ wildcard as req.) — JB-007
– JB-007, Commented Feb 21 at 12:21
@barszczula - did this work ? vote up/down for future users pls. ta — JB-007
– JB-007, Commented Mar 1 at 19:23

JB-007 · Accepted Answer · 2025-02-21 12:20:14Z

1

Simpler approach where....

Order doesn't matter:

=XMATCH("(?=.*FYE.*FYE)(?=.*Budget').*",C3:J3,3,-1)

i.e. Budget can appear before / between either of the 'FYE's etc.

Order matters

=XMATCH(".*FYE.*FYE.*Budget'.*",C3:J3,3,-1)

i.e. FYEs must preceed Budget'

Strictly wildcard:

=XMATCH(1,ISNUMBER(BYCOL(C3:J3,LAMBDA(a_,XMATCH("*FYE*FYE*",a_,2))))*ISNUMBER(BYCOL(C3:J3,LAMBDA(a_,XMATCH("*Budget'*",a_,2)))),,-1)

notes:

Xmatch({},lookup,..) returns match for any of items in {} within lookup. Produces array with same dim. as {} in this fashion.
With wildcard arg. cannot produce array with same dimension as lookup by swapping {} and lookup (in same way you might do with regular 'match') as wildcard must be present in lookup_value (not lookup)
To return last match for all items {} requires something more complex (than necessary).

answered Feb 21 at 12:20

JB-007

2,6441 gold badge9 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1