Advanced Lookup Multiple criteria using Index Match with Array concept - Tamil | Prabas MS Office

[@creatorkannan675]

Bro I want to know why want to ctrl +shift +enter konjum அது expline panunga bro

[@Prabasmsoffice]

In the old version of Excel, it used to create Array formulas such as this.

[@creatorkannan675]

@@Prabasmsoffice now I am using 2021 in that also want to use that method or normally enter

[@Prabasmsoffice]

For new versions, you can use it without Ctrl + Enter.

[@mathumathu3586]

Really good explain sir and so interesting video

[@labibad9505]

Hi Bro, Rombe Nandri... Simple and Great Explanation for beginner

[@Prabasmsoffice]

Thanks for watching this video

[@shankarganeshgajendran5438]

Thank you..for explaining this concept in detail..🎉🎉❤❤

[@Prabasmsoffice]

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! 🙏

[@Ridetoright111]

Excellent Concept..!!!😘😘😘 Please upload more videos for " Macro".Prabha..!!

[@ranjithrash5329]

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.

[@Prabasmsoffice]

Good understanding

[@nagalingamparasuraman5860]

Thank you sir. its very useful to me.

[@sudharsan2124]

Thank so much bro.. the videos u upload very helpful 🙏🙏🙏🙏

[@Prabasmsoffice]

Thanks too.

[@selvarasan.v7246]

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 (*)

[@sundarshiva]

Wow ...sema

[@Naruto65943]

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...

[@CarCraze-VimalKumar]

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.

[@karuppusamyn515]

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

[@Prabasmsoffice]

Interesting question Karuppasamy. Request you to watch the following video. I have discussed a similar scenario here. Do watch and comment. kzbin.info/www/bejne/e6CsfGyJe9anrbs

[@karuppusamyn515]

Ok sir.

[@UniiversalWiisdom]

Thanks Bro 💝 Easy to Understand 🙏

[@Prabasmsoffice]

Thanks for your love and support. Do refer our videos with your friends.

[@UniiversalWiisdom]

@@Prabasmsoffice sure bro ....

[@balamurali6101]

Awesome Video and good Explanation, Thankyou

[@Prabasmsoffice]

Keep exploring 👍

[@balamurali6101]

Sir i need a excel file to practice, i ll find the file on u r practice link but its difficult to find out.....

[@Prabasmsoffice]

drive.google.com/file/d/1m6xz6iOZnpIr1bUVxSK3-cK4UnJwwhzM/view

[@ramzein776]

There are three condition in this video , how many conditions are possible ?

[@TheKamaladasan]

Sir, This videos practice file can't found from your download site / Advanced Lookup Multiple criteria using Index Match with Array concept.

[@ume5336]

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

[@Prabasmsoffice]

Yes, that way also works.

[@sathies_Junction]

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.

[@kodairoadkannan]

super

[@Ridetoright111]

Hi Prabha, but in this scenario if we had duplicate data means what we do ? Like wise same date in different type of volumes.

[@Prabasmsoffice]

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. kzbin.info/www/bejne/mqLKe5WmmLxjhqM

[@Ridetoright111]

@@Prabasmsoffice Oh.. ok Prabha.. Given link video already seen it was interesting..helpful ..thank you

[@MM-pq9tp]

how to rectify THERE WAS A PROBLEM SENDING COMMAND TO THE PROGRAM IN EXCEL after ignore the DDE option in General

[@Manikandan-eh1hj]

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&regards

[@Prabasmsoffice]

Have you used any formula reference to create the date?

[@Manikandan-eh1hj]

@@Prabasmsoffice no but I am exporting tally and sum other software

[@Prabasmsoffice]

Then i need look into the real problem. Email me with screenshots. I will try to review and help.

[@kuttykathai5196]

Amazing

[@Prabasmsoffice]

Thanks for watching this video. kzbin.info/aero/PLXqX9fbIzJMbyD30T_0MnBuKrT1P26c7B

[@karuppusamyn515]

How to get output based on rank in Same index match function?

[@Prabasmsoffice]

Possible brother, I will make a video for this.

[@karuppusamyn515]

@@Prabasmsoffice ok sir

[@raphealjesudassamal517]

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

[@raphealjesudassamal517]

Sir Mail Sent plz check

[@Prabasmsoffice]

I will check and let you know.

