excel index match

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))

notasgoodasyouAsked:
Who is Participating?
 
barry houdiniCommented:
> 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
26991915.xlsx
0
 
rspahitzCommented:
What are you trying to do with this part?

($B$2:$B$5000=D19)
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
0
 
nutschCommented:
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)))
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
rspahitzCommented:
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')?
0
 
notasgoodasyouAuthor Commented:
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

where
'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
 
dlmilleCommented:
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..

E.g.,

=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).

Enjoy!

Dave
0
 
dlmilleCommented:
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))
0
 
notasgoodasyouAuthor Commented:
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
0
 
dlmilleCommented:
@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.

Dave
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.