MATCH FUNCTION using workbook links

[hartsojb]

Well what im try to do is modify an exixting spreadsheet, currently the
formula looks at a cell value in column AX then the match formula looks
in the file yarncost in column B for the value from column AX and
returns 0 if it does. The problem is that the spreadsheet yarncost has
started getting to many entries on one sheet so im trying to split it
into categories onto 5 seperate sheets. So i need the formula to be
able to search all 5 sheets on yarncost for the value from AX. The
following is the current formula. It works properly for a single
worksheet in yarncost. Any suggestions on how to make it work for 5
worksheets would be appreciated. I thinkered with using a nested IF
function but didn't get anywhere.

=IF(AX142=0,0,(+MATCH(AX142,[YARNCOST.XLS]Sheet1!$B$21:$B$391,0)-MATCH(AX142,[YARNCOST.XLS]Sheet1!$B$21:$B$391,0)))

 

[Harlan Grove]

Well what im try to do is modify an exixting spreadsheet, currently the
formula looks at a cell value in column AX then the match formula looks
in the file yarncost in column B for the value from column AX and
returns 0 if it does. The problem is that the spreadsheet yarncost has
started getting to many entries on one sheet so im trying to split it
into categories onto 5 seperate sheets. So i need the formula to be
able to search all 5 sheets on yarncost for the value from AX. The
following is the current formula. It works properly for a single
worksheet in yarncost. Any suggestions on how to make it work for 5
worksheets would be appreciated. I thinkered with using a nested IF
function but didn't get anywhere.

=IF(AX142=0,0,(+MATCH(AX142,[YARNCOST.XLS]Sheet1!$B$21:$B$391,0)
-MATCH(AX142,[YARNCOST.XLS]Sheet1!$B$21:$B$391,0)))

Excel provides 65,536 rows per worksheet, and your formula above shows ranges of
only 371 rows. Are you really running out of rows? I find it hard to imagien
that you have more than 65,000 different entries. Is there some other reason you
believe you have too many entries to fit in one worksheet? There may be
alternative data layouts that could accomodate your other needs while leaving a
single large table to use in formulas.