Searching & matching data from a table

[aamirsq]

I am trying to search from a table but its giving me a #REF err.

 =INDEX(F1:O27,MATCH('Original Data'!G2,Data!$F$3:$F$27,0),MATCH('Original Data'!I2,Data!$G$3:$G$27,0),MATCH('Original Data'!J2,Data!$H$1:$O$1,0))

What i have is like this:

Table:
Sites Types CO1 CO2 CO3 CO4 CO5
A A1 10 11 12
A A2 15
A A3 20
A A4 25
A A5 30 45 55
B A1 10 11
B A2 10
B A3 19 30
C A1 11
C A2 12


Sites ( A, B, C with each types A1 to A5 max/less & i have to choose the price from COs (CO1 to CO5 ) prices. [e.g. if i select A. A3. CO1=20]

 

[Somendra Misra]

Hi Aamir,

First, your last two lines are confusing, w.r.t what you want the result of your formula and on what basis.
Second post a sample file.

Regards,

 

[aamirsq]

i tried this and it works

=IF(J2=Data!$H$2,SUMIFS(Data!$H$2:$H$89,Data!$F$2:$F$89,G2,Data!$G$2:$G$89,I2) ,IF(J2=Data!$I$2,SUMIFS(Data!$I$2:$I$89,Data!$F$2:$F$89,G2,Data!$G$2:$G$89,I2) ,IF(J2=Data!$J$2,SUMIFS(Data!$J$2:$J$89,Data!$F$2:$F$89,G2,Data!$G$2:$G$89,I2) ,IF(J2=Data!$K$2,SUMIFS(Data!$K$2:$K$89,Data!$F$2:$F$89,G2,Data!$G$2:$G$89,I2) ,IF(J2=Data!$L$2,SUMIFS(Data!$L$2:$L$89,Data!$F$2:$F$89,G2,Data!$G$2:$G$89,I2) ,IF(J2=Data!$M$2,SUMIFS(Data!$M$2:$M$89,Data!$F$2:$F$89,G2,Data!$G$2:$G$89,I2) ,IF(J2=Data!$N$2,SUMIFS(Data!$N$2:$N$89,Data!$F$2:$F$89,G2,Data!$G$2:$G$89,I2) ,IF(J2=Data!$O$2,SUMIFS(Data!$O$2:$O$89,Data!$F$2:$F$89,G2,Data!$G$2:$G$89,I2) ))))))))

 

[aamirsq]

Can i make this formula short ?

 

[Debraj]

Hi Aamir..

Try this..

=SUMIFS(OFFSET(Data!$G$2:$G$26,,MATCH($J2,Data!$H$1:$O$1,0)),Data!F$2:F$26,$G2,Data!G$2:G$26,$I2)

 

[Somendra Misra]

My Suggestion:

=SUMPRODUCT((G2=Data!$F$2:$F$26)*(Data!$G$2:$G$26='Original Data'!I2)*('Original Data'!J2=Data!$H$1:$O$1)*(Data!$H$2:$O$26))

Regards,