lookup data from table

[pichai]

Hi, I need some help of the code for lookup value of point 1 and point2 from the table ,if i key in the no.group and type of grade.
eg.1 Group10 grade a value to be point1=75 point2=20
eg.2 Group16 grade B value to be point1=125 point2=172
How can i do it,please
pichai.

[NBVC]

Try this formula in I8:

=SUMPRODUCT(($B$4:$B$43=$G$8)*($C$4:$C$43=$H$8),D$4:D$43)

and copy it to J8.

[pichai]

Hi,thank for your best code ,that it work.
can by anyway to do this by the another code ,vlookup or not,please?

[tigertiger]

Try this formula in I8:

=SUMPRODUCT(($B$4:$B$43=$G$8)*($C$4:$C$43=$H$8),D$4:D$43)

and copy it to J8.

Hi,thank for your best code ,that it work.
can by anyway to do this by the another code ,vlookup or not,please?

OR we can use SUMIFS in Excel 2007

cell I8

=SUMIFS(D$4:D$43,$B$4:$B$43,$G$8,$C$4:$C$43,$H$8) 

Syntax is extracted from help of Microsoft:

SUMIFS(sum_range,criteria_range1,criteria1,criteria_range2,criteria2…)

Sum_range is one or more cells to sum, including numbers or names, arrays, or references that contain numbers. Blank and text values are ignored.

Criteria_range1, criteria_range2, … are 1 to 127 ranges in which to evaluate the associated criteria.

Criteria1, criteria2, … are 1 to 127 criteria in the form of a number, expression, cell reference, or text that define which cells will be added. For example, criteria can be expressed as 32, "32", ">32", "apples", or B4.