XLOOKUP dates & text wild card

[mmco]

Hello,

Attempting to use XLOOKUP to match a date and a wild card text match. Not sure what I am doing wrong, or if this is possible.

=XLOOKUP(1,($A$2:$A$21=B3)*($E$2:$E$21="*"&C3&"*"),!$F$2:$F$21,"Not Found",2)

Where B3 is the date and C3 is a text string that I am trying to use from a longer text string (for example just "Neon", from "Downtown Blue Neon")

I can get each separate lookup to work individually, so I think I am doing something wrong trying to insert a wildcard search as one of multiple criteria, and am unsure how to do this, or if it is possible.

Also, a secondary question on this, my source data dates come in as 11/17/2022 8:01:00 PM format, but I am only entering the date as 11/17/2022 for a search criteria. I've created a workaround by having a column in the source data convert the date/time to just the date, and that works, but was wondering if there was a way for the XLOOKUP to sort for this automatically? And what if there are same dates, but different times?

Hope that makes sense and thanks in advance!

 

[FormR]

Hi, you can't use the boolean logic like that with the wildcard search mode.

You could try this though, which address both of your questions.

=XLOOKUP(1,(INT($A$2:$A$21)=B3)*(ISNUMBER(SEARCH($C$3,$E$2:$E$21))),$F$2:$F$21,"Not Found",0)

 

[mmco]

Thanks a bunch. I'll dig into this so I can understand what is going on.

Thanks again!