# Problems with multi-criteria Index/Match. LOOKUP works. Index/Match, SUMPRODUCT,ARRAY fail

1. ## Problems with multi-criteria Index/Match. LOOKUP works. Index/Match, SUMPRODUCT,ARRAY fail

I am having problems with multi-criteria Index/Match and SumProduct.
I can get LOOKUP to work.

Can you look a test .xlsx file for me?

This works: =LOOKUP(2,1/(\$A\$2:\$A\$6=D2)/(\$B\$2:\$B\$6=E2),(\$C\$2:\$C\$6))
This fails: =INDEX(\$C\$2:\$C\$6,MATCH(D2&E2,\$A\$2:\$A\$6&\$B\$2:\$B\$6,0)) #VALUE!
=INDEX(\$C\$2:\$C\$6,MATCH(D2&E2,A2:A6&B2:B6,0)) #VALUE!
=INDEX(\$C\$2:\$C\$6,MATCH(1,(D2=\$A\$2:\$A\$6)*(E2=\$B\$2:\$B\$6),0)) #N/A
=INDEX(\$C\$2:\$C\$6,SUMPRODUCT((\$A\$2:\$A\$6=D2)*(\$B\$2:\$B\$6=E2)*ROW(\$C\$2:\$C\$6)),0)
SUMPRODUCT seems to calculate "off a row" and "muffs" either the first or last row.

Again, I can get it to work. What I expected to get it done failed.

Bottom line I "burned" a lot of time at MS and other googled sites and LOOKUP is the only one standing.  Register To Reply

2. ## Re: Problems with multi-criteria Index/Match. LOOKUP works. Index/Match, SUMPRODUCT,ARRAY

Looks like you tried to enter array formula. Try to enter the formula with Ctrl + Shift + Enter keys together.  Register To Reply

3. ## Re: Problems with multi-criteria Index/Match. LOOKUP works. Index/Match, SUMPRODUCT,ARRAY

You don't need to enter an array formula if you modify your original to: ``Please Login or Register  to view this content.``  Register To Reply

4. ## Re: Problems with multi-criteria Index/Match. LOOKUP works. Index/Match, SUMPRODUCT,ARRAY

The formulas you have in columns F H and I are Array formuls that MUST be entered with CTRL + SHIFT + ENTER
Highlight the cell with the formula, press F2
Then Press CTRL + SHIFT + ENTER
When correctly entered, the formula will be enclosed in {brackets}

The formuals in J K and L will not work because the Row Function returns a row #.
You're indexing C2:C6, but if it's Row 2 that is matching, then the Row function returns 2 (2 is the 2nd row)
But, INDEX(C2:C6,2) = C3 (C3 is the 2nd position in C2:C6)

If the matching value is in C6, then Row = 6
Index(C2:C6,6) = #REF! because there is no 6th position in C2:C6 (there are only 5 cells in C2:C6)

Hope that helps clear it up.  Register To Reply

5. ## Re: Problems with multi-criteria Index/Match. LOOKUP works. Index/Match, SUMPRODUCT,ARRAY

Get it done, pls check the file, nothing wrong with formulas, you just missed a few things

Regards  Register To Reply

6. ## Re: Problems with multi-criteria Index/Match. LOOKUP works. Index/Match, SUMPRODUCT,ARRAY

i have a similar problem. I want the reference from other sheet instead of the same sheet.

I want to match 3 array of sheet 2 and also from other workbook in common server with 3 cells from sheet1 and display the corresponding value.  Register To Reply

7. ## Re: Problems with multi-criteria Index/Match. LOOKUP works. Index/Match, SUMPRODUCT,ARRAY

It could be wise if U make a new post, do not post in others post....

Regards
Azumi  Register To Reply