+ Reply to Thread
Results 1 to 9 of 9

Index-Match function with multiple criteria

  1. #1
    Registered User
    Join Date
    05-19-2015
    Location
    Boulder, Colorado
    MS-Off Ver
    2016
    Posts
    45

    Index-Match function with multiple criteria

    So I'm trying to create an index/match function that will input data into one sheet referencing the data in another. I want it to input the offer number into the PO sheet for the Purchase order it matches according to: Customer name,Product type, and project $ amount. The offer number should be inputted in the "Offer #" column. For some reason my index/match formula is returning with a "too many arguments" error. The way I understand the index function and the formula I have been trying is:

    =INDEX(Offers!B2:D24,match(Pos!B2,Offers!B2:B24,0,match(Pos!C2,Offers!C2:C24,0,match(Pos!D2,Offers!D2:D24,0),1

    I'm sure theres something wrong with the way I'm setting this up but I honestly have no idea what that could be since I've been trying everything. Any help would be greatly appreciated.
    I'll upload a sample file to show what I'm talking about.
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    09-20-2011
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    2,278

    Re: Index-Match function with multiple criteria

    Reinstated formula. See posts below.

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Last edited by DBY; 06-05-2015 at 12:55 PM. Reason: Reinstated formula.

  3. #3
    Forum Expert
    Join Date
    09-20-2011
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    2,278

    Re: Index-Match function with multiple criteria

    Hi
    The above post is not what you need. I've just realized the combinations of Customer, Product, Amount and Offer are not unique so the above formula is of no value, you need extra criteria applied perhaps the dates for each are unique?

  4. #4
    Registered User
    Join Date
    05-19-2015
    Location
    Boulder, Colorado
    MS-Off Ver
    2016
    Posts
    45

    Re: Index-Match function with multiple criteria

    Damn that worked perfectly, Gurus like you make the excel world go around. Thanks a lot DBY-much appreciated.

  5. #5
    Registered User
    Join Date
    05-19-2015
    Location
    Boulder, Colorado
    MS-Off Ver
    2016
    Posts
    45

    Re: Index-Match function with multiple criteria

    The dates aren't relevant to matching the data at all, I believe this what you gave me is correct but I'll double check

  6. #6
    Forum Expert
    Join Date
    09-20-2011
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    2,278

    Re: Index-Match function with multiple criteria

    The Combinations appear more than once with different offer numbers. The formula I gave will only return the first one it finds in the 'Offers Sheet'.

  7. #7
    Registered User
    Join Date
    05-19-2015
    Location
    Boulder, Colorado
    MS-Off Ver
    2016
    Posts
    45

    Re: Index-Match function with multiple criteria

    The actual project names/customer names/and amount are unique for the data that I'm using. The sheet I showed you was just a sample therefore the titles were not unique.

  8. #8
    Forum Expert
    Join Date
    09-20-2011
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    2,278

    Re: Index-Match function with multiple criteria

    That's fine as long as you're happy that it's returning the correct value, all's well. I'll edit my post again and reinsert the formula.

    DBY

  9. #9
    Registered User
    Join Date
    05-19-2015
    Location
    Boulder, Colorado
    MS-Off Ver
    2016
    Posts
    45

    Re: Index-Match function with multiple criteria

    Returning the first one is fine, I just want a system to show that an offer has been drafted for the Po that has come in. Returning with a bunch of offers would be over complicated for what I'm trying to do, when most of the time two offers wouldn't be created for the same company for the same amount.

+ 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. How do you drag down an index match function with multiple criteria?
    By larryg003 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-28-2014, 01:26 PM
  2. Using MATCH and INDEX function with multiple criteria
    By Babylon in forum Excel Programming / VBA / Macros
    Replies: 52
    Last Post: 02-06-2014, 11:28 AM
  3. Multiple Criteria Index/Match Function in VBA Looking Up Dates/Strings/Numbers
    By nadstradamus in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-21-2014, 12:35 PM
  4. Index and match function multiple criteria
    By mfortier3 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-16-2013, 05:27 AM
  5. SOS:Return multiple values against multi criteria match and index function
    By nitesh_inin in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 11-26-2012, 05:45 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