+ Reply to Thread
Results 1 to 9 of 9

INDEX MATCH formula with multiple ctriteria - not getting it right...

  1. #1
    Registered User
    Join Date
    07-03-2019
    Location
    Stockholm, Sweden
    MS-Off Ver
    2013
    Posts
    59

    Question INDEX MATCH formula with multiple ctriteria - not getting it right...

    Hi,

    New here and new to slightly more complex Formulas in Excel, I keep struggling with following task.

    I need to retrieve all cells / values from column A for which there is data >0 in at least one of the columns B, C and D.
    Rows can look as follows:
    (A) (B) (C) (D)
    ExA 0 0 6
    ExB 5 5 0
    ExC 0 0 0
    ExD 1 8 2
    ExE 0 0 0
    and so on
    For above example I would like to retrieve ExA, ExB, ExD. Not ExC and ExE since they have 0 in all columns.

    Have tried some combinations of INDEX and MATCH using OR and IF functions but I can't seem to get it right...

    What I have tried (examples):
    =INDEX($A$4:$A$50; MATCH(0; OR($B$4:$B$50=0;$C$4:$C$50=0;$D$4:$D$50=0)))
    =INDEX($A$4:$A$50; MATCH(0; IF($B$4:$B$50<>0+$C$4:$C$50<>0+$D$4:$D$50<>0; 0; 1); 0))
    =INDEX($A$4:$A$50; MATCH(0; IF(OR($B$4:$B$50<>0;$C$4:$C$50<>0;$D$4:$D$50<>0); 0; 1); 0))

    Thanks!!
    W.

  2. #2
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,570

    Re: INDEX MATCH formula with multiple ctriteria - not getting it right...

    I don't get it. Do you want all the retrieved results in one cell?
    <----- If you were helped by my posts you can say "Thank you" by clicking the star symbol down to the left

    If the problem is solved, finish of the thread by clicking SOLVED under Thread Tools
    I don't wish to leave you with no answer, yet I sometimes miss posts. If you feel I forgot you, remind me with a PM or just bump the thread.

  3. #3
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,570

    Re: INDEX MATCH formula with multiple ctriteria - not getting it right...

    Ok, here is a start
    Attached Files Attached Files

  4. #4
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: INDEX MATCH formula with multiple ctriteria - not getting it right...

    Based on what you have tried,
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    07-03-2019
    Location
    Stockholm, Sweden
    MS-Off Ver
    2013
    Posts
    59

    Re: INDEX MATCH formula with multiple ctriteria - not getting it right...

    Yeah, I fixed it with inspiration from both your answers (working with sum)!
    Thanks a million to both of you for quick and helpful replies!

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,144

    Re: INDEX MATCH formula with multiple ctriteria - not getting it right...

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  7. #7
    Registered User
    Join Date
    07-03-2019
    Location
    Stockholm, Sweden
    MS-Off Ver
    2013
    Posts
    59

    Re: INDEX MATCH formula with multiple ctriteria - not getting it right...

    Done, thanks for the comment AliGW! :-)

  8. #8
    Registered User
    Join Date
    11-06-2012
    Location
    Brandon, MS
    MS-Off Ver
    Excel 2011 for mac
    Posts
    34

    Re: INDEX MATCH formula with multiple ctriteria - not getting it right...

    I have a similar problem, trying to have an index and match formula that looks up 2 columns and 1 row. Not having any luck. Any help will be greatly appreciated.
    Attached Files Attached Files

  9. #9
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,144

    Re: INDEX MATCH formula with multiple ctriteria - not getting it right...

    Administrative Note:

    We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

    Please see Forum Rule #4 about hijacking and start a new thread for your query.

    If you are not familiar with how to start a new thread see the FAQ: How to start a new thread

+ 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] Need Excel formula to use INDEX and MATCH with multiple criteria's over multiple ranges.?
    By mchilapur in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-22-2017, 08:56 AM
  2. Replies: 5
    Last Post: 10-16-2016, 02:33 AM
  3. [SOLVED] Index & Multiple match Formula not quite right
    By coach.32 in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 10-26-2014, 08:58 PM
  4. Replies: 2
    Last Post: 09-27-2014, 04:34 PM
  5. [SOLVED] Formula with multiple ctriteria (Sum(Countif)
    By kokapelly in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 01-13-2014, 12:58 PM
  6. Excel 2007 : Index, Match, Large Formula: Multiple Criteria, Multiple Ranges
    By SimpleJack in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 07-19-2013, 08:54 AM
  7. multiple match/index formula
    By anguyen27 in forum Excel Formulas & Functions
    Replies: 17
    Last Post: 09-17-2012, 03:49 PM

Tags for this Thread

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