excel index match

Posted on 2011-05-03
Last Modified: 2012-05-11
Hopefully someone can just see the syntax i have and tell me where the mistake is.  If not I will load a sample spreadsheet later.  The following is the formula is not working.

=INDEX('SAP Price'!$D$2:$D$3474,MATCH(1,INDEX(('SAP Price'!$B$2:$B$5000='03-2011'!D19)*('SAP Price'!$C$2:$C$5000='03-2011'!F19)*('SAP Price'!$E$2:$E$5000<='03-2011'!C19)*('SAP Price'!$F$2:$F$5000>'03-2011'!C19),0),0))

Question by:notasgoodasyou
    LVL 22

    Expert Comment

    What are you trying to do with this part?

    It returns a true/false and you're trying to multiply it with several other T/F values.  Was that what you wanted?

    True * True = True
    True * False = False
    False * True = False
    False * False = False
    LVL 39

    Expert Comment

    you have one 0 too many at the end.

    Also what happens if your match happens in lines 3475 and after?

    =INDEX('SAP Price'!$D$2:$D$5000,MATCH(1,INDEX(('SAP Price'!$B$2:$B$5000='03-2011'!D19)*('SAP Price'!$C$2:$C$5000='03-2011'!F19)*('SAP Price'!$E$2:$E$5000<='03-2011'!C19)*('SAP Price'!$F$2:$F$5000>'03-2011'!C19),0)))
    LVL 22

    Expert Comment

    When I past that into my Excel (and remove the sheet references) I get #N/A, which is not a syntax error but simply reporting that the search item was not found.

    Do those sheets exist ('SAP Price', '03-2011')?

    Author Comment

    The sheet exists,  I am trying to do an index match with 4 criteria.  

    i need the value returned from  'SAP Price'!$D$2:$D$5000

    'SAP Price'!$B$2:$B$5000='03-2011'!D19
    'SAP Price'!$C$2:$C$5000='03-2011'!F19
    'SAP Price'!$E$2:$E$5000<='03-2011'!C19
    'SAP Price'!$F$2:$F$5000>'03-2011'!C19

    LVL 41

    Expert Comment

    How about this?

    =SUMPRODUCT(('SAP Price'!$D$2:$D$5000)*('SAP Price'!$B$2:$B$5000='03-2011'!D19)*('SAP Price'!$C$2:$C$5000='03-2011'!F19)*('SAP Price'!$E$2:$E$5000<='03-2011'!C19)*('SAP Price'!$F$2:$F$5000>'03-2011'!C19)

    This should result in the sum of the match of all your critiera - if the match is one record, then it just returns that record.  If you put a -- in front of the first item that's being summed, it will return the count, to testify its retrieving one record for the sum..


    =SUMPRODUCT(--('SAP Price'!$D$2:$D$5000)*('SAP Price'!$B$2:$B$5000='03-2011'!D19)*('SAP Price'!$C$2:$C$5000='03-2011'!F19)*('SAP Price'!$E$2:$E$5000<='03-2011'!C19)*('SAP Price'!$F$2:$F$5000>'03-2011'!C19)

    Returns the count of the items returned from the match.

    If you have Excel 2007+ you can do this as well with the SUMIFS (again, it works if there's only one matching result).


    LVL 41

    Expert Comment

    Note - the suggested SUMPRODUCT needs a closing parenthesis:

    here it is, corrected:

    =SUMPRODUCT(('SAP Price'!$D$2:$D$5000)*('SAP Price'!$B$2:$B$5000='03-2011'!D19)*('SAP Price'!$C$2:$C$5000='03-2011'!F19)*('SAP Price'!$E$2:$E$5000<='03-2011'!C19)*('SAP Price'!$F$2:$F$5000>'03-2011'!C19))

    Author Comment

    The formula returns a value but they are all zeros. It should be returning the value that is in 'SAP Price'!$D$2:$D$5000
    LVL 50

    Accepted Solution

    > you have one 0 too many at the end

    No, Thomas, the zero is required but obviously the first range should be extended to row 5000 as per your suggestion, i.e.

    =INDEX('SAP Price'!$D$2:$D$5000,MATCH(1,INDEX(('SAP Price'!$B$2:$B$5000='03-2011'!D19)*('SAP Price'!$C$2:$C$5000='03-2011'!F19)*('SAP Price'!$E$2:$E$5000<='03-2011'!C19)*('SAP Price'!$F$2:$F$5000>'03-2011'!C19),0),0))

    The first zero is the 2nd argument of the INDEX function, the second zero is the third argument of the MATCH function.

    INDEX is only used here to avoid "array entering" - this version also works with CTRL+SHIFT+ENTER

    =INDEX('SAP Price'!$D$2:$D$5000,MATCH(1,('SAP Price'!$B$2:$B$5000='03-2011'!D19)*('SAP Price'!$C$2:$C$5000='03-2011'!F19)*('SAP Price'!$E$2:$E$5000<='03-2011'!C19)*('SAP Price'!$F$2:$F$5000>'03-2011'!C19),0))

    see attached in cells A19 and B19 of the sheet 03-2011. Both work OK so if you don't get a match that's because at least 1 of the criteria isn't matching, try using COUNTIF to check that there's a match for each, i.e.

    =COUNTIF('SAP Price'!$B$2:$B$5000,'03-2011'!D19)

    regards, barry
    LVL 41

    Expert Comment

    @notasgood - who's formula returns all zero's?

    If its mine, then check your criteria - you can break the sumproduct down one by one - again, if there's one match as a result of your criteria, it should work like a charm.


    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Join & Write a Comment

    I've recently been in need of an Excel macro that could add a letter before the text on multiple cells in an Excel document. My English is as it is, so I will try explain what it does diffrently. If you have an excel document with 2000 rows an…
    Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
    Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
    This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now