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(...
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!