5 Advanced Tips on how to use IF formula in Excel that will SURPRISE you 😲

Рет қаралды 171,484

Күн бұрын

Excel's IF() is one of the most used and fundamental functions. In this video, learn how to use IF function and 5 advanced tips.
We are going to learn ✔ IF formula syntax ✔ Nested IF formulas ✔ IFS() function to replace nested if formulas ✔ AND conditions in IF formulas ✔ Choose function ✔ Index function ✔ Using IF with lookups
For more advanced tips on IF, see this video - • 10 Advanced IF formula...
📑Video topics⏱
===============
0:00 - IF formula syntax and simple example
1:05 - Calculating GRADE using nested IF function
2:40 - Using IFS() function
3:46 - Checking for two or more conditions
4:45 - CHOOSE function
6:34 - BONUS trick - Using INDEX instead of CHOOSE
7:39 - Using IF with Lookups to switch between two tables
9:02 - Learning more about IF function
📗 Download Sample File & Practice 👇
===============================
Visit chandoo.org/wp/wp-content/upl... to download the example file for this video.
📺 WATCH NEXT😎
=================
If you want to learn more about Excel functions, do check out below useful videos.
1) IF formula with 10 advanced tips - • 10 Advanced IF formula...
2) SUMIFS formula with 10 advanced tips - • 10 *next level* SUMIFS...
3) Top 10 Excel functions for Data analysts - • Learn these top 10 Exc...
4) 15 Essential formulas for everyone using Excel - • These are the ONLY 15 ...
🤩 Excel formulas for Data Analysis - PLAYLIST - • 10 Advanced IF formula...
Learn EXCEL step by step 💻
=======================
If you want to learn all the essential bits of Excel in one handy place, please consider my Excel School program. This top-rated Excel course has been the secret of more than 20,000 people all over the world for their professional success.
Visit chandoo.org/wp/excel-school-p... for enrolment.
~
Have a beautiful day 🌼🌞😀
#if #excel

Пікірлер: 166

@Seftehandle Жыл бұрын

Perfect length and perfect tips. Exactly as per my taste. I dont use if alot since xlookup, however learnt a lot rearding choose and weekday! Thanks

@chandoo_ Жыл бұрын

You are welcome Tine 😀

@lealeveriza4497 Жыл бұрын

👏👏👏 I'm so happy I found your channel! You're a good teacher.👍

@AmanaButt Жыл бұрын

very well explained with good examples. Worth watching. Thanks

@peterjones6733 Жыл бұрын

Never realised about the black end bracket! Thank you!!

@Golfina19 Жыл бұрын

Great video. Thanks for all the tips.

@JackKing12. Жыл бұрын

Excellent....good to see your pivot table advice.

@legacygk2811 Жыл бұрын

Amazing stuff! Thank you so much.

@arunkumar-ue1il Жыл бұрын

As always you are awesome & make concepts simplified, Thank you!

@chandoo_ Жыл бұрын

Glad you like them!

@GurayVural-nz1lk Жыл бұрын

Very clear, excellent content!

@dennisvilcatomalopez2245 Жыл бұрын

Gracias por compartir los tips. Eres un grande 💯

@jaythakkar5660 Жыл бұрын

Awesome information and tips. Thank you

@ronebhowmick Жыл бұрын

Thanks for your teaching method

@selormselorm8535 Жыл бұрын

I love you clips. Thank you sir!

@yemiakinwande7039 Жыл бұрын

very helpful video - ifs and choose is new and useful for me. many thanks

@wrognOne 3 ай бұрын

Hey Chanddoo. you dont figure in those lists ....but you are a Super Hero of Excel. When they build that wall of fame ........ youll be there

@nasirmuhammad3901 Жыл бұрын

Your method of teaching is simpler. Well Done!

@shriidharkulkarni6648 Жыл бұрын

You are really awesome teacher of Excel.

@shiffamohammed5818 Жыл бұрын

Thanks a lot Chandoo for sharing such an amazing video. Another versatile use of if function!

@chandoo_ Жыл бұрын

Most welcome!

@salehmuhammad6811 Жыл бұрын

