Enhancing Search Functionality in Excel Database

[IMaadh]

My name is IMaadh,

I’ve created an Excel sheet that functions as a searchable database. Currently, I can search for any single entry within the table, but I want to enhance it to search for details in two cells simultaneously.

Example:

Table headings: Staff Number, Username, Location

Staff Number	Username	Location
1234	imaadh	Finance
4567	Alas	Head Office

Using the existing setup, searching for "imaadh" displays: [1234, imaadh, Finance]. I now want to be able to search for "Finance, Head Office" and get results that show both entries.

Below are the formulas I’ve implemented:

Table Name: INV_Report
Sheet Name: Search Engines

Woqod_Advance_Search_2:

=FILTER(INV_Report, NOT(ISERROR(SEARCH(Woqod_Search_Bar_2, Woqod_Details_Collection))), "")

Woqod_Details_Collection:

=CONCATENATE(
  INV_Report[Type], "*", INV_Report[STAFF NUMBER], "*", 
  INV_Report[USERNAME], "*", INV_Report[DEPARTMENT], "*", 
  INV_Report[LOCATION], "*", INV_Report[PC NUMBER], "*", 
  INV_Report[BRAND], "*", INV_Report[MODEL], "*", 
  INV_Report[DEVICE SERIAL No.], "*", INV_Report[OPERATING SYSTEM], "*", 
  INV_Report[DISPLAY SIZE], "*", INV_Report[DISPLAY SERIAL No.], "*", 
  INV_Report[TICKET No.], "*", INV_Report[DOCKING STATION], "*", 
  INV_Report[ASSET TYPE / OWNER], "*", INV_Report[DELIVERY], "*", 
  INV_Report[REMARKS]
)

Woqod_Search_Bar_2:

='Search Engines'!$H$4

 

[severynm]

I think you are close. If you can assume that the items in the search area will always be separated with a comma and a space, you can change the search formula to

=FILTER(INV_Report, NOT(ISERROR(SEARCH(TEXTSPLIT(Woqod_Search_Bar_2, , ", "), Woqod_Details_Collection))), "")

Also, not a big deal, but you may find it easier to use the Concat function instead of Concatenate (which Concat was introduced to replace).

 

[IMaadh]

I think you are close. If you can assume that the items in the search area will always be separated with a comma and a space, you can change the search formula to

=FILTER(INV_Report, NOT(ISERROR(SEARCH(TEXTSPLIT(Woqod_Search_Bar_2, , ", "), Woqod_Details_Collection))), "")

Also, not a big deal, but you may find it easier to use the Concat function instead of Concatenate (which Concat was introduced to replace).

Thank you so much for your reply, I appreciate it. I have tried earlier, but it didn't work, perhaps I did something wrong. To make it easier, I have shared a demo file with the codes. Could you kindly look into that?

Download Demo File

 

[severynm]

Thank you so much for your reply, I appreciate it. I have tried earlier, but it didn't work, perhaps I did something wrong. To make it easier, I have shared a demo file with the codes. Could you kindly look into that?

Download Demo File

Sorry, I really try to avoid downloading files behind a link shortener. If you're more specific with what error or result you are getting I can try to help further.

 

[IMaadh]

Sorry, I really try to avoid downloading files behind a link shortener. If you're more specific with what error or result you are getting I can try to help further.

Since you have avoided downloading the document, I strongly prefer that you create a document using the formula below. I believe this is the best way to quickly find a solution. Please don't take this the wrong way; I know we both have work to do, and I appreciate your kindness in helping me.


Table Name: INV_Report

Formular:-

One

=CONCATENATE(INV_Report[Username], "*", INV_Report[Staff Number], "*", INV_Report[Department], "*", INV_Report[Location])

Two

=Search!$H$4

Three

=FILTER(INV_Report,NOT(ISERROR(SEARCH(Woqod_Search_Bar_2,Woqod_Details_Collection))),"")

Image 1 - Demo Table + Search Bar

Image 2 - Single Search “alas” Results OK

Image 3 - I need to Search with space or without multiple values.