Lookup and match Multiple criteria

[Thomas Kuriakose]

Respected Sirs,

I tried the below to get he result for getting the quantity from Data tab in the attached. This is a sample data.

=VLOOKUP(A2,Data!A1:J109,MATCH(D2,Data!D2:D109,0)*9,FALSE)

Some of the values captured are correct, but as the data length increases, I get wrong data looked up.

What I need is, we need to lookup the material and match the location in data tab and get the corresponding quantity for the respective locations.

Kindly correct me on this.

thanks,

with regards,
thomas

 

[Villalobos]

Hi,

Check this array proposal.

 

[Thomas Kuriakose]

Respected Sir,

Thank you very much for the solution provided for this problem.

Much appreciated.

with regards,
thomas

 

[bosco_yip]

Maybe…………..

In I2, copy across to J2 and all down :

=SUMIFS(Data!I:I,Data!$A:$A,$A2,Data!$D:$D,$D2,Data!$F:$F,$F2)

Regards
Bosco

 

[Thomas Kuriakose]

Respected Sir,

Thank you very much for this support.

Kindly find attached the file, we have correct values till row 28, after which the data is not matching.

Kindly check.

Thank you very much,

with regards,
thomas

 

[Nebu]

Hi:

If you are looking for the results as in column I & J of your Lookup tab you can use the following non array formula as well.

ColumnI=INDEX(Data!$I$2:$I$109,MATCH(1,MMULT((Data!$A$2:$A$109=$A2)*(Data!$D$2:$D$109=$D2),1),0))

ColumnJ=INDEX(Data!$J$2:$J$109,MATCH(1,MMULT((Data!$A$2:$A$109=$A2)*(Data!$D$2:$D$109=$D2),1),0))

Thanks

 

[Thomas Kuriakose]

Respected Sir,

Thank you very much for this solution.

Thanks once again,

with regards,
thomas