Advanced Lookup Multiple criteria using Index Match with Array concept - Tamil | Prabas MS Office
Вставка
- Опубліковано 13 гру 2024
- How to use Index & Match Excel Function (Complex Lookup) - Tamil
Yes, Vlookup and Hlookup can help us to get data from another worksheet or even from another excel file. But the thing is they can only deal with one criteria and they have limitation in their function. So here we are going to see a beautiful excel function that you need to understand and learn. Yes, this can extract data with multiple criteria and can give excellent output.
Follow this link to download practice files:
prabastech.com...
#LearnMsofficeTamil
#ExcelTips
#ExcelTamil
#PrabasMSOffice
Learn the top Excel Secrets you Should Know - Excel Tips & Tricks
Part 1: • Learn the top Excel Se...
Part 2: • Excel Tips and Tricks ...
Part 3: • Excel Tips & Tricks - ...
Link for VLOOKUP Playlist: • Learn VLOOKUP Excel Fu...
Learn HLOOKUP in Excel: • Learn HLOOKUP in Excel...
Excel Printing tips: • The top 5 Excel tips y...
Data Validation: • Data Validation - Part...
Conditional Formatting in Excel: • Conditional Formatting...
How to Calculate Age Using Excel - Excel Function: • How to Calculate Age U...
Learn Index & Match Function - Powerful & Advanced Lookup: • Index and Match - How ...
How to create an Excel Dashboard using Dynamic Chart: • How to create an Excel...
How to Create a Table of Contents in Excel: • How to Create a Table ...
Excel Pivot Table and Chart: • Pivot Table and Chart ...
Pivot Table- How to use formula in pivot table: • How to use Formula or ...
Learn Logical Functions in Excel AND, OR and NOT: • Learn Logical Function...
Nested Logical Function in Excel: • Must Learn Excel Logic...
Vlookup Combo: • Advanced and Combined ...
How to get data from another excel sheet (or) workbook using Vlookup: • VLOOKUP to get data fr...
How to Generate Random Numberin Excel: • How to Quickly Generat...
Offset | CountA | Randbetween: • Offset | CountA | Rand...
Different methods of converting excel table as a picture: • Converting Excel Table...
How to Create a Clickable table of content in PDF Document: • How to Create a Clicka...
The top Secrets (Tips & Tricks) you should know while printing worksheet:
Part 1: • The top 5 Excel tips y...
Part 2: • The top 10 Excel tips ...
Learn how to play with Date in Excel:
Part 1: • Issue with Excel Sorti...
Part 2: • How to Handle Date in ...
How to Find Duplicate Data in Excel
Part 1: • How to Find Duplicate ...
Part 2: • Fix Duplicate Data in ...
Learn Excel Chart:
• Chart in Microsoft Off...
• How to Create Waterfal...
• How to Create Excel #W...
How to Sort Data Automatically in Excel:
Part 1: • How to Sort Data in Ex...
Part 2: • How to Sort Data in Ex...
Word:
How to Create a Table of Content in Microsoft Word:
Part 1: • How to Create a Table ...
Part 2: • Custom Clickable Table...
Part 3: • Learn How to Solve the...
PowerPoint:
• How to Create 3D Objec...
• How to Create 3D Spher...
• How to Create 3D Chart...
• 3D Models with Morph T...
• How to Create an Amazi...
You could also refer Prabas MSOffice for more information:
/ @prabasmsoffice
Bro I want to know why want to ctrl +shift +enter konjum அது expline panunga bro
In the old version of Excel, it used to create Array formulas such as this.
@@Prabasmsoffice now I am using 2021 in that also want to use that method or normally enter
For new versions, you can use it without Ctrl + Enter.
Really good explain sir and so interesting video
Good video that we can get out put from multiple criteria. Usually I am using vlookup in this scenario but i have to unique the value first and then do vlookup function now i can get it from index match function from multiple criteria. Thanks for posting prabas.
Good understanding
Hi Bro, Rombe Nandri...
Simple and Great Explanation for beginner
Thanks for watching this video
Excellent Concept..!!!😘😘😘
Please upload more videos for " Macro".Prabha..!!
Thank you sir. its very useful to me.
Wow ...sema
Thank you..for explaining this concept in detail..🎉🎉❤❤
Thank you so much for your comment and for being a part of the channel! Your support means a lot to me. If you have any questions or need further help, feel free to ask. I'm here to help! 🙏
Hi Prabhakar, Can we get the values from different Sheet to the required Sheet in the same workbook using Index Match.? Like we use in VLOOKUP. Please Explain.
Awesome Video and good Explanation, Thankyou
Keep exploring 👍
Sir i need a excel file to practice, i ll find the file on u r practice link but its difficult to find out.....
drive.google.com/file/d/1m6xz6iOZnpIr1bUVxSK3-cK4UnJwwhzM/view
Intex match concept very useful for my work place and received more than benefit dynamic out. but one doubt Please describe a little why we use this symbol (*)
Thank so much bro.. the videos u upload very helpful 🙏🙏🙏🙏
Thanks too.
Semma praba
Thanks Bro 💝 Easy to Understand 🙏
Thanks for your love and support. Do refer our videos with your friends.
@@Prabasmsoffice sure bro ....
Bro workout file of this video is not found in the link which you given in the description. Pls share the practice file of this video...
Hi Prabha, but in this scenario if we had duplicate data means what we do ? Like wise same date in different type of volumes.
Usually, this kind of combined formula extract only the 1st occurance. But we can tweak that like the following concept. Do watch it if you didn't.
ua-cam.com/video/dqgJbpcW0PA/v-deo.html
@@Prabasmsoffice Oh.. ok Prabha.. Given link video already seen it was interesting..helpful ..thank you
super
Sir,
I have A column=id number B column=date & C column = time (hh:mm:ss). Same Id number & date four time repeated in different row with different time. How i can get fist time, second time, third & fourth time.
Eg:
1401 01/07/2019 8:30:14
1401 01/07/2019 12:30:32
1401 01/07/2019 01:32:12
1401 01/07/2019 05:31:14
Interesting question Karuppasamy. Request you to watch the following video. I have discussed a similar scenario here. Do watch and comment.
ua-cam.com/video/EoIK9SFqtwY/v-deo.html
Ok sir.
In your second : You can add one column using & by (a1&b1&c1) it then you can match with and option it will give same value
Yes, that way also works.
Amazing
Thanks for watching this video.
ua-cam.com/play/PLXqX9fbIzJMbyD30T_0MnBuKrT1P26c7B.html
Hi Bro, I tried this and working fine..awesome explanation ..thank you!!
Just 1 query,how to replace the #N/A with blank if no values for that particular date ?
To replace NA with blank, we usually use iferror formula. Try and comment.
@@Prabasmsoffice yes, it works now.. Thank you so much.... I have subscribed a week ago but I didn't get any notification of your new videos.. Haven't you posted yet?
We publish one video per week. Make sure to activate bell icon near the subscribe button for regular notification. .
Thanks.
@@Prabasmsoffice done.. I clicked all bell button.. I have one more question regarding this subject... Is there any option/way where we can see only the items where it has values instead showing as blank or error?
Still facing in receiving comments, then join in my telegram for uninterrupted notification. Access the below link:
ua-cam.com/users/redirect?redir_token=a2N4QL31UDMLT-zVXxc1dAsnr-18MTU1ODY5NTA2MEAxNTU4NjA4NjYw&q=https%3A%2F%2Ft.me%2Fprabasmsoffice&event=channel_description
Dear sir
One issue
MY Excell having 2019 multiple sales date examble 04-04-2019 this date colum copy to past another Excell sheet this date automatically changes on 04-04-2015 what is the problem. any idea 💡 please give your better solution
Thank you®ards
Have you used any formula reference to create the date?
@@Prabasmsoffice no but I am exporting tally and sum other software
Then i need look into the real problem. Email me with screenshots. I will try to review and help.
VBA pathi video podunga
Hi Bro, why did you use 1 next to MATCH? Please explain.
To extract only the matching output.
Since, this has multiple lookups, we have to extract the output which has multiple matches.
Try to read the arguments in the formula two three times. You will get it.
really nice, எனக்கு ஒரு சந்தேகம் சகோ, இதே டேபிளில் countif & match சேர்த்து பயன் படுத்தலாமா ?
உதாரணமாக இதே டேபிளில் வெஜ்ஜில் எத்தனை பர்கர் உள்ளது என வர வேண்டும் எப்படி ? அதாவது counting
possible. I will make a detailed video for this question.
thank you so much... waiting
Sir, This videos practice file can't found from your download site / Advanced Lookup Multiple criteria using Index Match with Array concept.
Idhula namba normal Vlookup formula use pannina, answer mismatch aaguma?? Exact Answer kedaikadha..?
Take this as a practice, come up with an answer for your question.....
dear, as sir explained.. when conditions are more in our task, we cannot use vllokup formula anymore.
Super but match fucntion la lookup array la * use panni podradhu konjam confusing a iruku... Separate athuku oru video pondunga bro ...
Match fucntion la * use panradhuju padhil and fucntion use panna mudiuma?
There are three condition in this video , how many conditions are possible ?
sir, i have certain zones(which is fixed) with some 10 categories(which is fixed), from which i have to extract data for every month. But the zones & categories wont change, ie fixed titles. say for eg, for january month, for 4 zone, i have to feed data manually @ the end of month. if i have cumulated these data for last 1 year & at any time as required by my manger, if i want to retrieve data immediately how i have to do it. My requirement is; (if i filter the month, then achieved quantity in those 10 categories for all 4 zones i should able to get instantly). how to do this, kindly give me suggestion or by doing a video for this sir.. also please tell me which would be the best practice to choose either columns or rows if 2 categories fixed to arrive data from more categories.
How to get output based on rank in Same index match function?
Possible brother, I will make a video for this.
@@Prabasmsoffice ok sir
how to rectify THERE WAS A PROBLEM SENDING COMMAND TO THE PROGRAM IN EXCEL after ignore the DDE option in General
hi I have one workbook contains multiple Sheets and multiple criteria but I need all the data in one sheet i sent the sheet to your mail Id plz solve the issue
Sir Mail Sent plz check
I will check and let you know.
@@Prabasmsoffice thanks
Where is the answer video for this question please tell me
@@Prabasmsoffice where is the answer video for this question please tell me..?
Sir already informed...no practice file for this concept..
Kindly include
Sorry for the inconvenience,
The website crashed one time in the past, because of that we lost some of the important practice files. I will try to re develop them.
@@Prabasmsoffice I understand sir ..ok...if possible ...do some alternate ..thanks
Intha Formula use pannitu "Slicer" apply panna Mudiyuma Prabha..??🙃🙃
Yes.
why we need array function instead of just enter the formula, it will work same right?
What version of office application you are using?
If you use the latest application, it is same only. But it is different in order version of office application...
Refer the below video to gain more understanding.
ua-cam.com/video/hAKQiYBn6ok/v-deo.html
@@Prabasmsoffice using 2007
Did you watch the link I have shared?
@@Prabasmsoffice yes thank you
How to create Calendar in excel?
I will create a video regarding this.
Hello Sir,
எனக்கு அவ்வளவாக English தெரியாது, அதனால் நான் தமிழும் English கலந்து சொல்லுறேன்.
நீங்கள் போடும் வீடியோக்களில் சிலது என்னுடைய வேலைக்கு பயன் மிகவும் படுகிறது நன்றி,
Sir, நான் ஒரு மருத்துவமனையில் வேலை செய்கின்றேன், இங்கு மருந்துகள் வாங்கி பாதுகாப்போம், மருந்துகள் வந்ததில் இருந்து வெளிய சொல்லும் நாள் வரை தேதியை கவனிக்க வேண்டும், சில மருந்துகள் அதிக விலை இருக்கும் குறுகிய காலம் மட்டுமே validity இருக்கும், அதற்க்காக excel பயன் படுத்தி மாதம் ஒரு முறை அதை கவனிப்போம், அப்பழுது excelலில் மருந்துகளின் தேதியை ஒன்று ஒன்றாக கவனித்து அதில் மஞ்சள், பச்சை மற்றும் சிகப்பு என்று கலர் இடுவோம், இதை formulaவின் மூலம் சரி செய்ய முடியுமா?
உதாரணம்:
Received Date: 01/06/2020
Middle தேதி: 01/08/2020
Before Expiry 01/09/2020
Expiry 01/10/2020
இதில் text எல்லாம் ஒரு வரியசையிழும், தேதி எல்லாம் அடுத் அடுத்த வரிசையில் வரும், இதனுடைய கடைசியில் remark என்ற காலம் வரும் அதில் தேதியை முன் வைத்து, start, middle, end என்று மாற்றி கலர் போடுவோம்.
Received Date: பகுதியில் தேதியை போடும்போதும், middle பகுதியில் தேதியை போடும்போதும், Before Expiry பகுதியில் தேதியை போடும்போதும், Expiry பகுதியில் தேதியை போடும்போதும் remarkல் automatic - start, middle, end என்று textம் மற்றும் கலர் வரவேண்டும், இதை formulaவின் மூலம் செய்தால் என் வேலை சுலபமாக முடியும்.
இவை அனைத்தும் ஒரு table,
அடுத்து இரண்டாவது table ஒன்று உள்ளது,
first tableலில் வரும் remarkல் start, middle, end இவைகளை இரண்டாவது tableலில் ஒரு காலத்திலும், இரண்டாவது காலத்தில் இதன் மொத்தம் எண்ணிக்கை வர வேண்டும், நான் உங்களுக்கு e-mailலில் sample copyயை அனுப்புகின்றேன்.
உங்களது தெளிவான பதிவிறக்கி நன்றி.
இந்த மாதிரியான செயல்களை Formula மற்றும் conditional formatting மூலமாகவும் செய்யமுடியும். நீங்கள் எக்சலை அனுப்பிவையுங்கள், நான் முடிந்தவரை ஒரு காணொளியை பதிவு செய்ய முயல்கிறேன்.
@@Prabasmsoffice நான் உங்கள் mail idக்கு அனுப்பி உள்ளேன்.
நன்றி
Trainnig class conduct panringala bro
Yes. Contact me at prabas.msoffice@gmail.com
@@Prabasmsoffice phone number share panna mudiuma..
9940261814
hello Sir I want to talk to you ,
Contact me @ 9940261814