+ Reply to Thread
Results 1 to 3 of 3

Excel index-match ignore criteria based on conditions

  1. #1
    Forum Contributor
    Join Date
    08-22-2017
    Location
    USA
    MS-Off Ver
    2016 WINDOWS and MAC
    Posts
    294

    Excel index-match ignore criteria based on conditions

    Hi,

    I recently was provided a nice solution for an index-match problem I had. I now have run into another problem and I believe this one is quite advanced.

    I would like an index-match formula that contains multiple criteria, but one of the criteria is only applicable if the user specifies so. For instance, I want to return data between two dates, BUT if the user chooses to further filter the data, then the second criteria is used. I'm not quite sure how to implement this.

    In my attached example:

    I have a column of raw data. Data includes, date, temperature, oven type.

    User inputs two dates, and the index formula returns data only in those dates. However, if the user chooses "y" for "filter by oven type?" then the data is further filtered by the oven type.

    The attached shows both results of what I am trying to achieve.


    Thank you for your time.
    Attached Files Attached Files

  2. #2
    Forum Expert José Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    3,329

    Re: Excel index-match ignore criteria based on conditions

    Try this in O8 and copy down
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    For the other columns try
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Formula: copy to clipboard
    Please Login or Register  to view this content.


    See the file
    Attached Files Attached Files

  3. #3
    Forum Moderator - RIP 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
    29,464

    Re: Excel index-match ignore criteria based on conditions

    Why not use a Pivot Table and a couple of Slicers to select your data. See attached.
    Attached Files Attached Files
    Richard Buttrey

    RIP - d. 06/10/2022

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

+ 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. [SOLVED] Index/Match when the Match criteria is based on a second table
    By Plummet in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 06-10-2017, 01:02 PM
  2. VBA Code for Index/Match/Match to input into excel based on 2 criteria inside form
    By Carl Fisher in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-22-2017, 12:11 PM
  3. [SOLVED] INDEX MATCH based on two Field Conditions
    By naveeddil in forum Excel Charting & Pivots
    Replies: 5
    Last Post: 04-08-2016, 01:09 AM
  4. Ignore Blanks for INDEX/MATCH with multiple criteria search
    By Stephen23 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-15-2015, 09:14 AM
  5. Replies: 0
    Last Post: 11-05-2014, 10:54 AM
  6. [SOLVED] Index Match With 2 Sets of Criteria, Each with Multiple Conditions
    By MrMacro2013 in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 06-23-2014, 06:29 PM
  7. Index Match based on 2 Criteria only returning 1 match
    By stsanders22 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-04-2012, 11:26 PM

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1