Why this match/Index formula works in Excel 2007 but not in Excel 2002??

[EvelynLu]

Hi all experts out there,

May I know why my below formula work for Excel 2007 but not Excel 2002?

=IFERROR(INDEX(PartNum!A1:C45000,MATCH(1,(PartNum!B:B=SR_From!H10)*(PartNum!C:C=SR_From!H11),0),1),"")

As for Excel 2007, there is nothing display when nothing match. Whereas when open the Excel file in version 2002,
it appears "#Name" error. May I know how can I rectify this issues?

Thank you so much for all advise.

 

[T. Valko]

The walk around is to change them to ranges....

=IF(ISERROR(INDEX(PartNum!A1:C45000,MATCH(1,(PartNum!B1:B45000=SR_From!H10)*(PartNum!C1:C45000=SR_From!H11),0),1)),"",INDEX(PartNum!A1:C45000,MATCH(1,(PartNum!B1:B45000=SR_From!H10)*(PartNum!C1:C45000=SR_From!H11),0),1))

If the data being returned is text that can be reduced to:

=LOOKUP("zzzzz",CHOOSE({1,2},"",INDEX(PartNum!A1:A45000,MATCH(1,(PartNum!B1:B45000=SR_From!H10)*(PartNum!C1:C45000=SR_From!H11),0))))

Still array entered.

 

[EvelynLu]

Thank you so much Valko.

It works wonder...

 

[T. Valko]

You're welcome! Thanks for the feedback!

 

[EvelynLu]

Valko,

Sorry.. I need help again or if anyone can help... As I put the whole list in the form and it cause the file to be quite big.

May we know for your code, how can I input the path so that it link to another file to pull out the result? I have try below and it works in Excel 2007 but not Excel 2002.

Look forward to hear anyone's advise too.

Please help me... Thank you very much.

=LOOKUP("zzzzz",CHOOSE({1,2},"",INDEX('Y:\SalesDept\Request\[PartsNo.xltm]Tidy'!A1:A45000,MATCH(1,('Y:\SalesDept\Request\[PartsNo.xltm]Tidy'!B1:B45000=SR_From!H10)*('Y:\Sales\Request\[PartsNo.xltm]Tidy'!C1:C45000=SR_From!H11),0))))

 

[Aladin Akyurek]

In X2 control+shift+enter, not just enter:

=MATCH(1,IF(PartNum!B1:B45000=SR_From!H10,IF(PartNum!C1:C4500=SR_From!H11,1)),0)

In X3 just enter:

=IF(ISNUMBER(X2),INDEX(PartNum!A1:C45000,X2),"")

Or as a single formula, control+shift+enter, not just enter:

=IF(ISNUMBER(MATCH(1,IF(PartNum!B1:B45000=SR_From!H10,IF(PartNum!C1:C4500=SR_From!H11,1)),0)),INDEX(PartNum!A1:C45000,MATCH(1,IF(PartNum!B1:B45000=SR_From!H10,IF(PartNum!C1:C4500=SR_From!H11,1)),0)),"")