Numbers Formula Syntax Error.

Numbers and Excel use a different syntax for addressing tables/sheets. Any formulas that include names of sheets/tables cannot be simply copied to Numbers from Excel. Often with a little re-typing you can convert one to the other. This is what importing is supposed to do, and does do, when it is able to.

Some functions and operations in Excel do not have an equivalent in Numbers, and vice versa. There are no 'array formulas" in Numbers, for example. That is the crux of your problem. Those formulas cannot be imported or simply retyped using the Numbers syntax. You will have to do some redesigning of the spreadsheet to make it work.

What the array formula is doing is stepping through a list of cell references, running the array part of the formula on each of them in turn, then performing an operation on all the results.

When L7<>0, the formula

=IF(L7=0,"", MIN(IF('Trade Log'!$AF$8:$AF$5000='Trade Summary'!$H$3, IF('Trade Log'!$AE$8:$AE$5000='Trade Summary'!L45,'Trade Log'!$N$8:$N$5000))))

starts by running the following formula

IF('Trade Log'!$AF$8='Trade Summary'!$H$3, IF('Trade Log'!$AE$8='Trade Summary'!L45,'Trade Log'!$N$8))

starting with cells AF8, AE8, and N8 then does the formula using AF9, AE9, and N9 and so on until AF5000, AE5000, and N5000. It keeps all those results in an internal array (list).

Next comes the MIN function. It takes all those formula results and returns the MIN of them.

To replicate this in Numbers you will need to add a column that does the array part of the formula in your table. In particular, it needs to do it on each row from 8 to 5000 of the Trade Log table. For row 8 you would have (in Excel syntax):

=IF('Trade Log'!$AF$8='Trade Summary'!$H$3, IF('Trade Log'!$AE$8='Trade Summary'!L45,'Trade Log'!$N$8)

In Numbers syntax and correcting for the missing "else" values of the IF statements, this should be something like

=IF(Trade Log::$AF$8=Trade Summary::$H$3, IF(Trade Log::$AE$8=Trade Summary::$L$45,Trade Log::$N$8,0),0)

Copy that down all the way to row 5000. Assuming this formula will be in a column of the Trade Log table, you can leave off all the "Trade Log::" parts you see in that formula.

Let's say this is in column P of the Trade Log table. Your final formula, in the cell where the non-working formula is, would then be

=If(L7=0,"",MIN(Trade Log::P8:P5000))

That was kinda longwinded in an attempt to explain the problem and solution. Plus, the recommended formula wasn't quite right. Here is the quick version and a correction to the formula:

1. In the table named "Trade Log", create a new column. I am going to assume this will be column P.
2. In cell P8 put the formula =IF(AF8=Trade Summary::$H$3, IF(AE8=Trade Summary::$L$45,N8))
3. Complete the column by dragging or copy/pasting that formula all the way down to row 5000 so all cells P8 through P5000 have the formula. Copy/Paste will be faster.
4. In the cell where your non-working formula is, put the formula =IF(L7=0,"",MIN(Trade Log::P8:P5000))
5. If you want to, hide column P.

Originally I neglected to remove some of the absolute addressing (the $'s) and I had forced the "else" part of the IF statements to be 0. By default, when not specified in the formula the "else" result is "FALSE". FALSE may be equivalent to 0 in some formulas but not in MIN. FALSE is not considered in the MIN function so I put it back like it was.

Hopefully this all gets you the same answer as you get in Excel and I didn't make some other mistake.