Forum Discussion
Solved
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 ...
You should specify the desired results.
=XLOOKUP(1,(Dates>=$E$1)*ISNUMBER(SEARCH($G$1,C4:Q4)),Dates,"no match",0,-1)
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.
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.
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)))- 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.
You should specify the desired results.
=XLOOKUP(1,(Dates>=$E$1)*ISNUMBER(SEARCH($G$1,C4:Q4)),Dates,"no match",0,-1)