👍 great Chandoo. Knowing function is not important but knowing where to use is important and you taught us the second way. Thanks again big brother.

@chandoo_ Жыл бұрын

My pleasure Saleh.

@PrashantKg1996 Жыл бұрын

There is always something new to learn here. I didn't know that there is something called 'Ifs' function and I always used the nested if. Now I can use this new formula

@mkajiwala1 4 ай бұрын

Superb sir...... Very good examples, and brief too, thanks

@chandoo_ 4 ай бұрын

You are most welcome

@vijayarjunwadkar Жыл бұрын

Great video Chandoo! I learnt a few new tricks to use with IF and thanks a lot for that! 😊👍

@chandoo_ Жыл бұрын

Great to hear that Vijay.

@saaneroa5024 2 ай бұрын

Awesome video Chandoo

@MM-qk4if Жыл бұрын

Excellent👍

@mahmoudmohsen8105 Жыл бұрын

cool that index/choose tip and the xlookup thanks alot

@chandoo_ Жыл бұрын

You are welcome Mahmoud.

@srmchinni3 Жыл бұрын

Thank you, Chandu. That's brilliant.

@chandoo_ Жыл бұрын

You are welcome Sairam.

@anuradhabadade7239 Жыл бұрын

Thank you Sir...

@MAH8897 Жыл бұрын

Hi Chandoo, I enhance my knowledge. "THANK YOU" Regards

@chandoo_ Жыл бұрын

You are welcome Mohammed. Please share what you are learning with others and help colleagues in need.

@MAH8897 Жыл бұрын

@@chandoo_ ofcourse I do.

@raziajeena9861 Жыл бұрын

V well explained thanks

@dvictorjayaseelan8889 Жыл бұрын

You make learning so easy and impressive...

@chandoo_ Жыл бұрын

Thank you!

@chrism9037 Жыл бұрын

Chandoo, another outstanding video, thanks!

@chandoo_ Жыл бұрын

Glad you enjoyed it Chris.

@vijayasakthivelvigneshwari7513 Жыл бұрын

Thanks chandoo you made my day

@bell407mech Жыл бұрын

Man! You are still the best!!!

@arbazahmad7177 Жыл бұрын

Thanks 😊 for this great using tip of "if not found" option in Xlookup function.

@chandoo_ Жыл бұрын

Glad it was helpful!

@aiasaiascon3894 Жыл бұрын

Very nice to have this CHOOSE!!! Thank you Chandoo!!!!

@chandoo_ Жыл бұрын

Glad you like it!

@boboshanu7325 Жыл бұрын

Bell notification on only for you brother ✌️

@Rattuu Жыл бұрын

Awesome, thanks a lots.. You know Chandoo, I learnt power query from you and I also learnt Power BI. Now I am looking the job as Power BI developer to be a Data Analyst. The excel tips are very helpful to me and thanks for it. Could you please make videos about DAX? That how to use DAX functions in Power BI to next level? Thanks again to make me a good data analyst.😊😊😊

@surajbhardwaj428 Жыл бұрын

IFS & two Xlookup function liked most... 👌, Great work sir,,... Both function will help me sort out daily work in office....🙏🙏

@chandoo_ Жыл бұрын

Awesome to hear that Suraj.

@manikandanb9914 Жыл бұрын

Thank you sir

@chandoo_ Жыл бұрын

You are welcome Manikandan.

@Munichandra_Reddy Жыл бұрын

Hi Chandoo, You have explained inside x lookup how to use two Datasets, it is amazing thing🙏🙏 I want to Your help, in my current role, I am facing lack of store produce issue, please tell me, where will I learn more Advanced Store produce ? Swift response

@manissery1956 Жыл бұрын

Thanks a lot dear. In fact I just posted my comment under a video of another excel guru as to how can we do it. Really it's wonderful. Watching all yr presentations. Need a video from you on LET and Lambda.

@chandoo_ Жыл бұрын

I have a video on LAMBDA here. studio.kzfaq.infoYrMCkE59ef4/edit Check it out

@leehueyyun630 Жыл бұрын

You are awesome.

@radheshyamthakur Жыл бұрын

Good concept especially the last one.

