SOLVED

# Double Xlookup with Wildcards

Occasional Contributor

# Double Xlookup with Wildcards

Hi,

I am having trouble with adding a wildcard to my Xlookup formula. When it is using the exact text "Deliver" or a cell reference it works fine but as soon as I try to add a * wildcard before or after it errors out. So my question is, can you add wildcards to the following formula?

=XLOOKUP(1,("Deliver"=C\$4:Q\$4)*(Dates>=TODAY()),C\$3:Q\$3,,2,1)

I would like the ability to get a return on "4am Delivery" or "Delivery of Casegoods" etc

8 Replies

# Re: Double Xlookup with Wildcards

@JaseBass Perhaps like this?

=XLOOKUP(1,(G\$1=\$C4:\$Q4)*(Dates>=TODAY()),C\$3:Q\$3,"-",2,1)

Not sure though where "4am Delivery" or "Delivery of Casegoods" comes in here.

Added "-", just to show that no match was found.

# Re: Double Xlookup with Wildcards

Sorry for the confusion, I had changed "Deliver" to my cell reference G\$1. If I type in the exact match of Deliver I get the date returned but anything that deviates from the exact match I get nothing. Some times the user needs to be more specific than just Deliver.

# Re: Double Xlookup with Wildcards

= XLOOKUP("*deliver*", IF(Dates>=TODAY(), Events), Dates, "In past", 2, 1)

# Re: Double Xlookup with Wildcards

Because each row could have multiple instances of "Deliver" it is ignoring everything in the past and only needs to return the next instance. I will try this out.

# Re: Double Xlookup with Wildcards

As variant

=LET(
next, --(Dates>=TODAY()),
flag, IFERROR(SEARCH(G1,DeliveryData),0),
ind,  SEQUENCE(,COLUMNS(Dates)),
pos,  MMULT(ind*flag,TRANSPOSE(next)),
IF(pos=0,"-",INDEX(Dates,1,pos)))

# Re: Double Xlookup with Wildcards

Your solution helped with the wildcard after "Deliver" but nothing for before. It also returns a combined total if more than one instance is observed. I will see if I can tweak it.
best response confirmed by JaseBass (Occasional Contributor)
Solution

# Re: Double Xlookup with Wildcards

You should specify the desired results.

=XLOOKUP(1,(Dates>=\$E\$1)*ISNUMBER(SEARCH(\$G\$1,C4:Q4)),Dates,"no match",0,-1)

# Re: Double Xlookup with Wildcards

Hi Detlef, with a couple of tweaks I managed to get your solution to work exactly how we need it to. Many thanks.