Forum Discussion
IF AND
=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(...
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!
- dscheikey thanks! could you please explain to me what is going on in the formula? 🙂
- 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.
- dscheikey wow, that is beautifully simple. It worked, thanks so much!