@chandoo_ Жыл бұрын

You are welcome Radhe.

@maheshthorat7568 Жыл бұрын

You are a owesome human being.🥳

@nithin51988 Жыл бұрын

You are really awesome chandu...,short and crispy videos makes easy to understand...

@chandoo_ Жыл бұрын

Thank you so much 🙂

@digitalinfoMRU Жыл бұрын

Thanks 🙂 It helps a lot..

@chandoo_ Жыл бұрын

You are welcome DI.

@frenan82 Жыл бұрын

Thanks!

@chandoo_ Жыл бұрын

You are welcome :)

@kazimuhammadyakubali340 Жыл бұрын

tnx a lot

@tanmaypalkar9861 Жыл бұрын

Thank you Chandoo :)

@alwayspositive999 Жыл бұрын

Nice vid- for the batmobile example- how does the chart on the left- RELATE to the chart on the right- where is the connection to the" weekday/ person" chart in the formula?

@arvindr2101 Жыл бұрын

Good afternoon sir, Thankful for the important video

@chandoo_ Жыл бұрын

Good day Aravind and you are welcome :)

@bhoopendraraghav9781 Жыл бұрын

Very Nice

@taizoondean689 Жыл бұрын

Thank you sir 🙏

@chandoo_ Жыл бұрын

You are welcome TD.

@AP-eb8hd Жыл бұрын

IFS was a take away !! thanks..

@chandoo_ Жыл бұрын

😎

@vullikantisaidheeraj7634 Жыл бұрын

Thanks chandoo sir Instead of writing a long if function for grading we can also use vlookup by keeping it as a approximately match.

@chandoo_ Жыл бұрын

We can. As the topic is about IF, I stayed with IF based examples.

@yuthakrainarong265 Жыл бұрын

Sir, you had my bow.

@TheHanuwant 9 ай бұрын

HI, great tips. I have question related to using if formula when #N/A is also a condition and I have many #N/A to see. How can I use it?

@karansuroshe6236 Жыл бұрын

Thanks sir 🙏

@chandoo_ Жыл бұрын

You are welcome Karan.

@alializadeh8195 Жыл бұрын

Thanx

@SKMRify Жыл бұрын

Good video.

@chandoo_ Жыл бұрын

Glad you enjoyed it

@RMKserve Жыл бұрын

Haaa super tips❤️😘😘❤️👍

@Saikumar-yp1lx Жыл бұрын

Your videos are makes me excel pro

@chandoo_ Жыл бұрын

YAY!!!

@SumiaA Жыл бұрын

I don't have the xlookup function is there an alternative function to use?

@basicinfoforall7306 Жыл бұрын

I'm trying to learn data science from your content.

@hobsondrake Жыл бұрын

Chandoo, the excel Super Hero.

@chandoo_ Жыл бұрын

🦸‍♂️ We all are...

@Explorer_Nur Жыл бұрын

Thank you for this video. Pls I need some help. I can't use F4 to change my references to absolute. I'm using a windows computer pls someone should help me

@canirmalchoudhary8173 Жыл бұрын

If not found can be good alternative for second version of INDEX.

@7fink7 Жыл бұрын

can you share,please, an example of how formula can result showing "months" number value when A1 - is a date of start and B1 - is today's date....thank you! 🙃👍

@ankitBhartiya2 Жыл бұрын

As always you are nice. Please make video on Microsoft Office Specialist: Associate" Certifications MO-200, MO-201. Are they really worth ?

@chandoo_ Жыл бұрын

Thanks Ankit. I will share my views on certifications in an upcoming video.

@pankajpandey4485 Жыл бұрын

Basic to intermediate PowerPoint lesson or series. Please Sir

@syedsyed777 Жыл бұрын

Hi sir, Can I know which video editing software u r using ? Will be a great help!

@chandoo_ Жыл бұрын

I use Camtasia for creating and editing my videos. You can get a free trial from here - techsmith.pxf.io/c/3169105/481530/5161

@syedsyed777 Жыл бұрын

@@chandoo_ I would like to thank you for taking out your time and replying me. Thank you so much...

@probashibangla6708 8 ай бұрын

❤❤❤

