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.

 

[FDibbins]

try it and find out?

Try it on a dummy file though, so you dont mess up anything important

 

[T. Valko]

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.

What type of data does the formula return? Text? Numbers? Could be both? Something else?

 

[EvelynLu]

Hi mark858 and FDibbins

Appreciate your help. In 2002 , it does not pull out any data after key in the 2 requirement to match it. Whereas for 2007, the data is able to retrieve.

 

[EvelynLu]

Hi Valko,

It should return text.. as my record is retrieve from another tab which is link to another xlt file.

 

[T. Valko]

Hi mark858 and FDibbins

Appreciate your help. In 2002 , it does not pull out any data after key in the 2 requirement to match it. Whereas for 2007, the data is able to retrieve.

Another possibility...

In Excel 2002 you can't use entire columns as range references in array formulas.

 

[T. Valko]

Hi Valko,

It should return text.. as my record is retrieve from another tab which is link to another xlt file.

OK, I'll get back to you tomorrow. I'm going offline for the day.

 

[EvelynLu]

If cannot use entire column then what's the walk around method?

Thank you very much as need to solve this soon...

 

[FDibbins]

what formula are you currently using?

 

[MARK858]

If cannot use entire column then what's the walk around method?

Thank you very much as need to solve this soon...

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 above doesn't work then please answer Ford's question in post #18.

 

[EvelynLu]

Hi Guys,

Thank you soooo much. After change the range name, it can work... but have a problem here.

As my PartNum tab actually link to another file which has 45000 lines but the system shows error that
"Excel cannot complete this task with available resources. Choose less data or close other applications"

End up it only manage to pull only 16500 lines and not the rest. Is there any walk around method for this to be able to pull 45000 lines and yet without having this error message?

Really appreciate you guys help... Any advise will be grateful.

Thank you for save my life...
Thank you