Picking the data if criteria is qualified

Hi,
I am trying to calculate values in column H of sheet 1 according to condition/ rules mentioned in sheet 2 column D if the conditions mentioned in sheet 2 column A & B. It is important that both conditions of column & B should be satisfied for the calculation. The sheet has been attached. An urgent help is requested please.

Not to clear on what you want, but perhaps try this:

=INDEX(Sheet2!$D$2:$D$62,MATCH(1,INDEX((Sheet2!$A$2:$A$62=G2)*(Sheet2!$B$2:$B$62=F2),0),0))

copied down.

If you want to hide the #N/A errors where there are no matches, you can use IFERROR()

e.g

=IFERROR(INDEX(Sheet2!$D$2:$D$62,MATCH(1,INDEX((Sheet2!$A$2:$A$62=G2)*(Sheet2!$B$2:$B$62=F2),0),0)),"")

NBVC said:

Not to clear on what you want, but perhaps try this:

=INDEX(Sheet2!$D$2:$D$62,MATCH(1,INDEX((Sheet2!$A$2:$A$62=G2)*(Sheet2!$B$2:$B$62=F2),0),0))

copied down.

If you want to hide the #N/A errors where there are no matches, you can use IFERROR()

e.g

=IFERROR(INDEX(Sheet2!$D$2:$D$62,MATCH(1,INDEX((Sheet2!$A$2:$A$62=G2)*(Sheet2!$B$2:$B$62=F2),0),0)),"")

Click to expand...

Hi NBVC,
Thanks for your prompt response but it is not working as i have not defined the correct rules in sheet2. I have attached a fresh with correct rules. would appreciate, if you can have look to this matter please. Regards.

Seems you changed your sheet2 name a little and separated the 2 from the "Sheet"...

Try:

=INDEX('Sheet 2'!$D$2:$D$62,MATCH(1,INDEX(('Sheet 2'!$A$2:$A$62=G2)*('Sheet 2'!$B$2:$B$62=F2),0),0))

copied down