@Abhi2533 Жыл бұрын

🔥🔥🔥

@chandoo_ Жыл бұрын

😎

@shriram8088 Жыл бұрын

Hey chandoo Thanks for the video. Chandoo, i want to learn VBA codeing. Do you have paid course for that.

@chandoo_ Жыл бұрын

Yes I do. Please see chandoo.org/wp/vba-classes/

@vickyat4675 Жыл бұрын

Salaam chandoo Bhai,,,

@technicalwords8214 Жыл бұрын

If formula se nikli gayi value ka sum kr sakte ya nhi please guide sir

@LearnAllways Жыл бұрын

Hi. I have a students' table with Mark. What I want. I need to check the top 2 people based on the particular subject. Can you do some videos for this?

@bibekkoirala5294 Жыл бұрын

forward me if can help you

@ericgroen253 10 ай бұрын

interesting! But in your examples you typed , in stead of ; That might be a version error? I only got it to work with the ; 🙂

@chandoo_ 10 ай бұрын

Thank you. In European version of Excel ; is used and almost everywhere else , is used

@manissery1956 Жыл бұрын

One question which is not related to IF function. I have a big database comprising several columns and rows. From this I can find the highest value using MAX function. But how can I find out the cell address of that cell containing the highest value. Please help.

@chandoo_ Жыл бұрын

You can then use XMATCH or MATCH to find the row number of the highest value. If you use ADDRESS function, you can generate the address of the cell.

@Metzanine Жыл бұрын

Nested Xlookups? 🤯🥳

@supriyaagrawal432 Жыл бұрын

Great I saw many videos but everyone inform basic tips only

@chandoo_ Жыл бұрын

You are welcome Supriya.

@ruchikachauhan9697 Жыл бұрын

Your content is very helpful . Would love to know if you have a patreon account?

@MrDushyant123 Жыл бұрын

How to use for value and max values xlookup

@ArifKhan-qo9jw Жыл бұрын

Xlookup has covered mostly formulas so everyone should learn xlookup 😊

@sergiigolovynskyi6963 Жыл бұрын

👍

@teoxengineer Жыл бұрын

Chandoo hi, If I don’t use office 365 and searching a value from different tables, how can I find this correct value. (Xlookup example)

@chandoo_ Жыл бұрын

You can use =IFERROR(VLOOKUP(table1), VLOOKUP(table2)) approach.

@rashadagayev3108 Жыл бұрын

Switch function also can you

@bibekkoirala5294 Жыл бұрын

its a nice tips and by the way what is the name of that song, Chandoo?

@chandoo_ Жыл бұрын

It is "If you have got time by Merrie". www.epidemicsound.com/track/madn3HgYvn/

@bibekkoirala5294 Жыл бұрын

@@chandoo_ Thank you! ☺

@ShivaSingh-vs4yv Жыл бұрын

Why not use text function + vlookup to find who will drive batmobile 🤷‍♂ Text function to convert date into text then find it in the table using vlookup. Ps :- love you learning a lot from your channel since last 1 year. 😍

@chandoo_ Жыл бұрын

Great suggestion! You can use that too. As I wanted to showcase IF, IFS & CHOOSE, I went with the CHOOSE example first, but the bonus trick shows a better way of doing it too. PS: Thanks for learning and becoming awesome 😎

@iqbalsingh6311 Жыл бұрын

Instead of Choose, text function with Xlookup is better alternative

@JEMS-DILAN Жыл бұрын

sir, what will happen if sunday is holiday for all driver?

@chandoo_ Жыл бұрын

You can change the formula or mapping table in that case :)

@sahithikoluguri9539 Ай бұрын

I need this video in Brother

@Green__one Жыл бұрын

