Great Video !! SWITCH is "locked" to a single value and a single comparison op "=" IFS can be a sequence of independent expressions that are checked for TRUE => any comparison operation, any formula or function that checks for TRUE ✌ (There are tricks tho to use SWITCH as IFS ..=SWITCH(TRUE, ) (Downside of IFS, we can not include an exit condition for a recursive function in IFS, will deliver #NUM error, should be good old regular IF formula)
That is very true. In that scenario, I would revert to the =SWITCH(TRUE,...) option. I didn't know about the recursive issue with IFS - that's quite a niche find. Good work.
For the sake of completeness, I would also give CHOOSE a mention. It wouldn't work with your example since it can't handle text but in some instances it's very handy. The other day I wanted a GROUPBY that will extract the month from a date and then sort them in the correct order. Since there will never be more values added, I used SWITCH.
for those of us that learned spread sheet (pre MS Office) - in Visi-Calc, we didn't have the IF function, we had the lookup function to use. So many people want to assign letter grades you know the old scale 90-100 = A. 80-89 = B..... and so on, the lookup function is perfect for this scenario. Nested IF functions are a mess and really should be avoided. too hard to troubleshoot. Great Video again.
Yes. Your lookup value can be a formula. Just make sure that the kind of values you look for are are the same type. LEFT results in a text. If the lookup table has stored them as numbers you will get an #VALUE? error message. What you would need in this case is XLOOKUP(LEFT(A,5)+0,lookup column, return column). +0 (or *1) will convert the result into a number. You could also use the VALUE function.
@@ExcelOffTheGrid Unfortunately, IFS and SWITCH calculate ALL the conditions/results whereas IF stops calculating after the first TRUE. I discovered this while trying to figure out why my recursive LAMBDA was working with IF but not with IFS.
Very good video. And very nice topic to discuss!! A lot of people use if and spend hours to do complicated nested IF fuctions, while really - there are just simple solutions. For me, im basicaly nesting one IF as maximum. And thats mostly for cells that are not part of any table. So i only need result once. In any other case, the lookop table solution is for sure more flexible and easier to do. + You can do xlookap for smaller / higher values too which is one of the greatest options there !!
The third option is smart. I guess I keep commenting about data theory on your videos, Mark, but I think a good lesson to remember is that there is no rule that says you have to accomplish everything in one table, especially not if it means you have a formula filled with magic numbers!
My gripe with IFS is that it evaluates the entire formula, even if the first argument returns TRUE - whereas nested IF statements will stop evaluating as soon as TRUE is encountered. No big deal on most spreadsheets , but can really eat up CPUs on huge sheets with complex logical formulas. My experience, IF is less CPU intensive, and quicker. Thus I've stopped using IFS :(
That's interesting - I've never had spreadsheets so big that I've noticed a significant impact. My first thought is why have you got so many IF functions, sounds like data shaping before loading into Excel might help speed things up.
Great Video !!
SWITCH is "locked" to a single value and a single comparison op "="
IFS can be a sequence of independent expressions that are checked for TRUE => any comparison operation, any formula or function that checks for TRUE ✌
(There are tricks tho to use SWITCH as IFS ..=SWITCH(TRUE, )
(Downside of IFS, we can not include an exit condition for a recursive function in IFS, will deliver #NUM error, should be good old regular IF formula)
That is very true. In that scenario, I would revert to the =SWITCH(TRUE,...) option.
I didn't know about the recursive issue with IFS - that's quite a niche find. Good work.
For the sake of completeness, I would also give CHOOSE a mention. It wouldn't work with your example since it can't handle text but in some instances it's very handy. The other day I wanted a GROUPBY that will extract the month from a date and then sort them in the correct order. Since there will never be more values added, I used SWITCH.
Yes, CHOOSE is an option where numbers are involved. But I would probably still revert to SWITCH.
SWITCH with TRUE is one my favorite combos.
Mine too. 😁
SWITCH and IFS are 2 underrated functions and sometimes I would say even unknown to many users.
Great explanation Mark.
Thanks Ivan - when IF is so will known, most people won't look for alternative solutions.
Big thanks Mark! looking forward for the next informative video.
for those of us that learned spread sheet (pre MS Office) - in Visi-Calc, we didn't have the IF function, we had the lookup function to use. So many people want to assign letter grades you know the old scale 90-100 = A. 80-89 = B..... and so on, the lookup function is perfect for this scenario. Nested IF functions are a mess and really should be avoided. too hard to troubleshoot. Great Video again.
That parentheses at the end of multiple IF looks like wide smile :))))))))
At least that is one positive.
Or it could be 1 smile, a chin and 8 double chins 😁
Im wondering if you accept paid excel solutions requests
Thanks for asking. Not at present; we've got a long course and consultancy backlog, so we're not taking anything else on at the moment.
Great Xlookup solution. Thanks.!!!😄 Why I didnt think!!, but if you have range values to select de % which recommend? (100 - 200) 10%
XLOOKUP can handle range lookups - so that’s achievable.
Can you do one for power query? Why it didn't use switch already is baffling to me, especially as it's in power bi?
Power Query is tricky as there is no SWITCH function... but there are some interesting options. Maybe I should do a video - good idea.
I would use the LOOKUP function.
LOOKUP requires the list to be in ascending order, so would make me feel a little nervous that I might make an error.
❤❤❤
Thank you! Is it possible to use LEFT function with XLookup as a Lookup Value? I want to retreive only 5 digits not a whole sentence.
Yes. Your lookup value can be a formula. Just make sure that the kind of values you look for are are the same type. LEFT results in a text. If the lookup table has stored them as numbers you will get an #VALUE? error message. What you would need in this case is XLOOKUP(LEFT(A,5)+0,lookup column, return column). +0 (or *1) will convert the result into a number. You could also use the VALUE function.
Thanks for answering. Good thought about numbers vs text - I probably would have forgotten that.
Brilliant❤，thanks Mark
Glad you enjoyed it - Thanks Kebin.
Hi Mark, thank you for sharing this information…
Talking about performance, which scenario do you recommend?
Gracias, saludos.
For performance, I don't have a bit preference either way. If it became an issue, I would try them all and see if it makes any significant difference.
@@ExcelOffTheGrid Unfortunately, IFS and SWITCH calculate ALL the conditions/results whereas IF stops calculating after the first TRUE. I discovered this while trying to figure out why my recursive LAMBDA was working with IF but not with IFS.
CHOOSE is also efficient like IF, it only calculates the item chosen
Nice video Mark!
Thanks! Glad you enjoyed it. 😁
Pls make video xlookup between ranges ...-20 to -10 ; -10 to 0; 0 to 10 ;10 to 20; > 20 like that
Very good video. And very nice topic to discuss!! A lot of people use if and spend hours to do complicated nested IF fuctions, while really - there are just simple solutions. For me, im basicaly nesting one IF as maximum. And thats mostly for cells that are not part of any table. So i only need result once. In any other case, the lookop table solution is for sure more flexible and easier to do. + You can do xlookap for smaller / higher values too which is one of the greatest options there !!
The third option is smart. I guess I keep commenting about data theory on your videos, Mark, but I think a good lesson to remember is that there is no rule that says you have to accomplish everything in one table, especially not if it means you have a formula filled with magic numbers!
Make all sense!
My gripe with IFS is that it evaluates the entire formula, even if the first argument returns TRUE - whereas nested IF statements will stop evaluating as soon as TRUE is encountered.
No big deal on most spreadsheets , but can really eat up CPUs on huge sheets with complex logical formulas.
My experience, IF is less CPU intensive, and quicker. Thus I've stopped using IFS :(
That's interesting - I've never had spreadsheets so big that I've noticed a significant impact.
My first thought is why have you got so many IF functions, sounds like data shaping before loading into Excel might help speed things up.