+ Reply to Thread
Results 1 to 7 of 7

Index & Match Formula for Unique Values On Multiple Criteria

  1. #1
    Registered User
    Join Date
    04-05-2018
    Location
    Cleveland, Ohio
    MS-Off Ver
    Excel 2007
    Posts
    11

    Index & Match Formula for Unique Values On Multiple Criteria

    I'm trying to pull in unique text (invoice number) from a master list of data, but only if two criteria are met... I've been able to figure out how to pull in the data with one criteria (vendor name), but getting duplicate results. I haven't been able to figure out the correct formula to have it go to the next unique record. Also, i need to figure out how to add a second criteria (date and vendor) before it generates a result.

    This is the formula that works to pull in one criteria.

    =IF(ISERROR(INDEX('Master Invoice Entry'!$A:$R,SMALL(IF('Master Invoice Entry'!$B:$B='Memo Creator'!$B$17,ROW('Master Invoice Entry'!$A:$R)),ROW(2:2))-1,1)),"",INDEX('Master Invoice Entry'!$A:$R,SMALL(IF('Master Invoice Entry'!$B:$B='Memo Creator'!$B$17,ROW('Master Invoice Entry'!$A:$R)),ROW(2:2))-1,1))

    The second criteria that needs to be met is 'Master Invoice Entry'!$C:$C='Memo Creator'!$N$9

    So basically, if 'Master Invoice Entry'!$C:$C='Memo Creator'!$N$9 and 'Master Invoice Entry'!$B:$B='Memo Creator'!$B$1 are met, I want the formula to pull in an invoice number (but on the master invoice entry, some invoices have multiple lines)

    Invoice Number Vendor Name Date
    1234 DEF Company March 2018
    1234 DEF Company March 2018
    ABCD DEF Company March 2018
    ABCD DEF Company March 2018
    9876 DEF Company April 2018

    So if the criteria were DEF company and March 2018 I would want two results, invoice 1234 and ABCD... but currently I'm getting all five lines, not just the two unique invoice numbers.

    Help please!

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,409

    Re: Index & Match Formula for Unique Values On Multiple Criteria

    Welcome to the forum! Will you please attach a sample Excel workbook? We are not able to work with or manipulate a description of one and nobody wants to have to recreate your data from scratch.

    1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

    2. Make sure that your desired results are also shown (mock up the results manually).

    3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

    4. Try to avoid using merged cells as they cause lots of problems.

    Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

    Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Registered User
    Join Date
    04-05-2018
    Location
    Cleveland, Ohio
    MS-Off Ver
    Excel 2007
    Posts
    11

    Re: Index & Match Formula for Unique Values On Multiple Criteria

    Thank you! I've attached the worksheet, and cut out all of the identifying information. I added three "desired results" tabs, for the three different vendors, and I left the formulas that work intact.

    Essentially, on the Memo Creator tab, the only two fields that should be manipulated are B17 (a drop down LOVs) and N9.

    When you choose 123 Company, I only want two out of the three invoices listed on the Masters Invoice Entry tab to appear. The ones that match 123 company and are for the month of March. I do NOT want the April 2018 invoice to appear.

    Hope this makes sense.

    Thank you!!
    Attached Files Attached Files

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,531

    Re: Index & Match Formula for Unique Values On Multiple Criteria

    Here is a low tech proposal.
    Populate column R on the Master Invoice Entry sheet using: =IF(A2<>A1,1,"")
    Populate the range D24:D31 on the desired results sheets using*:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    *Denotes an array entered formula that needs to be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
    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.

  5. #5
    Forum Expert shukla.ankur281190's Avatar
    Join Date
    05-17-2014
    Location
    Lucknow, India
    MS-Off Ver
    Office 365
    Posts
    3,935

    Re: Index & Match Formula for Unique Values On Multiple Criteria

    Try

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


    Copy down!!
    Last edited by shukla.ankur281190; 04-10-2018 at 01:26 AM. Reason: Changes as per Jet Message me
    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)

  6. #6
    Registered User
    Join Date
    04-05-2018
    Location
    Cleveland, Ohio
    MS-Off Ver
    Excel 2007
    Posts
    11

    Re: Index & Match Formula for Unique Values On Multiple Criteria

    That worked!!! Thank you so much!!!

  7. #7
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,531

    Re: Index & Match Formula for Unique Values On Multiple Criteria

    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. [SOLVED] Looking for help on unique value pull with multiple criteria using Index Match
    By shinobi2u in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-09-2017, 05:07 PM
  2. [SOLVED] Index & Match Formula for Unique Values On Multiple Criteria
    By Neilesh Kumar in forum Excel General
    Replies: 2
    Last Post: 03-24-2017, 10:13 AM
  3. [SOLVED] Index & Match Formula with Unique Values bases on a criteria
    By Neilesh Kumar in forum Excel General
    Replies: 5
    Last Post: 03-24-2017, 06:22 AM
  4. Two Criteria Index Match on Multiple Sheets with Multiple Values
    By MegganM in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 01-21-2016, 09:51 PM
  5. [SOLVED] Index/Match with Multiple Criteria and Multiple return values
    By Brawnystaff in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 09-28-2015, 10:27 AM
  6. Replies: 0
    Last Post: 07-08-2014, 09:51 AM
  7. [SOLVED] Two criteria Index Match on multiple sheets returning multiple values
    By Joak in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-21-2014, 10:03 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1