+ Reply to Thread
Results 1 to 7 of 7

search formula for several results with 3 search criteria

  1. #1
    Registered User
    Join Date
    10-31-2019
    Location
    sedna
    MS-Off Ver
    2016 enterprise
    Posts
    20

    search formula for several results with 3 search criteria

    Hello everybody

    I have a formula that allows me to search results from a list of values with two search criteria. i need to be able to add one more criteria: 3 search criteria in total. I tried it but somehow can't get the syntax right.
    It is important that the formula retrieves several results if more than 1 are present, just like it does now.

    Attached is the document with the 2 search criteria formula in range B8:B14
    Search criteria are in cells C4, C5 in sheet "acces point sensorik"
    The data is in the sheet "datenbank sensorik"

    Thanx in advance for your help

    Greetings
    Attached Files Attached Files

  2. #2
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    26,955

    Re: search formula for several results with 3 search criteria

    Have you considered using an Advanced Filter rather than using compound Excel Formulae?

    It just seems more natural to me rather than jumping through hoops with formulae.
    And even better, what about a Pivot Table with some SLicers to filter and analyse it - no formulae are needed at all with a PT.
    Richard Buttrey

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Registered User
    Join Date
    10-31-2019
    Location
    sedna
    MS-Off Ver
    2016 enterprise
    Posts
    20

    Re: search formula for several results with 3 search criteria

    Hi Richard thanks for your answer.

    I dont know about pivot tables and advanced filters, can they substitute the formula based document design and deliver same results and fulfill the same function?

    The user will not be allowed to acces the sheet datenbank sensorik, it must happen on another sheet and sort out and deliver cell values from the sheet datenbank sensorik according to 3 search criteria and only thoos values. User friendliness for excell noobs is key in design

  4. #4
    Registered User
    Join Date
    10-31-2019
    Location
    sedna
    MS-Off Ver
    2016 enterprise
    Posts
    20

    Re: search formula for several results with 3 search criteria

    Also the Sheet acces point sensorik will have 30+ result columns (index excel function) and for each indexed column in datenbank sheet up to 40 , 50, different results must be displayed.

    The file i attached is trimmed but multiple results will be displayed on acces point sensorik for practically each of the 30+ columns in datenbank

  5. #5
    Registered User
    Join Date
    08-15-2020
    Location
    malaysia
    MS-Off Ver
    office2007
    Posts
    345

    Re: search formula for several results with 3 search criteria

    Where is your 3 criteria ?

    B8 cell array formula
    HTML Code: 
    =INDEX('datenbank sensorik'!L:L,SMALL(IF(($C$4='datenbank sensorik'!$C$4:$C$1000)*('datenbank sensorik'!$E$4:$E$1000=$C$5),ROW($4:$1000),4^8),ROW(A1)))&""

  6. #6
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    26,955

    Re: search formula for several results with 3 search criteria

    Here's a version with a Piviot Table added

    I've included three Slicers to filter it, but of course you can have a Slicer for every field in the data should you wish
    If you add new data you'll need to remember to change the source data for the PT - normally I'd automate that with a Macro.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    10-31-2019
    Location
    sedna
    MS-Off Ver
    2016 enterprise
    Posts
    20

    Re: search formula for several results with 3 search criteria

    lotta thanx to sir richard and sir wk9128 ,i'll have a closer look and get back to you with a feedback on the thread.
    i first have to figure out what a pivot table is and how to use it and try to adapt wk9128's formula to 3 criteria and test both on field.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Userform Search when search two text boxes return multiple results
    By nekiwa07 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-29-2020, 09:42 AM
  2. [SOLVED] search data and display results like a Google type search box
    By cfcoetzer in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 07-16-2020, 07:54 AM
  3. Replies: 4
    Last Post: 01-13-2019, 01:46 PM
  4. [SOLVED] Search multiple worksheets for search term and paste results in a summary sheet
    By Andy15 in forum Excel Programming / VBA / Macros
    Replies: 23
    Last Post: 04-03-2017, 01:49 PM
  5. Simple search box column and retrive results to search box sheet
    By Tanakaka in forum Hello..Introduce yourself
    Replies: 1
    Last Post: 01-08-2017, 08:47 AM
  6. Macro or Formula to search worksheets and return results base on criteria
    By Shellybelly in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-19-2016, 01:18 PM
  7. [SOLVED] Code to search Excel and return results even if part of search text is present
    By Taoyuan00 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-30-2016, 09:20 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts