IF AND

[WishIWerentAN00b]

=AM3*(IFS(AND(AJ3="BASELINE";AB3="mini");Inf_Sales_Dashboard!$I$219);(AND(AJ3="BASELINE";AB3="micro");Inf_Sales_Dashboard!$I$220);(AND(AJ3="BASELINE";AB3="mid-tier");Inf_Sales_Dashboard!$I$221);(AND(AJ3="BASELINE";AB3="medium");Inf_Sales_Dashboard!$I$222);(AND(AJ3="BASELINE";AB3="big");Inf_Sales_Dashboard!$I$223);(AND(AJ3="BASELINE";AB3="mega");Inf_Sales_Dashboard!$I$224);(AND(AJ3="TOP";AB3="mini");Inf_Sales_Dashboard!$I$228);(AND(AJ3="TOP";AB3="micro");Inf_Sales_Dashboard!$I$229);(AND(AJ3="TOP";AB3="mid-tier");Inf_Sales_Dashboard!$I$230);(AND(AJ3="TOP";AB3="medium");Inf_Sales_Dashboard!$I$231);(AND(AJ3="TOP";AB3="BIG");Inf_Sales_Dashboard!$I$232);(AND(AJ3="Top";AB3="mega");Inf_Sales_Dashboard!$I$233))

I am trying to say: if (for example) the cell AJ3 is "top" and the cell A3 is "mega" then return this cell, and then multiply it by AM3. What would be the correct syntax? 

(the ; is because my settings are european, it is a , for other settings)

Thanks for the help! 

[#2]

@WishIWerentAN00b Many of the brackets should be removed. A working formula could look like this:

=AM3*(IFS(
AND(AJ3="BASELINE",AB3="mini"),$I$219,
AND(AJ3="BASELINE",AB3="micro"),$I$220,
AND(AJ3="BASELINE",AB3="mid-tier"),$I$221,
AND(AJ3="BASELINE",AB3="medium"),$I$222,
AND(AJ3="BASELINE",AB3="big"),$I$223,
AND(AJ3="BASELINE",AB3="mega"),$I$224,
AND(AJ3="TOP",AB3="mini"),$I$228,
AND(AJ3="TOP",AB3="micro"),$I$229,
AND(AJ3="TOP",AB3="mid-tier"),$I$230,
AND(AJ3="TOP",AB3="medium"),$I$231,
AND(AJ3="TOP",AB3="BIG"),$I$232,
AND(AJ3="TOP",AB3="mega"),$I$233
))

[#3]

@Riny_van_Eekelen thanks so much!
This is the formula I used now:

=AM3*(IFS(AND(AJ3="BASELINE";AB3="mini");Inf_Sales_Dashboard!B219;(AJ3="BASELINE";AB3="micro");Inf_Sales_Dashboard!B220;AND(AJ3="BASELINE";AB3="mid-tier");Inf_Sales_Dashboard!B221;AND(AJ3="BASELINE";AB3="medium");Inf_Sales_Dashboard!B222;AND(AJ3="BASELINE";AB3="big");Inf_Sales_Dashboard!B223;AND(AJ3="BASELINE";AB3="mega");Inf_Sales_Dashboard!B224;AND(AJ3="top";AB3="mini");Inf_Sales_Dashboard!B228;AND(AJ3="top";AB3="micro");Inf_Sales_Dashboard!B229;AND(AJ3="top";AB3="mid-tier");Inf_Sales_Dashboard!B230;AND(AJ3="top";AB3="medium");Inf_Sales_Dashboard!B231;AND(AJ3="top";AB3="big");Inf_Sales_Dashboard!B232;AND(AJ3="top";AB3="mega");Inf_Sales_Dashboard!B233))

but it returns a parse error :(

[#4]

@WishIWerentAN00b Can't really tell. The formula seems rather complicated for something rater simple. What's a "parse error"?

Can you share the file (OneDrive, Dropbox or the likes)?

By the way, I must apologize for the fact that I didn't mention that I removed all the sheet references and swapped the semi-colons for commas. I couldn't "see" a working formula on my system otherwise.

[#5]

@WishIWerentAN00b 

=VLOOKUP(AJ3&AB3,CHOOSE({1,2},$A$3:$A$14&$B$3:$B$14,$C$3:$C$14),2,0)*AM3

You can try this formula with a reference table which is in range A3:C14 in this example. Enter the formula with ctrl+shift+enter if you don't work with Office365 or 2021. 

=Inf_Sales_Dashboard!I219

This formula is entered in cell C3 and copied down to cell C14.

 

[#6]

Hi@WishIWerentAN00b,

another possibility would be with OFFSET() and SWITCH()::

=AM3*OFFSET(Inf_Sales_Dashboard!I219;SWITCH(AJ3;"BASELINE";0;"TOP";9;NA())+SWITCH(AB3;"mini";0;"micro";1;"mid-tier";2;"medium";3;"big";4;"mega";5;NA());0)

Attention. You have asked in your original formula once "TOP" and once "top". In addition "BIG" and "big"! This is not the same!

[#7]

@WishIWerentAN00b 

There's a few ways to do this.  Any time the logic gets beyond 3-4 If-then scenarios, I prefer to use a lookup formula.  I'm aware of IFS and know how to use it but prefer a simpler approach.

Create a dedicated lookup table and use VLOOKUP or XLOOKUP and you'll have a short formula like this:

=XLOOKUP(F3:F6&G3:G6,A2:A13&B2:B13,C2:C13)

[#8]

@dscheikey thanks! could you please explain to me what is going on in the formula? :)

[#9]

Yes, this is actually very simple. The SWITCH() function is similar to a VLOOKUP() only that the search terms and the result are included in the formula. It looks for the tag in AJ3. If it is "BASELINE" 0 is returned. If it is "TOP" a 9 is returned. The second SWITCH() function looks for the entry in AB3. If it is "mini" a 0 is returned, if it is "micro" a 1 is returned and so on. Both SWITCH() numbers are added together. In OFFSET() the cell of Inf_Sales_Dashboard!I219 is moved down by the result of both SWITCH()'s. Now you are at the entry that is to be multiplied by AM3.

[#10]

@dscheikey wow, that is beautifully simple. It worked, thanks so much!