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!