Syntax error using a VLOOKUP formula from Excel in Numbers

LIBeaky wrote:

confirming =SUMIFS(Rates::D;Rates::B;B2;Rates::C;C2)*D2 works

Glad to hear it works. In the pharmaceutical world I suppose this might be called an "off label" use.😀

But I think it will be reliable for your purposes.

SG

Since a particular Level-Country pair will occur only once you can use SUMIFS, like this:

=SUMIFS(Rates::D;Rates::B;B2;Rates::C;C2)*D2

AVERAGEIFS or MAXIFS or MINIFS would also work.

If you don't usually have your region set to English note that in Numbers you can go to File > Advanced > Language and Region... and change the region and language to input the formula and then switch back to the language you prefer to use in Numbers. That way you can see that In German the formula would be:

=SUMMEWENNS(Rates::D;Rates::B;B2;Rates::C;C2)*D2

etc.

SG

Not sure exactly what that formula is doing. My guess is part of the problem is with CHOOSE({1/2} and that it's a form of "array" formula that Numbers can't support.

There are usually workarounds in Numbers, often more understandable than the original.

If you post a screenshot and show what it is doing then someone here can make suggestions.

Note, BTW, that VLOOKUP is old. Use XLOOKUP. It's easier, and more flexible.

Also note that in Numbers you typically wouldn't use something like $C$5:$C$219. You would use $C, referring to the entire column rather than a range within it.

SG