Index Error

[mmac723]

I have a large data set (87,000+ lines). There are duplicates in the data, so VLOOKUP will not work, and I've been using INDEX/MATCH. I've been running into A LOT of issues.
I got the following formula to work - kinda.

=IFERROR(INDEX('[Historic Lead Data_2019 09 13.xlsx]Sheet1'!$G$2:$G$87205,SMALL(IF($J68481='[Historic Lead Data_2019 09 13.xlsx]Sheet1'!$F$2:$F$87205, ROW('[Historic Lead Data_2019 09 13.xlsx]Sheet1'!$G$2:$G$87205)-1," "),COLUMN()-11)),"PENDING")

When I tried to lock the data set (adding $'s) -it ruined the formula -broke it to a #VALUE error, when it was previously working.
Im so stuck. Anyone have any advice??

 

[joeu2004]

I have not studied the formula, but my first impression is: did you array-enter the formula by pressing ctrl+shift+Enter instead of just Enter?

I suspect that is necessary in order for the formula to work as intended.

That fact that it seemed to work before making changes might have more to do with the location of the formula than with the changes.

Often, formulas that should be array-entered seem to "work" (insofar as they do not return a #VALUE error) if they are normally-entered in a row that is referenced in the formula. Long story why; probably not relevant.

The #VALUE error arises when the formula is normally-entered in a non-referenced row.

Just a wild guess on my part. I hope it is not a misdirection. Again, I have not studied the formula at all.

 

[nr6281]

HI can you share a sample excel workbook?

 

[jasonb75]

Aside from the formula not being aray confirmed as suggested by joeu2004, COLUMN()-11 is the potential fail point that stands out if the formula has been moved to a different column.

As you've used COLUMN() I'm assuming that you're dragging the formula left to right, see if this alternative is more stable (doesn't need to be array confirmed).

Note that the cell refernce shown in red should refer to the cell holding the first formula before dragging right.

The bold section is optional to improve performance by removing the need to calulate the whole array after the first error is found.

=IF(K2="PENDING","PENDING",IFERROR(INDEX('[Historic Lead Data_2019 09 13.xlsx]Sheet1'!$G:$G,AGGREGATE(15,6,ROW('[Historic Lead Data_2019 09 13.xlsx]Sheet1'!$G$2:$G$87205)/($J68481='[Historic Lead Data_2019 09 13.xlsx]Sheet1'!$F$2:$F$87205),COLUMNS($L2:L2))),"PENDING"))

 

[mmac723]

I have not studied the formula, but my first impression is: did you array-enter the formula by pressing ctrl+shift+Enter instead of just Enter?

I can't believe this was it... Whelp. I feel like an idiot.
thanks so much my people!!