Index & Match Functions- Excel Three Way Lookup using Index & Match & Conditional Formatting
HTML-код
- Опубликовано: 11 сен 2024
- Index & Match Functions- Excel Three Way Lookup using Index & Match & Conditional Formatting
1. Learn how to use three way lookup using index & match
2. Conditional formatting & index match for three way lookup
3. Example & explanation how to apply three way lookup in excel & conditional formatting
Download the Assignment Master Practice Folder here (All Playlist Example at one place)
drive.google.c...
(Playlist Folder-Excel Lookup Functions-ThreeWayLookup.xlsx)
Direct Link-drive.google.c...
Note: In Conditional Formatting Example kindly select cell A6 instead of A5,minor selection mistake are there in video,kindly rectify it while doing practice for the same.
Website - "www.innozant.com"
Let's Connect:
Instagram ► / innozantoffi. .
Facebook ► / innozant
Twitter ► / innozant
Linkedin ► / inno. .
#threewaylookup #indexmatchinexcel#Innozant #misinterviewquestions
Respected Sir, your way of learning is really amazing. By God you are the only Hon'l teacher who are learning very deeply. Sir didn't leave any point which is raised in my mind. Sir in this world, only found some people, who have sensitive heart for others. And it is highly appreciated for the practice folder make for us for better learning. Sir amazing. Live Long Life Sir.
Thanks for your valuable appreciation .God bless you dear❤️
@@Innozant sir esme jo conditional formatting laga hai vo to jo ham lookup value me diye hai vo usase ek row niche se color kar raha hai
Sir use ek bar check kijiye
Sir mai janta hu comment ki length thodi lambi ho gyi hai but aap jo kaam kar rhe ho use sirf 1 ya 2 word likh kar (Amazing sir, Nice video) justify nhi kiya jaa skta... Mai apka playlist dekh rha tha start se to mujhe realise hua ki aap to bilkul coaching class kya tarah padha rhe ho start se aur hme ye sochne ki jarurat bhi nhi hai kaun sa video pahle padhe aur kaun sa baad me... Agar aap Noida ya delhi me rahte hoge to shayad MAATI Institute (Noorain Ansari Sir... Ansari naam se famous hain) ke bare jante hoge maine waha se 6 months ki class kari thi fee paid kar k aur aap unse better class free me provide kra rhe ho 👍👍👍 (Shayad mai pahle ek baar padh chuka hu isliye lag rha ho). But sir mai ek baat to guarantee k sath bol skta hu aap unse jyada deeply padhate ho aur bohat se new formulas aur unke use karne ka new tarika bhi batate ho. Sir aap jo kaam kar rhe ho uske liye salute hai. Aaj bhale hi aap ki video bohat jyada na chal rhi ho but jab chalega to koi apke aas paas nhi hoga..🔥🔥🔥
Omg 😃 I am feeling Soo nice to hear that . Mujhe kaafi accha laga hai yeh sunkar ki Aapki life me Meri wajah se value add hui hai Jo ki mere liye sabse badhkar hai Maine is hi liye yeh channel bhi shuru kiya tha Mai zada kuch keh hi nahi chah raha because I am speechless aapko pata nahi chal raha hoga magar mujhe aaj tak itni khushi kisi bhi comment pe nahi hui hai at the end Mai kehna chahunga thanks Meri knowledge ko interpret karke use apne tareeke se use karne ke liye mai aapko kabhi nahi bhulunga thanks all of you for your love and support 😀🙏
@@Innozant tum bahut achha kaam karta h maqsood bhai!!
@@AbhishekPandey-je5ql Ye maqsood bhai kon hen?
Nice sir
Thank you thank you thank you very much
Sir, I am really amazed by your teaching Excel in a so simple way that each and every thing is automatically grasped by even a lay man. Hats of you, Sir. Myself 57 yrs old but very much fond of Excel Learning for office work automation. Till now, I used to see RUclips videos and get the formulas or vba codes as per my requirement for office automation. I have also used so many times these formulas but never could understand the basic behind them. Your style/way of teaching in this video has really raised the curiosity of mine to learn more about basics not simply use cut and paste formulas. I can show you the solid effect of your teaching in this video is that I could explore to manage the conditional formatting of actual row highlighting by simply changing the initial value $A5 to $A6 for vlookup. Its all credit goes to your way of teaching Excel in so simple way. I salute to you, Sir.
Really means a lot to me sir..Happy learning.Thabks for being part of Innozant Family.
sach he excel ka vagban ho sir ji
Your way of teaching is owseum
Thanks Hasan
Today after watching this video i came to know someone can teach like you. I am retired teacher but due to my interest I study Excel and I have never seen this type of explanation. Thankyou very much.
Really means a lot to me.Thanks for such a valuable feedback.Recommmed you to start your learning journey from Playlist-Basic Excel
@@Innozant I already learnt basic excel. now trying to learn advance. I watch many videos but yours is the best.
Thanks for your appreciation 🙏Happy Learning
Sir your genius 😊
completed ali the three steps of conditional formating. Amazing trick. Thanks
Sir you are really great teacher for me.
Mughe jab bhi excel nai koi bhi problem aati h aapki video se bilkul clear ho jati h.
Aap ek ek baat bilakul deeply btate h.
Jo koi nhi btata h.
Very well & very nice your teaching style.
I big fan for you.
Thanks Sir. So much better
Really love you Praveen ji for such a lovely comment..Usse bhi jyada is baat ki khushi hui ki apke life me mera mehnat value add karta hai ye mere liye sabse badhke hai.
Main youtube sirf kuch tricks ya demo video banake apne video sale karne ke purpose se nahi juda hu balki koshish hai step by step complete tutorial in sequence upload karun jisse aiss student bhi jo fees afford nahi kar sakte hain wo seekh saken bht hi asani se video ke madhyam se.M trying my best ti deliver awesome content.🙏♥️🙂
I am speechless. Hats off. Watched. Tried myself. Now got it fixed in my mind. Thanks.
Thanks Shrikant,One way two way and three way lookup tips par hona chaiye aapke.❤️🙏😊
World के best teacher ho sir ji आप.... Excel में इतना पकड़ करोड़ों में किसी एक को ही होगा..... इतना तो जिसने excel बनाया है वो भी नहीं जानता होगा....God bless you... You are brilliant...I love you
Thanks for your valuable appreciation.. Really means a lot to me.❤️
No word how easily you have explained IndexMatch, no one can beat you sir.. Thank you for your support Every video is very helpful for us.
Thanks Dipti ,Keep Learning ❤️
Excellent way of teaching ,You have In-depth knowledge of concept
सर इस विडियों के लिए मे कया कमेंट करु शब्द हि नही मिल रहे सच मे यह विडियो अद्भुत अविस्मरणीय है आपने जो मेहनत की हे वह साफ साफ दिख रही है
Fantastic 👌👌
Thanks dear.
God bless You..Happy learning dear.
Sir log Aapke sath hai aur woh Aapke content ko pasand kar rehe hai kyunki unhe woh chez sikhne ko milti hai Jo woh fees dekar bhi Nahi sikte hai sir aap sach mein bahut accha kar rehe hai aapni knowledge aur experience share karke 🙏 sir god bless you ❤️🙏sir
Thanks Pawan aapke itne pyar ke liye .God bless you dear
sir, aapka video regular dekhta hu or aapke Jaisa easy way me koi nahi samjha sakta hai youtube par dehro video hai pr aapka content sach me lajabab hai aapki teaching style sach me lajabab hai ache guru ki talash thi jo aaj jaker pura huaa hai thori si dikat hai village me internet connection ka problem rahta hai pr aapka video sare dekhna hai aur mai aapko yakin dilata hu aapke video ke madham ye jarur kuch na kuch ban jayenge aur last yahi kahna sir aapke jaisa koi nahi hai i really like you sir.🙏🙏🙏
Thanks Ashish aapke valuable feedback ke liye ,God bless you .Life me bht aage jayenge ,mehnat Krte rehna aur frustration me aake quit mat Krna ,ache cheejon ko hone me time lgta hai
@@Innozant ✍...#Motivate करने ले लिए आपका धन्यबाद धैर्य ही सफलता की कुंजी है और समय बलवान होता है ये सत्य बस आपके मारगदर्शन की जरुरत है आपका फिर से बहुत - बहुत धन्यबाद सर और भगवान् से यही प्रार्थना करूँगा आपकी भी सारी इच्छा पूरी हो 📎❤❤❤🙏🙏🙏
Guruji, no one like you blessed me before. Thank you for your blessings. With your blessing I will definitely improve my skills in advanced excel and I will be become MIS Executive with your support. Thank you so much.
Offcourse you can...👍👍mehnat kriye ..time dijiye Kitna bhi time lag Jaye frustrate nhi hona hai..Offer letter dekhna hai mujhe Aapka MIS executive Ka ..Computer operator Ka tag Apne CV se apko hatana hai ye yaad rakhna
Very nice video. Dil khush ho gaya.
Thanks dear
X E Lent way of teaching. Well done and thanks.
After attempting 4times got my answer and concept of three way lookup and conditional formatting is crystal clear it's happened because of you sir 🙏 it's look like a blockbuster movies to watch again &again you are next next level teacher of excel respect from ❤️
Thanks Pawan..God bless You..Nice to see are doing. Good job
Good afternoon sir, You deserve more than one like.
Thanks
i know me pehle bhi apki tareef kr chuki hu but kya kre sir rha nhi jara 😅,sir ap sach me hr topic ko itta deeply pdhate ho ki koi kuch kmi nikal hi nhi skta.apke channel ki mene in 3 4 dino me almost 200+ videos finish krdi aur hr video me kuch new seekhne ko mila.
itta deeply to koi coaching centre vale bhi nhi pdhate.shyad youtube pe esa koi channel nhi h apke alava jisne itte sare topic itte deeply cover kiye ho
bs ek hi request h sir kbhi in videos ko private mt krna.🙏
apki ye videos hum sbke ke liye bhut jyada precious h.
last me bs itna hi kehna h ki apke hard work ka fl apko ek din jror milega sir,
thank you so much🙏and,
god bless you sir😊🙏
Waah ye Aapne bolkar Dil hi Khush Kar Diya . You really made my day. 🙂arrey tension mat lo ye to bas shuruwat hai abhi bohot saari series plan Kar rakhi hai Naye Saal ke liye just wait and watch and this comment is surely the best one yet thank you for all you support 🙏
And thank again cause I am really speechless at the moment.
Omg Ishu 200+ videos in 3-4 days.. Really shows your dedication..God bless you..Bht aage jaoge life me..my best wishes always with you.👍
@@Innozant
thank you sir for your blessings. 🙏
i always wait for your videos and series😊
👍❤️Good Night..tc.
AWESOME SIR,THERE IS NO RESPONSE OF YOUR KNOWLEGE, LOVE YOU SIR,THANKS LOT OFF
🙏❤️ Thanks dear..Happy learning
Bohot he achhe tarike se Aapne index match 3 way lookup + conditional formatting samjhaya ek hee video me. Your every video always enhance the knowledge in excel. Very good video sir ji🙏 or apna naam bhi dekhkar achha Lagta hai. Dhanyavad Sir ji.🙏
Thanks Harivansh , Koshish yahi rehta hai ki aplogon ki concept achese samjha sakun jisse apko training alagse lene ki jrurat na ho.
Aap to dedicated student hain mere..apka naam kaise bhulenge dear.❤️😊🙏
@@Innozantitna mehatva dene ke liye bohot bohot dhanyavad Sir ji apko 🙏🙏🙏
nice sir aapke or mr mike ke dono ke advance excel Fun mein enjoy krta hun ^superb ^👍🏻👍🏻👍🏻👍🏻👍🏻
Sir app bahut achhi trah se teach karte ho age bhi ase hi vido bnate rahna
Thanks dear ..Sure ❤️
Sir This is fabulous. You do thing in such a creative way that makes people become to love this application.Thank you for very clearly explanations.You're savage
Thanks Sandeep for wonderful feedback from you.Aplogon ke aise valuable feedback se hi mujhe Motivation milta hai acha deliver krne ka Jo apke life me value add kare
Wonderful... Wonderful....
so much helpful
thanks a lot sir...from Bangladesh.
Thanks dear ..God bless You..Happy learning ❤️👍
Very intuitive and Excellent video sir. 😀😀
Thanks Mira..Hope you like thesw videos dear❤️❤️
Dear Sir, all that you doing is very useful and innovative as you are introducing new concept for Excel users. Please don't take my comments as criticism. Actually when I watch you video, I open Excel Sheet and practice it and after running it I found some mistake so I tell you. Rest is O.K.
Not at all Atul ji...Keep learning ..👍❤️
Hi, First of all I would like to thank you to provide such a great knowledge about excel, conditional formatting is not properly working in three ways.
Kashif sahab..it's working fine dear , kindly check the video description for acknowledgement 👍❤️
problem है नही है क्युकी इतने अच्छे टीचर जो हमारे साथ है और एक बात इस वीडियो को आगे की और नही बार बार पीछे स्केप करके देखा है
Thanks Manish ji❤️🙏 aapke itne valuable feedback ke liye
Sir, I am very scared about excel formulas before. since 3 years from different youtube channels i am used excel formulas some how like byheart. After watching your videos explaining excel formulas with concept and examples deeply, now i am loving to understanding the formula and memorizing. =formula(what to select, next what, and(what to mention)). Really with whole hearted i love your teaching and I will not say so simple you are a teacher but I will definitely say you are my GURU.
Love you dear... Really means a lot to me..🙏❤️
Recommend you to learn step by step from basic Excel Playlist onwards then move on to Advanced Excel.
Kindly Tell me your name dear 🙂🙏
@@Innozant Guruji my name is Madhusudhan(35), Working as Computer Operator I am from Hyderabad, Telangana State.
Ok dear ...Ye Aapka designation MIS Executive phir MIS Analyst ,Data Analyst sab apke naam ke aage lgega.
Mera Guru Dakshina wahi Hoga ..aap iske liye mehnat kro aur computer operator Ka tag hatake MIS executive Ka tag lagake dikhao aap❤️👍
Thanks for taking the time to share your knowledge and expertise.
We have to learn these by continuous/constant/consistent practice by looking/watching your videos. Thank you very much. Doing a great and noble job indeed. Continue your good work with ever smiling face.
Thanks Sir for such a lovely appreciation ,Must watch today @8pm -MIS INTERVIEW CRACKER new episode
@@Innozant Sure. Eagerly waiting for your video.
@@Innozant Today whether cracker new episode is there ?? I am waiting.since 8pm
Sir video is uploaded ..Kindly watch recent video Mis interview cracker #18
Rakesh singh ji ne sahi kaha sir, aap bahut achha padhate ho.. me aapke video hamesha dekhati hu aur me meri freind ko recommend bhi karti hu , mujhe bahut kuchh naya janane ko milta hai.. me abhi Canada me hu, kai baar yeh sochati ku ki agar aap jeise log Canada me aajaye to bahot aachi life ji sakate hai.. thanks..
Thanks Shruti itne valueable aurvoyare appreciation ke liye .Mere liye sabse eham aur happiness yhi hai ki ye vidros aapke life me value add kar pa rahe hain..Aap Wahan settle ho chuke hain ya bus job ki wajahse kuch saal ke liye hain.
Koi baat nhi Ghumne hi sahi aayenge to pechaan lena aap😀mere liye yhi bht hoga..Pata chale aap pehcahane bhi nahi😀❤️🙏
Sir you are great excellent I really appreciated your way of teaching love you sir .this will definitely enhance your motivation UpTo peak level and you deserve for it.lastly tusi great ho.
Thanks Naveed
🎉 good teaching
Sir, I like your teaching method.
Thanku soo much ..your are amazing sir
Very nice concept of three way lookup function with match & conditions formation thank you sir
Thanks.Pramod..there is one mistake.. Instead of A5 change it to A6 and it works perfectly fine..❤️🙏
Thank u so much sir for this superb video ❤️❤️❤️....plz make videos on advanced macro🤩
Thanks dear...sure
Ultimate Dilip Ji, You are an Excel Wizard..
Thanks Nishant Ji
Hi I always seen your useful videos and really like them and regards your efforts. Just a suggestion that for conditional formatting the best possible way to determine the formula is to use in the sheet itself and such complexed C formating it help to combined them in the sheet and easily copy paste
Thanks Naveed for your valuable appreciation, Yes Naveed You are ✅, For students for learning purpose it is a good way to apply formula on sheet the paste it into determine formula for conditional formatting
You are great sir
Sir thank you for the most important video. I did lot of errors, I practiced again by watching video again and understood the entire concept.
Thanks ..God Bless you dear..
Amazing Information thanks Dear 1 millions honorary likes
Surprised why too less views ??
Even i don't know..😀Lagta hai viewer kp shorts video..Magic video aur tricks video hi jyada attract krta hai..
but wo main kbui shorts video na banana sirf views paane ke liye na bnaunga ..
Thanks for showing your love and trust ..God bless you.
Amazing Sir!
A big Thank you, Sir.
Thanks dear
Aapke samjhane ke tarike unique hote hain sir.
Thanks
Great video and explanation 👍
Superb.. Thank you...
कभी कभी सिर घूम जाता है विडियो देख कर
धन्यवाद गुरु जी
Soon dear
So Nice Sir. Thank you. God bless you.
Thanks Binay Ji.
very excellent sir
Sir u r ultimate great..
Thanks Rishi❤️🙏
Really like this concept.
Thanks Hemant.Hope you like three way lookup concept and learn applying conditional formatting too.
@@Innozant yes sir, condition formatting was great.
Sir like your way of teaching 💯
❤️🙏
Hello sir Maine aapka video kabhi bhi skip kar ke nahi dekhta hu aur in future skip karunga bhi nahi kyunki mujhe poora concept sikhna hai.Thanku sir good night
Thanks Satish.❤️🙏 Happy Learning and Keep Supporting.
Seriously I have no words, god bless u sir...
Thanks Bhoopi..One way two way aur three way teeno lookup ki practice bht achese karna hai aapko..ok.kahin bahar nhi honge interview me lookup round me.
@@Innozant u are great sir...
Oh oh superb excellent
Thanks Jai..Hope you learn from. this
Good example
Sir yaar 🙏🙏🙏🙏🙏🙏💐💐💐💐Thnq thnq so much
Excellent video
Thanks Mahaboob.Row highlight me bus A5 ki jagah A6 le lena.ek row niche highlight ho gya tha.. example me.
Thanks a lot sir for sharing such a wonderful & informative videos. i like the way you teaching and it is very easy to understand each topic. I request to you please make a series on VBA and Power BI also. Again thanks a lot!!!
Thanks Saurabh Ji .God Bless You dear
Thank you sir
Respected Sir.. The reference you gave for particular Row wasn't highlighted it's a different row highlighted I think you haven't selected a complete table. reply
Very good sir ji
Sir isme heading k liye agar columns merge kar rakha ho to koi fark padta hei kya conditional formating m....
Sir, thank you very much for your teaching
Sir, I have a dought. Why do we need to use lookup in this situation of condition formating? Instead, we can directly use the cell reference
Because lookup will also give the same result....
wow sir amazing thank you
Thanks dear
GENIOUS!!!❤❤❤
Thanks dear Suraj
what a great way to teach
Thanks Prem ji
Hi,
One query I have when you select Ashu Sharma the line highlight below of it instead of the selected value.i.e. Ashu Sharma for respective month
Very very good Sir
Thanks Atul Ji.. Happy Learning Sir🙏
Sir One problem in your vedio and when i practice this formula same problem!. sir jab aap vlookup ka formula laga kar colour sat kar k enter kya tu jo value aap ny select ki thi us say neechay wali row hihglight hio is vedio mein bhi yahi problem hian or jab mein ny practice kya tu yahi problem aya ........reply please.........
Hi, I am a big fan of your videos. I have learned a lot from your videos atually i have learned xcel in a proper way.
I have a problen in my work, can you help me?
My problem is that, I have to find the third value based on two values, which are in a drop down means its a dynamic values, which excel shall i use? Plesae help...
Thank u sir ❤❤❤❤
❤️🙏thanks
"Sir, when using the second VLOOKUP within the AND condition, we're encountering an issue where the row below the intended row is being selected. For example, when selecting 'Ashu Sharma,' 'Jaishree Sharma' is being selected instead. Why is this happening?
Sir isme jo ashu sharma kii value aapne nikali h.. Theek h lekin row or column jaishri saxena par highlight ku ho raha h.. Ashu sharma par hona chaiye na..
SUPERB
Nice 👌
Watch Full video..aur seekho saath ke saath practice..
@@Innozant ok sir
Excellent sir 👌😊
Nice video
Thanks.dear❤️🙏
Thanks Sir.
Thanks Tarun ji
Sir, Dil se thank you. Ab Index aur match ek Cricket match ki trh lag rha hài, Jo mera sir dard tha.
Haha... Thanks for such a lovely comment.. Seekhiye aur aage badhiye life me
A. O. A
Janab , excel 2007 mai weeknum formula ka return_type 1 aur 2 hai (11, 12,13 ect) naihai tu yeh kaisay possible hai please ans. Thanks
Hello Dilipji, In the conditional formatting, the condition with vlookup selects next row rather than the row itself. This may please be corrected.
Dear Manish Ji..kindly check video description.. Already acknowledgement is there to rectify the formula as i select wrong cell while applying formula dear🙏🙏🙏❤️❤️❤️
Usko theeo krke apply kar leij eaise maine practice sheet me sahi kar diya hai alredy
@@Innozant Noted with regards.
Very amazing urdu lang videos
Thanks dear❤️🙏
Fantastic sir ji
Good Morning. Thanks ❤️ dear
Hello sir , 🤩❤
Great video i often watch and take help from your valuable videos , Thanks for making such nice videos, for us it really helps us in a positive way
Just a quick question sir we often being told to create these kind of dashboards like you created here 3 way , 2 way, but sir not on the same excel sheet and definitely not on top of the data sir, please make a video where the present data is not disturbed may be some where in the corner or in the next excel sheet . please guide me😊
Sure will demonstrate in video or going to make video for the same
Sir, 🙏🙏
Mera ek question hai ki ek workbook me adhiktam kitni sheet le sakte hai aur koi shortcut hai esko check karne k liye.
Please reply dejiyega sir.
Sir video to bohot acha laga but ek question tha lookup value ashu sharma ka course aapne MISL5 liya to MISL6 highlight kyu ho rha h i have doubt this 🙄
Sujata Maine description me likh diya hai wo Maine A6 ke jagah A5 select Kar liya tha isliye niche wala cell heighlight ho raha hai😄
Aab bus conditional formatting me jake A5 ko A6 Kar do bus.Threewaylookup assignment me Maine sahi kar diya hai wo download krke practice kijiye.
Happy Learning ❤️😊🙏
@@Innozant ohk sir thank you🙂
Sir,I have seen today..this video...sir,yhan vlookuo mein B6 lookup value lenge to result shi aa rha hai..otherwise ak row niche pahunch ja ra hai
Same problem agar out put data alag workbook or months alag workbook p hoga to tab kaise kare sir..
Very nice sir
Thanks Sahu ji
Sir mujhe ye bta do ki esme value 1 bad wali kyun show kr rhi hai conditional formatting krne ke bad.jaise A ki bajaye woh B pe conditional formatting dikha rha hai.plz answer de dijiye
EVEN SUMPRODUCT CAN WORK WELL HERE, PROVIDED EACH AND EVERY RECORD IS UNIQUE. COMBO OF XLOOKUP AND FILTER WILL ALSO DO THE TRICK.
Nicely explained, thank you Sir.
I need an extra solve, please help.
Col A (Name), Col B (Gender)[M/F] Col C (Father's Name)
Need a list through data validation at Col C, data comes from Col A depending upon the Col B, where it considers male only. Indexed on Name.
Please help. 🙏
Kindly share query @innozantofficial telegram id
Excellent
Thanks Balwan❤️
Very good
Thanks Raj
Nice sir