Copper Contributor



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! 

9 Replies

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


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


but it returns a parse error :(

@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.



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. 


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




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




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


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:




@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!