Rather than trying to fix your formula, I'd rather show you a cleaner approach.
INDIRECT is a powerful but Volatile function that will always recalculate with every change in a workbook. If you're using it in an entire column, it can slow your workbook down immensely. You're also stuck searching an arbitrarily large area which has it's own risks and performance issues.
You can find the row number of the 2nd instance of a value using an array-entered function. Double click in a cell so you're in edit mode, paste in this formula and then confirm it using CRTL-SHIFT-ENTER:
=SMALL(IF('[InsightsDataExport.xlsx]Lead Profile'!$AP1:$AP10000=A1,ROW($A$1:$A$10000),FALSE),2)
The inside parts of this formula do the following:
'[InsightsDataExport.xlsx]Lead Profile'!$AP1:$AP10000=A1
creates an array of TRUE and FALSE values depending on whether each cell in AP1:AP10000 matches the value in the local A1
ROW($A$1:$A$10000)
creates an array of numbers from 1 to 10000 that are used if a cell matches A1, otherwise FALSE is used. So, if the value you're searching for is found in cells 2 and 5, the inside part of the formula has an array like:
{FALSE, 2, FALSE, FALSE, 5, FALSE....}
Then, SMALL(array,2) means to take the 2nd smallest numerical value, which will be the row number of the 2nd match, which in this case in 5.
To see it in slow motion, test it on a small range, then use the Evaluation Function tool from the Formulas tab.
Now that you have the row number, you can use an INDEX on the column you're actually searching for:
=INDEX('[InsightsDataExport.xlsx]Lead Profile'!$AR1:$AR10000,rownumber)
However, this is still operating over an arbitrarily large range to ensure it encompasses all values. This is poor practice for several reasons. Mainly, it never guarantees you include all data; in particular, if rows are being deleted the range will keep shrinking.
It's much tidier and easier to read if you work with tables instead.
If you insert a table onto your data, you can refer to column names instead. The functions will then search through only the number of rows that there are in the table.
On your Data, use Insert > Table. Then when you create formulas, if you select all data rows for a column in the table, excel will automatically insert the column name. So, if column AP has a heading called "ID", the formula to identify the row with the 2nd matching ID becomes:
=SMALL(IF(InsightsDataExport.xlsx!Table1[Id]=A1,ROW(InsightsDataExport.xlsx!Table1[Id]),FALSE),2)
You can then also use INDEX on the column that has the data you want to return. Just note that if you select only the data rows excluding the column heading, then row 2 is actually the first row of the index being searched, so you'll need to subtract 1 from the row number. If the data is in a column with heading "Amount", you'll use a formula like:
=INDEX(InsightsDataExport.xlsx!Table1[Amount],rownumber - 1)