[@raphealjesudassamal517]

@@Prabasmsoffice thanks

[@kaviranjitha6624]

Where is the answer video for this question please tell me

[@kaviranjitha6624]

@@Prabasmsoffice where is the answer video for this question please tell me..?

[@gurub5773]

VBA pathi video podunga

[@nansri1]

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 ?

[@Prabasmsoffice]

To replace NA with blank, we usually use iferror formula. Try and comment.

[@nansri1]

@@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?

[@Prabasmsoffice]

We publish one video per week. Make sure to activate bell icon near the subscribe button for regular notification. . Thanks.

[@nansri1]

@@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?

[@Prabasmsoffice]

Still facing in receiving comments, then join in my telegram for uninterrupted notification. Access the below link: kzbin.info?redir_token=a2N4QL31UDMLT-zVXxc1dAsnr-18MTU1ODY5NTA2MEAxNTU4NjA4NjYw&q=https%3A%2F%2Ft.me%2Fprabasmsoffice&event=channel_description

[@CarCraze-VimalKumar]

Hi Bro, why did you use 1 next to MATCH? Please explain.

[@Prabasmsoffice]

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.

[@sadheshkumarp5704]

How to create Calendar in excel?

[@Prabasmsoffice]

I will create a video regarding this.

[@gurub5773]

Semma praba

[@Naruto65943]

Idhula namba normal Vlookup formula use pannina, answer mismatch aaguma?? Exact Answer kedaikadha..?

[@Prabasmsoffice]

Take this as a practice, come up with an answer for your question.....

[@badrudeen7763]

dear, as sir explained.. when conditions are more in our task, we cannot use vllokup formula anymore.

[@hajajahirhussain6814]

Super but match fucntion la lookup array la * use panni podradhu konjam confusing a iruku... Separate athuku oru video pondunga bro ...

[@hajajahirhussain6814]

Match fucntion la * use panradhuju padhil and fucntion use panna mudiuma?

[@praveeng3921]

why we need array function instead of just enter the formula, it will work same right?

[@Prabasmsoffice]

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.

[@Prabasmsoffice]

kzbin.info/www/bejne/nnKugpyPd9Nppc0

[@praveeng3921]

@@Prabasmsoffice using 2007

[@Prabasmsoffice]

Did you watch the link I have shared?

[@praveeng3921]

@@Prabasmsoffice yes thank you

[@badrudeen7763]

Sir already informed...no practice file for this concept.. Kindly include

[@Prabasmsoffice]

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.

[@badrudeen7763]

@@Prabasmsoffice I understand sir ..ok...if possible ...do some alternate ..thanks

[@mohamedmafas8389]

really nice, எனக்கு ஒரு சந்தேகம் சகோ, இதே டேபிளில் countif & match சேர்த்து பயன் படுத்தலாமா ? உதாரணமாக இதே டேபிளில் வெஜ்ஜில் எத்தனை பர்கர் உள்ளது என வர வேண்டும் எப்படி ? அதாவது counting

[@Prabasmsoffice]

possible. I will make a detailed video for this question.

[@mohamedmafas8389]

thank you so much... waiting

[@Ridetoright111]

Intha Formula use pannitu "Slicer" apply panna Mudiyuma Prabha..??🙃🙃

[@Prabasmsoffice]

Yes.

[@BK08082]

Trainnig class conduct panringala bro

[@Prabasmsoffice]

Yes. Contact me at [e-mail]

[@BK08082]

@@Prabasmsoffice phone number share panna mudiuma..

[@Prabasmsoffice]

9940261814

[@MTCC-g4s]

hello Sir I want to talk to you ,

[@Prabasmsoffice]

Contact me @ 9940261814

[@mainudeens]

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யை அனுப்புகின்றேன்.

[@Prabasmsoffice]

உங்களது தெளிவான பதிவிறக்கி நன்றி. இந்த மாதிரியான செயல்களை Formula மற்றும் conditional formatting மூலமாகவும் செய்யமுடியும். நீங்கள் எக்சலை அனுப்பிவையுங்கள், நான் முடிந்தவரை ஒரு காணொளியை பதிவு செய்ய முயல்கிறேன்.

[@mainudeens]

@@Prabasmsoffice நான் உங்கள் mail idக்கு அனுப்பி உள்ளேன். நன்றி