• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Multiple Match Formula

T

TPR

Member
Dear Friends,

Have just become crazy, can't understand my mistake in formulas.
Please go thru the appended file. I think both the formulas should work but giving me error.
Pls help
Regards
TPR
 

Attachments

  • multiple match.xlsx
    8.6 KB · Views: 15
Hmm, a couple of mistakes in your formula.
Might work like this (enter as array formula)
=INDEX($C$2:$E$5,MATCH(I3&J3,($A$3:$A$5)&($B$3:$B$5),0),MATCH(K3,$C$2:$E$2,0))

There have been similar threads before, and better formulas provided. Try to query it and look for replies by Bosco_yip for example.
 

Attachments

  • Copy of multiple match.xlsx
    8.9 KB · Views: 8
In your first formula you're using INDEX(matrix, row, col, area), this of course gives an error since you're providing (matrix, row, row, col).
In the second formula you're using INDEX(matrix, row) and that would just return the whole row instead of only the rate.

Here's 2 formulas that gives the correct results:
=SUMPRODUCT(($A$3:$A$5=I3)*($B$3:$B$5=J3)*($C$2:$E$2=K3)*$C$3:$E$5)

=INDEX($C$3:$E$5,INDEX(MATCH(I4&J4,$A$3:$A$5&$B$3:$B$5,0),),MATCH(K4,$C$2:$E$2,0))
 
GraH - Guido said:
Hmm, a couple of mistakes in your formula.
Might work like this (enter as array formula)
=INDEX($C$2:$E$5,MATCH(I3&J3,($A$3:$A$5)&($B$3:$B$5),0),MATCH(K3,$C$2:$E$2,0))

There have been similar threads before, and better formulas provided. Try to query it and look for replies by Bosco_yip for example.
Click to expand...
1] GraH - Guido's CSE-formula have a mistake :

=INDEX($C$2:$E$5,MATCH(I3&J3,($A$3:$A$5)&($B$3:$B$5),0),MATCH(K3,$C$2:$E$2,0))

and, should read as :

=INDEX($C$3:$E$5,MATCH(I3&J3,($A$3:$A$5)&($B$3:$B$5),0),MATCH(K3,$C$2:$E$2,0))

2] Or, in refer to the OP's particular case, in the "Input Table" column A, A3:A5 seemed all put "APPLE", then you can use this non-CSE formula :

=INDEX(C$3:E$5,MATCH(I3,A$3:A$5,0)+MATCH(J3,B$3:B$5,0)-1,MATCH(K3,C$2:E$2,0))

3] And, there are other possible formula version :

=SUMIFS(INDEX(C$3:E$5,0,MATCH(K3,C$2:E$2,0)),A$3:A$5,I3,B$3:B$5,J3)

or,

=VLOOKUP(I3&J3,IF({1,0,0,0},INDEX(A$3:A$5&B$3:B$5,0),B$3:E$5),MATCH(K3,B$2:E$2,0),0)

Regards
Bosco
 
Last edited:
bosco_yip said:
1] GraH - Guido's CSE-formula have a mistake :

=INDEX($C$2:$E$5,MATCH(I3&J3,($A$3:$A$5)&($B$3:$B$5),0),MATCH(K3,$C$2:$E$2,0))

and, should read as :

=INDEX($C$3:$E$5,MATCH(I3&J3,($A$3:$A$5)&($B$3:$B$5),0),MATCH(K3,$C$2:$E$2,0))

2] Or, in refer to the OP's particular case, in the "Input Table" column A, A3:A5 seemed all put "APPLE", then you can use this non-CSE formula :

=INDEX(C$3:E$5,MATCH(I3,A$3:A$5,0)+MATCH(J3,B$3:B$5,0)-1,MATCH(K3,C$2:E$2,0))

3] And, there are other possible formula version :

=SUMIFS(INDEX(C$3:E$5,0,MATCH(K3,C$2:E$2,0)),A$3:A$5,I3,B$3:B$5,J3)

or,

=VLOOKUP(I3&J3,IF({1,0,0,0},INDEX(A$3:A$5&B$3:B$5,0),B$3:E$5),MATCH(K3,B$2:E$2,0),0)

Regards
Bosco
Click to expand...
Yes You are perfect sir. In GraH - Guido's formula there was an mistake, result was not coming perfect.

But I can't understand what was my mistake in my second formula.
Regards
TPR
 
TPR said:
Yes You are perfect sir. In GraH - Guido's formula there was an mistake, result was not coming perfect.

But I can't understand what was my mistake in my second formula.
Regards
TPR
Click to expand...
This is your 2nd CSE-formula :

=INDEX($A$2:$E$5,MATCH(1,(I4=$A$3:$A$5)*(J4=$B$3:$B$5)*(K4=$C$2:$E$2)))

Should read as (also, CSE-formula) :

=INDEX($A$3:$E$5,MATCH(1,(I4=$A$3:$A$5)*(J4=$B$3:$B$5),0),MATCH(K4,$C$2:$E$2,0))

Regards
Bosco
 
You must log in or register to reply here.
Back
Top