Trouble Referencing Data in Separate Spreadsheet

[Eszrai]

Hi, I’ve been working with the following array formula for about 3 months now and only recently has it begun to present some problems. I understand excel fairly well, but this formula is a bit complex for me. I was able to customize this formula from one I found online. I do not understand VBA, and am hoping someone may be able to correct this or point me in the right direction.

{=IF(ISERROR(INDEX('[Equipment.xlsx]Sheet1’!$A$2:$R$1000,SMALL(IF('[Equipment.xlsx]Sheet1’!$A$2:$R$1000=$P$6,ROW('[Equipment.xlsx]Sheet1’!$A$2:$R$1000)),ROW(1:1))-1,3)),"",INDEX('[Equipment.xlsx]Sheet1’!$A$2:$R$1000,SMALL(IF('[Equipment.xlsx]Sheet1’!$A$2:$R$1000=$P$6,ROW('[Equipment.xlsx]Sheet1’!$A$2:$R$1000)),ROW(1:1)*2-1)-1,14))}

It is designed to pull data from a table referenced in another file (Equipment.xlsx) per business. The ROW reference moves down the range and pulls the next item matching the reference point [ROW(1:1), ROW(2:2), etc.]. The unique (usually) reference point is the business phone number as a number was needed as the reference field for this formula to work. Some now contain a bit of text to differentiate. The [*2-1] near the end was incorporated after the fact due to some items being duplicated.

Lately however, instead of pulling info like:

Equipment 1 $100 123ABC
Equipment 2 $150 123JKL
Etc.

It has been giving:

Equipment 1 $100 123ABC
#NUM #NUM #NUM

The table within Equipment has grown, but is still within the $A$2:$R$1000 range. I have since been told it will continue to grow annually. I can’t make it an Excel Table (Ctrl+T) due to the main person updating this sheet. Out of about 50 or so worksheet tabs in the presentation spreadsheet, some are working fine. However closer to the end, they are all giving the #NUM error above. Any assistance at all would be very much appreciated!

 

[FDibbins]

Was the reference file open before (when it was working)?

Some excel functions will only work on open workbooks, so try opening the source and see if that solves the problem?

 

[Eszrai]

Both actually. I've had both documents open and saved to pull the most up to date info as well as both saved and Equipment.xlsx closed. Neither seems to be different as of yet.

 

[FDibbins]

OK then are you sure there IS a match? If there IS a (visual) match, are the 2 cells really identical? Check for leading or trailing spaces, typos, spelling, etc

 

[Eszrai]

Hi there, yes I am sure there is a match as it is pulling partial data. If there isn't a match (extra spacing, typo, etc) the formulas are left blank with nothing. I started copy & pasting from the data document to avoid this exact scenario. Sorry, this doesn't appear to be a simple fix