The one I keep finding myself needing is a way to solve the following: if(_BIG_LONG_FORMULA_,_BIG_LONG_FORMULA_,_SOMETHING_ELSE_) When the big long formula is the same in both places, what I really want is not to have the big long formula typed out twice because it makes the whole thing almost unreadable (I'm talking formulas with multiple nested Ifs and lookups). I feel like there has to be a better way, I just can't seem to find it.

@chandoo_ Жыл бұрын

You can use LET(). For example =LET(x, BIG_LONG_FORMULA, IF(x, x, something_else)) Also, there are some clever ways to avoid IF. I suggest reading www.excelhero.com/blog/2010/01/i-heart-if.html

@Green__one Жыл бұрын

@@chandoo_ I think "LET" may be exactly what I'm looking for, thanks! I'll give you an example of the type of thing I'm trying to model, I have a spreadsheet for keeping track of payroll, but the union contract is insanely complex for the rules to figure it all out, for example, there are shift premiums for working between 1500 and 2300, a higher premium for between 2300 and 0700, and another premium that stacks on top of those if it's between 1500 on a friday and 0700 on a Monday. Additionally all those premiums only apply if more than 2 consecutive hours are worked in the appropriate timeframe, but if more than half of a shift is in a specific timeframe, all of the shift is counted at that rate, unless a higher rate is hit. But if the employee is in overtime all premiums kick in immediately instead of only if more than 2 hours are worked. And let's not even get started on stat-holidays, and super-stat-holidays! You can imagine that the formula ends up being super complex to capture all of that!

@chandoo_ Жыл бұрын

I would probably break the formula to multiple cells and use assumption / input cells so that the final rate calc is a shorter formula.

@Green__one Жыл бұрын

@@chandoo_ That's what I do, but even so, there's such a dependency on each other cell that it can be hard. Basically I calculate total hours, overtime hours, evening hours, night hours, and weekend hours, then total dollar value.

@statesticsandanalyses6348 Жыл бұрын

In Which version Choose is available ???

@chandoo_ Жыл бұрын

It has been around for a long time. The help article says since 2007, but It might be there in 2003 too.

@iqbalsingh6311 Жыл бұрын

X lookup or Vlookup with approximate match is Better than nested if

@vishnu8899 Жыл бұрын

Sir just a small request,please attach a finished and unfinished file so that we can follow along and do it as well as pause the video and try again

@chandoo_ Жыл бұрын

Sorry Vishnu. I won't be doing that. I already provide full example files and you can easily remove the formulas if you want to follow along.

@vishnu8899 Жыл бұрын

@@chandoo_ Yes sir been doing that all along…thank you for the videos 👍.What I meant to ask was I try solving your file before looking up the video or a snippet of the video…So that I can gauge my knowledge so I don’t view the code written..but as you said it’s easy to just delete and start.😊

@mehboob876 Жыл бұрын

I have an excel list of approximately 1 million part numbers. I need an excel pro to change part numbers of any duplicates on the list. For example if there are duplicate part numbers: 1234 1234 1234 I need this changed by adding "A" "B" etc for example : 1234 - A 1234 - B 1234 - C etc 1234 - AA 1234 - AB etc. The new part number should be listed on the next column. How to do...

@oliverscheurich9202 Жыл бұрын

=CONCATENATE(A1,"-",VLOOKUP(COUNTIF($A$1:A1,A1),$F$1:$G$600,2,FALSE)) Enter this formula in cell B1 and copy down. The part numbers are in column A. =SUBSTITUTE(ADDRESS(1,F1,4),"1","") Enter this formula in cell G1 and copy down to G600. In range F1:F600 are numbers 1, 2, 3, 4 and so on.

@shaikjeelani6179 Жыл бұрын

Do "and" ,"or" multiple conditions in single formula of if

@chandoo_ Жыл бұрын

Please see the video I mention at the end. It covers those topics - kzfaq.info/get/bejne/Y992o916t8_Ke2g.html

@technophile01 Жыл бұрын

hello. I made a table on excel Android with 30 boxes corresponding to the days of the month. each day I enter my expenses. I would like excel to automatically calculate for me in a box the average of the previous entries thank you

@chandoo_ Жыл бұрын

You can use AVERAGEIFS() and exclude future dates.

@technophile01 Жыл бұрын

@@chandoo_ Hello. I am not a specialist and I do not know how to do it concretely, you would be very kind to write me the formula if you wanted. thank you so much. I remind you that I would like the average to be calculated each day, for example on the 4th day that of 4 values the 5th that of 5 values etc.. until the 30th day.