Home

Search function + results based on results of search

%3CLINGO-SUB%20id%3D%22lingo-sub-802808%22%20slang%3D%22en-US%22%3ESearch%20function%20%2B%20results%20based%20on%20results%20of%20search%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-802808%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Excel%20Masters!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20situation%20here.%20I've%20created%20a%20search%20function%20where%20I%20can%20search%20a%20data%20set%20by%20typing%20in%20a%20client%20name%20(or%20partial%20name)%2C%20and%20my%20table%20will%20return%20all%20entries%20with%20at%20least%20a%20partial%20match.%20I.e.%20search%20%22Ohio%22%20and%20it%20returns%20Ohio%20State%2C%20Ohio%20State%20University%2C%20Ohio%20River%2C%20etc.%20That%20part%20of%20the%20search%20tool%20works%20great.%20My%20issue%20comes%20in%20the%20associated%20columns%20of%20the%20search%20results.%3C%2FP%3E%3CP%3EThe%20search%20results%20can%20produce%20duplicates%20of%20the%20client%20name%20(Ohio%20State%2C%20Ohio%20State%2C%20etc.)%20but%20each%20client%20entry%20(duplicate%20or%20not)%20will%20have%20a%20unique%20column%20associated%20(project%20names)%20that%20I%20want%20to%20populate%20along%20side.%20i.e.%20%5BOhio%20State%20-%20Football%20Impact%5D%20%5BOhio%20State%20-%20Baseball%20Record%5D%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20the%20project%20name%20doesn't%20contain%20the%20search%20term%2C%20it%20doesn't%20pull%20that%20into%20the%20results%2C%20causing%20the%20results%20in%20project%20name%20to%20get%20misaligned.%20I%20believe%20that%20is%20the%20root%20of%20the%20issue%2C%20as%20I%20have%20other%20columns%20that%20work%20off%20of%20the%20project%20name%20column.%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20formula%20I'm%20using%20to%20search%20the%20database%20and%20pull%20in%20the%20search%20results%20(works%20great)%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DIFERROR(INDEX(Table3%5BClient%5D%2CAGGREGATE(15%2C6%2C(ROW(Table3%5BClient%5D)-ROW(Table3%5B%5B%23Headers%5D%2C%5BClient%5D%5D))%2FISNUMBER(SEARCH(B%243%2CTable3%5BClient%5D))%2CROW(A1)))%2C%22%22)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EB3%20is%20the%20search%20box.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDo%20you%20guys%20have%20any%20idea%20how%20I%20can%20get%20the%20unique%20project%20name%20to%20populate%20along%20side%20it's%20(potential)%20duplicate%20client%3F%20Here's%20a%20screenshot%20of%20the%20problem%3A%20each%20client%20line%20entry%20has%20a%20unique%20project%20name.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F126770i0C5A5E9F9E17AB6C%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22kjdom8h824g31.png%22%20title%3D%22kjdom8h824g31.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-802808%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
DStripling
Occasional Visitor

Hi Excel Masters!

 

I have a situation here. I've created a search function where I can search a data set by typing in a client name (or partial name), and my table will return all entries with at least a partial match. I.e. search "Ohio" and it returns Ohio State, Ohio State University, Ohio River, etc. That part of the search tool works great. My issue comes in the associated columns of the search results.

The search results can produce duplicates of the client name (Ohio State, Ohio State, etc.) but each client entry (duplicate or not) will have a unique column associated (project names) that I want to populate along side. i.e. [Ohio State - Football Impact] [Ohio State - Baseball Record] 

If the project name doesn't contain the search term, it doesn't pull that into the results, causing the results in project name to get misaligned. I believe that is the root of the issue, as I have other columns that work off of the project name column. 

The formula I'm using to search the database and pull in the search results (works great):

 

=IFERROR(INDEX(Table3[Client],AGGREGATE(15,6,(ROW(Table3[Client])-ROW(Table3[[#Headers],[Client]]))/ISNUMBER(SEARCH(B$3,Table3[Client])),ROW(A1))),"")

 

B3 is the search box.

 

Do you guys have any idea how I can get the unique project name to populate along side it's (potential) duplicate client? Here's a screenshot of the problem: each client line entry has a unique project name.

kjdom8h824g31.png

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies