Custom Search bar - Partial & Exact match

[Eawyne]

Hiyall,

I've been trying to create a custom search bar and this video explaining it rather well. However, when it comes to the partial research, it appears that my version of Excel doesn't understand the [FIRST] variable (it's a Pro version 2021 - I thought it was enough, but maybe it needs 365 ?).

I wondered if there was a workaround ?

=FILTER(Table1;Table1[First]=B13;"No match")

The basic formula requires an exact match, and it's quite limiting, unfortunately :

=FILTER(Table1;Table1[Sujet]=B13;"No match")

 

[jkpieterse]

That First variable is a column in your table called Table1. Apparently your table does not have a column by that precise name

 

[Eawyne]

Wow... I was so focused I didn't even notice that ! Granted, I tried downloading their file, but it's not even complete, and I just didn't think to reproduce their Table entirely... Thanks for helping me take a step back, I'm still learning Excel's formula structure.

 

[Eawyne]

So I made it work - sorta. I think the formula is good, but in the video, he doesn't cover what's bothering me ; the "is empty" condition doesn't seem to trigger, and when my search box is empty, it shows up the whole list !

=FILTER(Table1;ISNUMBER(SEARCH(B13;Table1[Sujet]));"Rien trouvé")

I tried adding an ISBLANK but is doesn't do much either :

=IF(ISBLANK(B13);"Rien
trouvé";FILTER(Table1;ISNUMBER(SEARCH(B13;Table1[Sujet]))))

EDIT : in the screenshots, the first "Rien trouvé" line is with the basic non-partial formula, where the "is empty" does trigger.

 

[Fluff]

How about

=IF(B13="";"Rientrouvé";FILTER(Table1;ISNUMBER(SEARCH(B13;Table1[Sujet]));"Rientrouvé"))

 

[Eawyne]

Well, that's kinda frustrating how easy it was. Why is it working with just IF and not IFBLANK (or empty) ?

Thanks, it works flowlessly.

 

[Fluff]

If B13 contains a formula, then it's not blank. I never use isblank for that reason.

 

[Eawyne]

That's the thing though, B13 does not contain anything ! How peculiar... I'll dig into this later, again, thanks so much !

 

[Fluff]

Glad we could help & thanks for the feedback.