+ Reply to Thread
Results 1 to 12 of 12

Combining INDEX/MATCH with several criteria and INDEX/SMALL

  1. #1
    Registered User
    Join Date
    06-05-2017
    Location
    Oslo, Norway
    MS-Off Ver
    Office365/2016
    Posts
    14

    Combining INDEX/MATCH with several criteria and INDEX/SMALL

    I am playing around with Index in an effort to finally learn it.
    I have however come to a snag for which I cannot find a thread.

    I would greatly appreciate if someone could point me to the correct thread or a solution!

    I have found a way to find a value that meets three criterias:
    Please Login or Register  to view this content.
    This only finds the first value, and not the rest.

    I have also found a way to find all the values, (and sort them in multiple columns), but this only accepts one criteria:
    Please Login or Register  to view this content.
    How do I combine these into one sentence?

    I tried by entering an AND statement to the IF statement in the second, but this returns the heading of the column in the table where I look.

  2. #2
    Forum Expert tim201110's Avatar
    Join Date
    10-23-2011
    Location
    Russia
    MS-Off Ver
    2016, 2019
    Posts
    2,359

    Re: Combining INDEX/MATCH with several criteria and INDEX/SMALL

    =IFERROR(INDEX(Data_Entry[[#All];[NPS E-mail adress]];SMALL(IF((Menu!$B$3=Data_Entry[[#All];[Order Type]])*(Data_Entry[[#All];[Add to eMarketeer]]=Menu!$A$2)*(Data_Entry[[#All];[Sent?]]= Menu!$A$3);0);ROW(Data_Entry[[#All];[NPS E-mail adress]])-MIN(ROW(Data_Entry[[#All];[NPS E-mail adress]]))+1);COLUMNS($C$5:C5)));"")

  3. #3
    Registered User
    Join Date
    06-05-2017
    Location
    Oslo, Norway
    MS-Off Ver
    Office365/2016
    Posts
    14

    Re: Combining INDEX/MATCH with several criteria and INDEX/SMALL

    Quote Originally Posted by tim201110 View Post
    =IFERROR(INDEX(Data_Entry[[#All];[NPS E-mail adress]];SMALL(IF((Menu!$B$3=Data_Entry[[#All];[Order Type]])*(Data_Entry[[#All];[Add to eMarketeer]]=Menu!$A$2)*(Data_Entry[[#All];[Sent?]]= Menu!$A$3);0);ROW(Data_Entry[[#All];[NPS E-mail adress]])-MIN(ROW(Data_Entry[[#All];[NPS E-mail adress]]))+1);COLUMNS($C$5:C5)));"")
    Thank you for the quick reply!
    This does however only return the heading of the table, and nothing more.

    Excel INDEX MATCH SMALL.gif

  4. #4
    Forum Expert shukla.ankur281190's Avatar
    Join Date
    05-17-2014
    Location
    Lucknow, India
    MS-Off Ver
    2016
    Posts
    3,834

    Re: Combining INDEX/MATCH with several criteria and INDEX/SMALL

    Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
    If I helped, Don't forget to add reputation (click on the little star ★ at bottom of this post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)

  5. #5
    Registered User
    Join Date
    06-05-2017
    Location
    Oslo, Norway
    MS-Off Ver
    Office365/2016
    Posts
    14

    Re: Combining INDEX/MATCH with several criteria and INDEX/SMALL

    Quote Originally Posted by shukla.ankur281190 View Post
    Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
    I have done so!
    Attached Files Attached Files

  6. #6
    Forum Expert tim201110's Avatar
    Join Date
    10-23-2011
    Location
    Russia
    MS-Off Ver
    2016, 2019
    Posts
    2,359

    Re: Combining INDEX/MATCH with several criteria and INDEX/SMALL

    =IFERROR(INDEX(Data_Entry[[#All],[NPS E-mail adress]],AGGREGATE(15,6,ROW(Data_Entry[[#All],[Order Type]])/(Menu!$B$3=Data_Entry[[#All],[Order Type]])/(Data_Entry[[#All],[Add to eMarketeer]]=Menu!$A$2)/(Data_Entry[[#All],[Sent?]]= Menu!$A$3),COLUMNS($C$5:C5))),"")
    not array

  7. #7
    Registered User
    Join Date
    06-05-2017
    Location
    Oslo, Norway
    MS-Off Ver
    Office365/2016
    Posts
    14

    Re: Combining INDEX/MATCH with several criteria and INDEX/SMALL

    Quote Originally Posted by tim201110 View Post
    =IFERROR(INDEX(Data_Entry[[#All],[NPS E-mail adress]],AGGREGATE(15,6,ROW(Data_Entry[[#All],[Order Type]])/(Menu!$B$3=Data_Entry[[#All],[Order Type]])/(Data_Entry[[#All],[Add to eMarketeer]]=Menu!$A$2)/(Data_Entry[[#All],[Sent?]]= Menu!$A$3),COLUMNS($C$5:C5))),"")
    not array
    Super! This works. Thank you very much!
    As it is now not an array anymore. How do one lock Data_Entry[[#All];[NPS E-mail adress]] like $B4 to prevent it from changing when copying the cells to other cells?

  8. #8
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    12,283

    Re: Combining INDEX/MATCH with several criteria and INDEX/SMALL

    Try the following modification of Tim's formula:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  9. #9
    Registered User
    Join Date
    06-05-2017
    Location
    Oslo, Norway
    MS-Off Ver
    Office365/2016
    Posts
    14

    Re: Combining INDEX/MATCH with several criteria and INDEX/SMALL

    I have tried that, but it only returns the last entry. Copy paste works for now. I was just curious.

    Thank you for your help and assistance!

  10. #10
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    12,283

    Re: Combining INDEX/MATCH with several criteria and INDEX/SMALL

    It only shows one email address if PM is in cell B3 on the Menu sheet because there is only one email address that is associated with PM. If Brake & Fix is placed in cell B3 then the formula shows both associated email addresses as modeled in row 8 of the attached copy of the file.
    Let us know if you have any questions.
    Attached Files Attached Files

  11. #11
    Registered User
    Join Date
    06-05-2017
    Location
    Oslo, Norway
    MS-Off Ver
    Office365/2016
    Posts
    14

    Re: Combining INDEX/MATCH with several criteria and INDEX/SMALL

    Ahh!

    I had a write error in the real file. Thank you very much!

  12. #12
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    12,283

    Re: Combining INDEX/MATCH with several criteria and INDEX/SMALL

    You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools drop down in the ribbon above your first post. I hope that you have a blessed day.

+ 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. Index, Match, Small with multiple criteria
    By anhlu in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-04-2017, 07:37 PM
  2. Match Index, Small Index with Concatenate
    By kharding15 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-30-2015, 05:38 PM
  3. Index / Match / Small by Month with Date Criteria
    By marcusduton in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-20-2015, 05:37 AM
  4. [SOLVED] Combining 3 Formulas: Return all Names that Match Criteria Using Index/Match
    By bchilme in forum Excel Formulas & Functions
    Replies: 20
    Last Post: 09-29-2014, 09:28 AM
  5. Replies: 9
    Last Post: 08-29-2014, 09:42 PM
  6. Replies: 6
    Last Post: 04-30-2014, 02:42 AM
  7. [SOLVED] INDEX MATCH SMALL ROW, Double criteria Lookup
    By GP_SRT in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 11-22-2013, 11:15 AM

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