SOLVED

Double Xlookup with Wildcards

%3CLINGO-SUB%20id%3D%22lingo-sub-2328186%22%20slang%3D%22en-US%22%3EDouble%20Xlookup%20with%20Wildcards%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2328186%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20having%20trouble%20with%20adding%20a%20wildcard%20to%20my%20Xlookup%20formula.%20When%20it%20is%20using%20the%20exact%20text%20%22Deliver%22%20or%20a%20cell%20reference%20it%20works%20fine%20but%20as%20soon%20as%20I%20try%20to%20add%20a%20*%20wildcard%20before%20or%20after%20it%20errors%20out.%20So%20my%20question%20is%2C%20can%20you%20add%20wildcards%20to%20the%20following%20formula%3F%26nbsp%3B%3C%2FP%3E%3CP%3E%3DXLOOKUP(1%2C(%22Deliver%22%3DC%244%3AQ%244)*(Dates%26gt%3B%3DTODAY())%2CC%243%3AQ%243%2C%2C2%2C1)%3C%2FP%3E%3CP%3EI%20would%20like%20the%20ability%20to%20get%20a%20return%20on%20%224am%20Delivery%22%20or%20%22Delivery%20of%20Casegoods%22%20etc%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20in%20advance.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2328186%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2328321%22%20slang%3D%22en-US%22%3ERe%3A%20Double%20Xlookup%20with%20Wildcards%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2328321%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1046086%22%20target%3D%22_blank%22%3E%40JaseBass%3C%2FA%3E%26nbsp%3BPerhaps%20like%20this%3F%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DXLOOKUP(1%2C(G%241%3D%24C4%3A%24Q4)*(Dates%26gt%3B%3DTODAY())%2CC%243%3AQ%243%2C%22-%22%2C2%2C1)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3BNot%20sure%20though%20where%26nbsp%3B%3CSPAN%3E%3CSTRONG%3E%224am%20Delivery%22%3C%2FSTRONG%3E%20or%20%3CSTRONG%3E%22Delivery%20of%20Casegoods%22%3C%2FSTRONG%3E%20comes%20in%20here.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EAdded%20%22-%22%2C%20just%20to%20show%20that%20no%20match%20was%20found.%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2328356%22%20slang%3D%22en-US%22%3ERe%3A%20Double%20Xlookup%20with%20Wildcards%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2328356%22%20slang%3D%22en-US%22%3ESorry%20for%20the%20confusion%2C%20I%20had%20changed%20%22Deliver%22%20to%20my%20cell%20reference%20G%241.%20If%20I%20type%20in%20the%20exact%20match%20of%20Deliver%20I%20get%20the%20date%20returned%20but%20anything%20that%20deviates%20from%20the%20exact%20match%20I%20get%20nothing.%20Some%20times%20the%20user%20needs%20to%20be%20more%20specific%20than%20just%20Deliver.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2328372%22%20slang%3D%22en-US%22%3ERe%3A%20Double%20Xlookup%20with%20Wildcards%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2328372%22%20slang%3D%22en-US%22%3E%3D%20XLOOKUP(%22*deliver*%22%2C%20IF(Dates%26gt%3B%3DTODAY()%2C%20Events)%2C%20Dates%2C%20%22In%20past%22%2C%202%2C%201)%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2328440%22%20slang%3D%22en-US%22%3ERe%3A%20Double%20Xlookup%20with%20Wildcards%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2328440%22%20slang%3D%22en-US%22%3EBecause%20each%20row%20could%20have%20multiple%20instances%20of%20%22Deliver%22%20it%20is%20ignoring%20everything%20in%20the%20past%20and%20only%20needs%20to%20return%20the%20next%20instance.%20I%20will%20try%20this%20out.%3C%2FLINGO-BODY%3E
Occasional Contributor

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

 

Thanks in advance.

8 Replies

@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.
= XLOOKUP("*deliver*", IF(Dates>=TODAY(), Events), Dates, "In past", 2, 1)
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.

@JaseBass 

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.
best response confirmed by JaseBass (Occasional Contributor)
Solution

@JaseBass 

You should specify the desired results.

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

 

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