Try The SWITCH Function Instead of IF Statements

SWITCH Function tuturial in Excel (better than a Nested IF statement)

The SWITCH function in Excel evaluates an expression against a series of values and returns the corresponding result for the first match. It is a simpler alternative to nested IF functions, allowing you to specify an expression to test, followed by pairs of values and results, with an optional default value if no match is found. For example, =SWITCH(A1, 1, "One", 2, "Two", "Other") returns "One" if A1 equals 1, "Two" if A1 equals 2, or "Other" if no match is found. In this tutorial we compare SWITCH vs IF, SWITCH vs IFS, SWITCH vs LOOKUPS (Xlookup). We also cover how to use the SWITCH function for approximate matches, and an example of an advanced SWITCH use case.

LEARN:

▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬

Chapters:
0:00​ -​ SWITCH Function
1:45​ - SWITCH Default
2:22​ - SWITCH vs IF
4:56​ - SWITCH vs IFS
6:18​ - SWITCH vs LOOKUPS
8:04​ - SWITCH Range Match
10:24​ - Advanced SWITCH

[DavidM]

I learned way back in the Lotus123 days to never put values in formulas. Set up a small data table that you refer to instead. This will make changes or tracking down errors far simpler.

[budams]

About the first example, you could use half the table ( the results) and use direct references instead of the Manual text. Comes in very handy if you use complex and easy formulas with some standardized text value as outcome . So instead of “bonus”use $G$17, so when and if you have to change it to something else, you only have to refer to that one cell, and changing it here will change it everywhere . The big advantage over the look up is, that if you only need specific elements in some formula (like “Bonus” and “critical “, you don’t have to reference the whole range or array formula - keeps the calculations down as well….

[enzogolfetti]

Ohh that's Microsoft adding SQL logics to Excel in an amazing way. Great video!

[jerry]

For the bonus example, I would create a data table that contains three columns: country, fixed bonus, and percentage of salary. In the calculation of bonus, then you can then use a simple formula that calls the lookup function twice, once for the fixed bonus, and to this is added the salary multiplied by a lookup of the percentage of salary. No conditional functions are needed at all, it is all data driven. And if the bonus regimes and/or rates change, you only have to change the data table.

[gn_eze]

Hi Kenji, thank you for your videos. Through them, I’ve refreshed my memory on several excel function, as well as learnt new hacks.

I am a petroleum engineering graduate and would want to venture into Data Science and Machine Learning, starting with the former. So far, they are tons of bootcamps, programs, and roadmaps to becoming a data analyst but they are all tilted towards financial or business analysis - which is definitely not my intended destination. Where do I learn data science with a content that is focused on the Petroleum (or Energy) industry?

[anaballesteros-sosa]

Thanks for this video, very useful for me.

[gavin.d.m]

I wouldn't hard code this into a formula, I'd use a look up table so you can change the criteria without changing the formula.

[jerry]

Using one of the lookup functions, you can have the lookup table on a different sheet in the workbook, if you don't want users to be distracted by the table.

[ritadwi-mq]

hi, thank you so much for your video, its really help, , , GBU

[Stratelier]

The SWITCH() function also has one nice feature that I really wish IFS() also had: the ability to specify a default value in the event of no match.
To compare, with the basic IF() you can provide 2 or 3 arguments (condition, then-vaue, [else-value]) but when using IFS() you MUST provide arguments in pairs (condition-1, then-1, etc) with no support for a final "else" argument (instead, you provide a final pair of "TRUE, _else-value"_ ) -- if the number of arguments is an odd number the formula will error.

[GV-gnmj]

thanks for posting. Why do we have to use true with switch to use >=?

[food_panda]

@Kenji Explains hello bro
I have done my graduation 🎓 but I need to learn excel in particular
So, if you don't mine please create excel basic to intermediate or advanced level. That will help for the accounting and finance students ☺️🤝❤️

[chazhinkeldey]

Thanks for the tips! Can you put formulae in the part where you put the 8000? For example, can you reference the cell that has 8000 in it in case that changes? Or input ranges or something else like that?

[RobTsintas]

For the specific case of the first example, where the possible values are consecutive integers, my first thought would be CHOOSE.

[mariakatsikani]

I am using vstack and filter function ( vstack 2 filtered datasets). The data has numbers, names, dates, etc. the data, even though i press right click and format cells, does not show as numbers, dates, etc and i can not use manually filters on my columns. What can i do in order to make vstack work for me and have the right formats? I am using regular excel without power querries. Thank you❤

[chapagawa]

Never got a bonus for a high evaluation, but lots of reviews and action plans and client visits for a low one, while expecting more revenue during that time.

[ravi.sganesh]

Nice Sir, It's explained with simplified formulas👌

[tetea_rivung]

What about Greater than (<) or Less than (>) like IFS Function ?

[NitinJunnarkar]

What if there are true logic statement in expression Like TRUE and FALSE together, TRUE or FALSE together?

[davedsilva]

He is talking about the =SWITCH function