INDEX formula problem

[ddub25]

=INDEX(BL11:BL90,MATCH(1,(BJ11:BJ90=J25)*(BK11:BK90=O25),0))

I have been trying to use the above formula to match 2 columns of data in Table 1 with 2 columns of data in Table 2. If there is a match, then I want the value in the 3rd column in Table 2 to be referenced into Column C of Table1. If no match is found then I want '0' to be returned.

For example:

Table 1
___Column A______Column B______Column C
1__Plums_________Grade A_______Formula Entered Here = 0
2__Bananas______ Grade C_______Formula Entered Here = 0
3__Oranges______ Grade B_______Formula Entered Here = 0
4__Apples________Grade A_______Formula Entered Here = 50

Table 2
___Column A______Column B______Column C
1__Apples________Grade A_______50
2__Pears_________Grade B_______62
3__Bananas_______Grade B_______55

In this example only cell C4 in Table 1 has found a match with Table 2 and so '50' is referenced.

 

[Aladin Akyurek]

On Excel 2007 and later...

Control+shift+enter, not just enter:

=IFERROR(INDEX(ResultRange,MATCH(1,IF(FruitRange=Fruit,IF(GradeRange=Grade,1)),0)),0)

On earlier excel versions...

Control+shift+enter, not just enter:

=LOOKUP(9.99999999999999E+307,CHOOSE({1,2},0,
    INDEX(ResultRange,MATCH(1,IF(FruitRange=Fruit,IF(GradeRange=Grade,1)),0)),0)

 

[Andrew Poulsom]

Did you remember to press Ctrl+Shift+Enter for that array formula? If you want a normal formula try:

=INDEX(BL11:BL90,MATCH(1,INDEX((BJ11:BJ90=J25)*(BK11:BK90=O25),0),0))

 

[ddub25]

Thanks for both your help. I hadn't pressed Ctrl+Shift+Enter. Although, thanks Andrew I used your secong option instead.

Dan