[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


excel index match

Posted on 2011-05-03
Medium Priority
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
  • 3
  • 2
  • 2
  • +2
LVL 22

Expert Comment

ID: 35515365
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

ID: 35515370
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

ID: 35515419
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')?
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.


Author Comment

ID: 35515444
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 42

Expert Comment

ID: 35515595
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 42

Expert Comment

ID: 35515604
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

ID: 35515672
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

barry houdini earned 2000 total points
ID: 35515757
> 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 42

Expert Comment

ID: 35516178
@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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This article describes a serious pitfall that can happen when deleting shapes using VBA.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

834 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