एक्सेलमधील इंडेक्स आणि लुकअप फंक्शन्स हे व्हीपीआरसाठी सर्वोत्तम पर्याय आहेत. डेटाच्या सूचीमध्ये मूल्ये शोधणे एक्सेल टेबलमध्ये योग्य संख्या कशी शोधायची

वेगवेगळ्या नॉन-समीप श्रेणींमध्ये विखुरलेल्या भागांमध्ये विभागलेल्या अनेक सारण्यांपेक्षा किंवा अगदी वैयक्तिक शीट्समधून, मोठ्या, परंतु संपूर्ण टेबल किंवा सेलच्या लगतच्या श्रेणींमध्ये शोधणे निश्चितपणे सोपे आहे. जरी आपण एकाच वेळी अनेक सारण्यांवर स्वयंचलित शोध केला तरीही, महत्त्वपूर्ण अडथळे उद्भवू शकतात. आणि सर्व डेटा एका टेबलमध्ये ठेवणे कठीण आहे, कधीकधी जवळजवळ अशक्य आहे. एका विशिष्ट उदाहरणाचा वापर करून, आम्ही Excel मधील एकाधिक टेबलांवर एकाचवेळी शोधासाठी योग्य उपाय दाखवू.

एकाधिक श्रेणींमध्ये एकाचवेळी शोध

व्हिज्युअल उदाहरणासाठी, एकाच शीटच्या जवळच्या नसलेल्या श्रेणींमध्ये असलेल्या तीन साध्या वेगळ्या टेबल्स तयार करूया:

आपण उत्पादनांच्या 20 तुकड्यांचे उत्पादन करण्यासाठी आवश्यक असलेली रक्कम शोधली पाहिजे. दुर्दैवाने, हा डेटा वेगवेगळ्या स्तंभ आणि पंक्तींमध्ये आहे. म्हणून, सर्व प्रथम, आपल्याला ही उत्पादने (प्रथम सारणी) तयार करण्यासाठी किती वेळ लागेल हे तपासण्याची आवश्यकता आहे.

प्राप्त केलेल्या डेटाच्या आधारे, ताबडतोब दुसर्‍या टेबलमधील शोधावर जाणे आवश्यक आहे आणि दिलेल्या उत्पादन खंडामध्ये किती कामगारांचा समावेश असावा हे शोधणे आवश्यक आहे. प्राप्त झालेल्या निकालाची तुलना तिसऱ्या सारणीच्या डेटाशी केली पाहिजे. अशा प्रकारे, तीन सारण्यांमध्ये एका शोध ऑपरेशनमध्ये, आम्ही आवश्यक खर्च (रक्कम) त्वरित निर्धारित करू.

सरासरी एक्सेल वापरकर्ता VLOOKUP सारख्या फंक्शन्सवर आधारित सूत्रांचा वापर करून उपाय शोधतो. तसेच 3 टप्प्यांमध्ये शोध पूर्ण करेल (प्रत्येक टेबलसाठी स्वतंत्रपणे). असे दिसून आले की आपण विशेष सूत्र वापरून केवळ 1 टप्प्यात शोध करून त्वरित निकाल मिळवू शकता. यासाठी:

1. सेल E6 मध्ये, मूल्य 20 प्रविष्ट करा, जी शोध क्वेरीसाठी अट आहे.
2. सेल E7 मध्ये, खालील सूत्र प्रविष्ट करा:

20 पीसीसाठी उत्पादन खर्च. एक विशिष्ट उत्पादन.

एकाधिक सारण्यांमध्ये VLOOKUP सह सूत्र कसे कार्य करते:

या सूत्राच्या ऑपरेशनचे सिद्धांत मुख्य VLOOKUP फंक्शन (पहिले एक) साठी सर्व वितर्कांच्या अनुक्रमिक शोधावर आधारित आहे. प्रथम, तिसरे VLOOKUP फंक्शन सेल E6 साठी मूल्य म्हणून निर्दिष्ट केलेल्या उत्पादनाचे 20 तुकडे तयार करण्यासाठी आवश्यक असलेल्या वेळेसाठी प्रथम सारणी शोधते (जे नंतर आवश्यक असल्यास बदलले जाऊ शकते). नंतर दुसरे VLOOKUP फंक्शन मुख्य फंक्शनच्या पहिल्या वितर्काचे मूल्य शोधते.

तिसरे फंक्शन शोधण्याच्या परिणामी, आम्हाला 125 मूल्य मिळते, जे दुसऱ्या फंक्शनसाठी पहिले वितर्क आहे. सर्व पॅरामीटर्स प्राप्त झाल्यानंतर, दुसरे कार्य उत्पादनासाठी आवश्यक कामगारांच्या संख्येसाठी दुसऱ्या टेबलमध्ये दिसते. परिणामी, मूल्य 5 परत केले जाते, जे मुख्य कार्याद्वारे वापरले जाईल. सर्व प्राप्त डेटावर आधारित, सूत्र गणनाचा अंतिम परिणाम देते. बहुदा, एका विशिष्ट उत्पादनाचे 20 तुकडे तयार करण्यासाठी $ 1,750 ची रक्कम आवश्यक आहे.

या तत्त्वानुसार, तुम्ही अनेक शीटमधून VLOOKUP फंक्शनसाठी सूत्रे वापरू शकता.

मायक्रोसॉफ्ट एक्सेल अनेकदा मोठ्या प्रमाणात माहितीसह कार्य करते. हे हजारो पंक्ती, स्तंभ आणि स्थानांसह विशाल तक्ते तयार करते. अशा अॅरेमध्ये कोणताही विशिष्ट डेटा शोधणे कठीण होऊ शकते. आणि कधी कधी हे अजिबात अशक्य आहे. हे कार्य सोपे केले जाऊ शकते. Excel मध्ये योग्य शब्द कसा शोधायचा ते शिका. हे तुमच्यासाठी दस्तऐवज नेव्हिगेट करणे सोपे करेल. आणि आपण शोधत असलेल्या माहितीवर आपण पटकन जाऊ शकता.

तुम्ही जे शोधत आहात त्या सर्व सेलचे पत्ते प्रदर्शित करण्यासाठी, पुढील गोष्टी करा:

1. तुमच्याकडे Office 2010 असल्यास, मेनूवर जा - संपादित करा - शोधा.
2. इनपुट फील्डसह एक विंडो उघडेल. त्यात एक शोध वाक्यांश लिहा.
3. एक्सेल 2007 मध्ये, हे बटण संपादन पॅनेलमधील होम मेनूवर आहे. ती उजवीकडे आहे.
4. सर्व आवृत्त्यांमध्ये समान परिणाम Ctrl + F की दाबून प्राप्त केला जाऊ शकतो.
5. फील्डमध्ये, तुम्हाला शोधायचा असलेला शब्द, वाक्यांश किंवा संख्या टाइप करा.
6. संपूर्ण दस्तऐवज शोधण्यासाठी "सर्व शोधा" वर क्लिक करा. तुम्ही "पुढील" वर क्लिक केल्यास, प्रोग्राम एक्सेल सेल कर्सरच्या खाली असलेल्या एका वेळी एक सेल निवडेल.
7. प्रक्रिया पूर्ण होण्याची प्रतीक्षा करा. दस्तऐवज जितका मोठा असेल तितका वेळ सिस्टम शोधेल.
8. परिणामांसह एक सूची दिसेल: दिलेल्या वाक्यांशाशी जुळणारी सेलची नावे आणि पत्ते आणि त्यात लिहिलेला मजकूर.
9. जेव्हा तुम्ही प्रत्येक ओळीवर क्लिक कराल, तेव्हा संबंधित सेल हायलाइट होईल.
10. सोयीसाठी, आपण विंडो "ताणून" शकता. त्यामुळे ते अधिक रेषा दर्शवेल.
11. डेटा क्रमवारी लावण्यासाठी, शोध परिणामांवरील स्तंभाच्या नावांवर क्लिक करा. तुम्ही "शीट" वर क्लिक केल्यास, शीटच्या नावावर आधारित रेषा वर्णक्रमानुसार तयार होतील, तुम्ही "मूल्ये" निवडल्यास - त्या मूल्यानुसार ठेवल्या जातील.
12. हे स्तंभ देखील "ताणलेले" आहेत.

तुम्ही तुमच्या स्वतःच्या अटी सेट करू शकता. उदाहरणार्थ, एकाधिक वर्ण शोधणे सुरू करा. तुम्हाला पूर्णपणे आठवत नसलेला शब्द Excel मध्ये कसा शोधायचा ते येथे आहे:

1. लेबलचा फक्त एक भाग एंटर करा. तुमच्याकडे किमान एक अक्षर असू शकते - ते उपस्थित असलेली सर्व ठिकाणे निवडली जातील.
2. * (तारांकित) चिन्हे वापरा आणि? (प्रश्न चिन्ह). ते हरवलेल्या वर्णांची जागा घेतात.
3. प्रश्न एक गहाळ आयटम सूचित करतो. तुम्ही लिहिल्यास, उदाहरणार्थ, "P???", सेल प्रदर्शित केले जातील ज्यात "P" ने सुरू होणारा चार वर्णांचा शब्द असेल: "नांगर", "फील्ड", "जोडी", आणि असेच.
4. तारा (*) कितीही वर्णांसाठी बदलतो. मूळ "रास्ट" असलेली सर्व मूल्ये शोधण्यासाठी, "*रास्ट*" की शोधणे सुरू करा.

आपण सेटिंग्जवर देखील जाऊ शकता:

1. शोधा विंडोमध्ये, पर्याय क्लिक करा.
2. "ब्राउझ करा" आणि "शोध क्षेत्र" विभागात, तुम्हाला कुठे आणि कोणत्या निकषांनुसार जुळणी शोधायची आहेत ते निर्दिष्ट करा. तुम्ही सूत्रे, नोट्स किंवा मूल्ये निवडू शकता.
3. सिस्टमला लोअरकेस आणि अपरकेस अक्षरांमध्ये फरक करण्यासाठी, "केस सेन्सिटिव्ह" बॉक्स चेक करा.
4. तुम्ही "संपूर्ण सेल" बॉक्स चेक केल्यास, परिणाम सेल दर्शवतील ज्यात फक्त निर्दिष्ट शोध वाक्यांश आहे आणि दुसरे काहीही नाही.

सेल फॉरमॅट पर्याय

विशिष्ट फिल किंवा शैलीसह मूल्ये शोधण्यासाठी, सेटिंग्ज वापरा. Excel मध्ये एखादा शब्द बाकीच्या मजकुरापेक्षा वेगळा दिसत असल्यास तो कसा शोधायचा ते येथे आहे:

1. शोध बॉक्समध्ये, "पर्याय" वर क्लिक करा आणि "स्वरूप" बटणावर क्लिक करा. अनेक टॅबसह एक मेनू उघडेल.
2. तुम्ही विशिष्ट फॉन्ट, फ्रेम प्रकार, पार्श्वभूमी रंग, डेटा स्वरूप निर्दिष्ट करू शकता. प्रणाली दिलेल्या निकषांशी जुळणारी ठिकाणे शोधेल.
3. वर्तमान सेलमधून माहिती घेण्यासाठी (या टप्प्यावर निवडलेले), "या सेलचे स्वरूप वापरा" वर क्लिक करा. मग प्रोग्रामला समान आकार आणि वर्णांचे प्रकार, समान रंग, समान सीमा आणि यासारख्या सर्व मूल्ये सापडतील.

अनेक शब्द शोध

एक्सेलमध्ये, तुम्ही संपूर्ण वाक्यांशांनुसार सेल शोधू शकता. परंतु आपण "ब्लू बॉल" की प्रविष्ट केल्यास, सिस्टम या विनंतीवर अचूकपणे कार्य करेल. "ब्लू क्रिस्टल बॉल" किंवा "ब्लू ग्लिटर बॉल" असलेली मूल्ये परिणामांमध्ये दिसणार नाहीत.

Excel मध्ये एकापेक्षा जास्त शब्द शोधण्यासाठी, परंतु एकाच वेळी अनेक, पुढील गोष्टी करा:

1. त्यांना शोध बारमध्ये लिहा.
2. त्यांच्या दरम्यान तारे ठेवा. हे "*Text* *Text2* *Text3*" निघेल. हे निर्दिष्ट शिलालेख असलेली सर्व मूल्ये शोधेल. त्यांच्यामध्ये कोणतीही पात्रे आहेत की नाही याची पर्वा न करता.
3. अशा प्रकारे, आपण वैयक्तिक अक्षरे देखील की सेट करू शकता.

फिल्टर करा

फिल्टर वापरून एक्सेलमध्ये कसे शोधायचे ते येथे आहे:

1. कोणताही भरलेला सेल निवडा.
2. होम - सॉर्ट - फिल्टर क्लिक करा.
3. सेलच्या वरच्या ओळीत बाण दिसतील. हा एक ड्रॉप डाउन मेनू आहे. ते उघडा.
4. मजकूर फील्डमध्ये तुमची क्वेरी प्रविष्ट करा आणि ओके क्लिक करा.
5. स्तंभ केवळ शोध वाक्यांश असलेल्या सेल प्रदर्शित करेल.
6. परिणाम रीसेट करण्यासाठी, ड्रॉप-डाउन सूचीमध्ये "सर्व निवडा" तपासा.
7. फिल्टर बंद करण्यासाठी, क्रमवारीत पुन्हा त्यावर क्लिक करा.

तुम्हाला कोणत्या पंक्तीमध्ये आवश्यक मूल्य आहे हे माहित नसल्यास ही पद्धत कार्य करणार नाही.

Excel मध्ये वाक्यांश किंवा संख्या शोधण्यासाठी, अंगभूत इंटरफेस वैशिष्ट्ये वापरा. तुम्ही अतिरिक्त शोध पर्याय निवडू शकता आणि फिल्टर सक्षम करू शकता.

हे ट्यूटोरियल फंक्शन्सच्या मुख्य फायद्यांबद्दल बोलते INDEXआणि मॅच Excel मध्ये, जे त्यांना तुलनेत अधिक आकर्षक बनवते VLOOKUP. तुम्हाला सूत्रांची अनेक उदाहरणे दिसतील जी तुम्हाला फंक्शनला तोंड देणारी अनेक जटिल कार्ये सहजपणे हाताळण्यास मदत करतील. VLOOKUPशक्तीहीन

अलीकडील अनेक लेखांमध्ये, आम्ही नवशिक्या वापरकर्त्यांना फंक्शनच्या मूलभूत गोष्टी समजावून सांगण्याचा सर्वतोपरी प्रयत्न केला आहे. VLOOKUPआणि प्रगत वापरकर्त्यांसाठी अधिक जटिल सूत्रांची उदाहरणे दाखवा. आता आम्ही तुम्हाला वापरण्यापासून परावृत्त न करण्याचा प्रयत्न करू VLOOKUP, नंतर किमान Excel मध्ये अनुलंब शोध लागू करण्यासाठी पर्यायी मार्ग दाखवा.

आम्हाला याची गरज का आहे? - तू विचार. होय कारण VLOOKUPएक्सेलमध्ये हे एकमेव शोध कार्य नाही आणि त्याच्या अनेक मर्यादांमुळे तुम्हाला अनेक परिस्थितींमध्ये इच्छित परिणाम मिळण्यापासून रोखता येते. दुसरीकडे, कार्ये INDEXआणि मॅच- अधिक लवचिक आणि अनेक वैशिष्ट्ये आहेत जी त्यांना पेक्षा अधिक आकर्षक बनवतात VLOOKUP.

INDEX आणि MATCH बद्दल मूलभूत माहिती

या ट्युटोरियलचा उद्देश फंक्शन्सची क्षमता दाखवणे हा आहे INDEXआणि मॅच Excel मध्ये अनुलंब शोध लागू करण्यासाठी, आम्ही त्यांच्या वाक्यरचना आणि अनुप्रयोगावर लक्ष देणार नाही.

आम्ही येथे सार समजून घेण्यासाठी आवश्यक किमान देतो आणि नंतर आम्ही सूत्रांच्या तपशीलवार उदाहरणांचे विश्लेषण करू जे वापरण्याचे फायदे दर्शवितात. INDEXआणि मॅचऐवजी VLOOKUP.

INDEX - सिंटॅक्स आणि फंक्शनचा अनुप्रयोग

कार्य INDEXएक्सेलमधील (INDEX) दिलेल्या पंक्ती आणि स्तंभ क्रमांकावरील अॅरेमधून मूल्य मिळवते. फंक्शनमध्ये खालील वाक्यरचना आहे:

प्रत्येक युक्तिवादाचे एक अतिशय सोपे स्पष्टीकरण आहे:

• रचना(अॅरे) ही सेलची श्रेणी आहे ज्यामधून मूल्य काढायचे आहे.
• पंक्ती_संख्या(rownumber) अॅरेमधील पंक्ती क्रमांक आहे ज्यामधून मूल्य काढायचे आहे. निर्दिष्ट न केल्यास, युक्तिवाद आवश्यक आहे स्तंभ_संख्या(स्तंभ_संख्या).
• स्तंभ_संख्या(column_number) ही अॅरेमधील स्तंभाची संख्या आहे ज्यामधून मूल्य काढायचे आहे. निर्दिष्ट न केल्यास, युक्तिवाद आवश्यक आहे पंक्ती_संख्या(रेखा_क्रमांक)

जर दोन्ही वितर्क दिले असतील, तर फंक्शन INDEXनिर्दिष्ट पंक्ती आणि स्तंभाच्या छेदनबिंदूवर सेलमधून मूल्य मिळवते.

येथे एक साधे कार्य उदाहरण आहे INDEX(INDEX):

INDEX(A1:C10,2,3)
=INDEX(A1:C10,2,3)

सूत्र श्रेणीमध्ये शोधते A1:C10आणि मध्ये सेलचे मूल्य परत करते 2राओळ आणि 3 मीस्तंभ, म्हणजे सेलमधून C2.

खूप सोपे, बरोबर? तथापि, सराव मध्ये, आपल्याला नेहमी माहित नसते की आपल्याला कोणत्या पंक्ती आणि स्तंभाची आवश्यकता आहे आणि म्हणून आपल्याला फंक्शनची मदत आवश्यक आहे. मॅच.

मॅच - सिंटॅक्स आणि फंक्शनचा अनुप्रयोग

कार्य मॅचएक्सेलमधील (MATCH) सेलच्या श्रेणीमध्ये निर्दिष्ट मूल्य शोधते आणि श्रेणीमध्ये त्या मूल्याचे संबंधित स्थान परत करते.

उदाहरणार्थ, श्रेणीमध्ये असल्यास B1:B3न्यू-यॉर्क, पॅरिस, लंडन या मूल्यांचा समावेश आहे, नंतर खालील सूत्र एक अंक देईल 3 , कारण "लंडन" हा यादीतील तिसरा आयटम आहे.

सामना("लंडन",B1:B3,0)
=MATCH("लंडन",B1:B3,0)

कार्य मॅच(MATCH) मध्ये खालील वाक्यरचना आहे:

MATCH(lookup_value,lookup_array,)
MATCH(lookup_value, lookup_array, [match_type])

• lookup_value(lookup_value) तुम्ही शोधत असलेली संख्या किंवा मजकूर आहे. वितर्क हे बुलियन किंवा सेल संदर्भासह मूल्य असू शकते.
• लुकअप_अॅरे(lookup_array) ही सेलची श्रेणी आहे ज्यामध्ये शोधायचे आहे.
• match_type(match_type) - हा युक्तिवाद फंक्शनला सांगतो मॅचतुम्हाला अचूक जुळणी किंवा अंदाजे जुळणी शोधायची आहे का:
  • 1 किंवा निर्दिष्ट नाही- इच्छित मूल्यापेक्षा कमी किंवा समान कमाल मूल्य शोधते. वर पाहिल्या जाणार्‍या अॅरेची चढत्या क्रमाने क्रमवारी लावणे आवश्यक आहे, म्हणजेच सर्वात लहान ते सर्वात मोठे.
  • 0 - इच्छित एक समान प्रथम मूल्य शोधते. संयोजनासाठी INDEX/मॅचतुम्हाला नेहमी अचूक जुळणी हवी असते, म्हणून तिसरे फंक्शन आर्ग्युमेंट मॅचसमान असावे 0 .
  • -1 - इच्छित मूल्यापेक्षा मोठे किंवा समान सर्वात लहान मूल्य शोधते. पाहिले जात असलेले अॅरे उतरत्या क्रमाने क्रमवारी लावले जाणे आवश्यक आहे, म्हणजे सर्वात मोठ्या ते सर्वात लहान.

पहिल्या दृष्टीक्षेपात, फंक्शनचे फायदे मॅचशंका निर्माण करते. श्रेणीतील घटकाची स्थिती कोणाला माहित असणे आवश्यक आहे? आम्हाला या घटकाचे मूल्य जाणून घ्यायचे आहे!

मी तुम्हाला स्मरण करून देतो की तुम्ही शोधत असलेल्या मूल्याची सापेक्ष स्थिती (म्हणजे पंक्ती आणि/किंवा स्तंभ क्रमांक) हीच आम्हाला वितर्कांसाठी निर्दिष्ट करण्याची आवश्यकता आहे. पंक्ती_संख्या(लाइन_क्रमांक) आणि/किंवा स्तंभ_संख्या(स्तंभ_संख्या) कार्ये INDEX(INDEX). जसे तुम्हाला आठवते, फंक्शन INDEXदिलेल्या पंक्ती आणि स्तंभाच्या छेदनबिंदूवर मूल्य परत करू शकते, परंतु आम्हाला कोणत्या पंक्ती आणि स्तंभामध्ये स्वारस्य आहे हे निर्धारित करू शकत नाही.

Excel मध्ये INDEX आणि MATCH कसे वापरावे

आता तुम्हाला या दोन फंक्शन्सबद्दल मूलभूत माहिती माहित आहे, मला विश्वास आहे की फंक्शन्स कसे आहेत हे आधीच स्पष्ट होत आहे मॅचआणि INDEXएकत्र काम करू शकतात. मॅचसेलच्या दिलेल्या श्रेणीमध्ये शोधलेल्या मूल्याची सापेक्ष स्थिती परिभाषित करते आणि INDEXती संख्या (किंवा संख्या) वापरते आणि संबंधित सेलमधून निकाल देते.

अद्याप पूर्णपणे स्पष्ट नाही? वैशिष्ट्यांचे प्रतिनिधित्व करा INDEXआणि मॅचया फॉर्ममध्ये:

INDEX(, (सामना ( इच्छित मूल्य,आम्ही शोधत असलेला स्तंभ,0))
=INDEX( स्तंभ ज्यामधून काढायचा आहे;(मॅच( इच्छित मूल्य;आम्ही शोधत असलेला स्तंभ;0))

मला वाटते की एका उदाहरणाने समजून घेणे आणखी सोपे होईल. समजा तुमच्याकडे राज्यांच्या राजधानींची खालील यादी आहे:

खालील सूत्र वापरून जपानच्या राजधानीतील एका शहराची लोकसंख्या शोधू या:

INDEX($D$2:$D$10,MATCH("जपान",$B$2:$B$10,0))
=INDEX($D$2:$D$10;MATCH("जपान";$B$2:$B$10;0))

आता या सूत्रातील प्रत्येक घटक काय करतो ते पाहू:

• कार्य मॅच(MATCH) स्तंभातील "जपान" मूल्य शोधते बी, आणि विशेषतः पेशींमध्ये B2:B10, आणि एक संख्या मिळवते 3 , कारण "जपान" यादीत तिसऱ्या स्थानावर आहे.
• कार्य INDEX(INDEX) वापरतो 3 वादासाठी पंक्ती_संख्या(लाइन_संख्या), जे कोणत्या पंक्तीमधून मूल्य परत केले जावे हे निर्दिष्ट करते. त्या. एक साधे सूत्र मिळते:

  INDEX($D$2:$D$10.3)
  =INDEX($D$2:$D$10,3)

  सूत्र असे काहीतरी सांगते: सेलमध्ये पहा D2आधी D10आणि तिसर्‍या रांगेतून म्हणजेच सेलमधून मूल्य काढा D4, दुसऱ्या ओळीपासून मोजणी सुरू होते.

येथे Excel मध्ये परिणाम आहे:

महत्वाचे! फंक्शन वापरत असलेल्या अॅरेमधील पंक्ती आणि स्तंभांची संख्या INDEX(INDEX), वितर्क मूल्यांशी जुळले पाहिजे पंक्ती_संख्या(लाइन_क्रमांक) आणि स्तंभ_संख्या(स्तंभ_संख्या) कार्ये मॅच(मॅच). अन्यथा, सूत्राचा परिणाम चुकीचा असेल.

थांबा, थांबा... आपण फंक्शन का वापरू शकत नाही VLOOKUP(VLOOKUP)? चक्रव्यूह शोधण्यात वेळ वाया घालवण्यात काही अर्थ आहे का? मॅचआणि INDEX?

VLOOKUP("जपान",$B$2:$D$2,3)
=VLOOKUP("जपान",$B$2:$D$2,3)

या प्रकरणात, काही अर्थ नाही! या उदाहरणाचा उद्देश निव्वळ स्पष्टीकरणात्मक आहे, जेणेकरून तुम्हाला समजेल की कार्य कसे होते मॅचआणि INDEXजोडी काम. खालील उदाहरणे तुम्हाला बंडलची खरी ताकद दाखवतील. INDEXआणि मॅच, जे अनेक कठीण प्रसंगांना सहजतेने सामोरे जाते VLOOKUPअडकतो.

VLOOKUP पेक्षा INDEX/MATCH का चांगले आहे?

उभ्या शोधासाठी कोणते सूत्र वापरायचे हे ठरवताना, बहुतेक एक्सेल गुरू असे मानतात INDEX/मॅचपेक्षा बरेच चांगले VLOOKUP. तथापि, अनेक एक्सेल वापरकर्ते अजूनही वापरण्याचा अवलंब करतात VLOOKUP, कारण हे कार्य खूप सोपे आहे. याचे कारण असे की फार कमी लोकांना वरून स्विच करण्याचे पूर्ण फायदे पूर्णपणे समजतात VLOOKUPएक घड वर INDEXआणि मॅच, आणि कोणीही अधिक जटिल सूत्र अभ्यासण्यात वेळ घालवू इच्छित नाही.

Excel मध्ये MATCH/INDEX वापरण्याचे शीर्ष 4 फायदे:

1. उजवीकडून डावीकडे शोधा.कोणत्याही साक्षर एक्सेल वापरकर्त्याला माहीत आहे, VLOOKUPडावीकडे पाहू शकत नाही, याचा अर्थ इच्छित मूल्य अभ्यासाधीन श्रेणीच्या सर्वात डावीकडे असणे आवश्यक आहे. बाबतीत मॅच/INDEX, शोध स्तंभ शोध श्रेणीच्या डावीकडे किंवा उजवीकडे असू शकतो. उदाहरण: हे वैशिष्ट्य कृतीत दर्शवेल.

2. सुरक्षित जोडणे किंवा स्तंभ काढणे.फंक्शनसह सूत्रे VLOOKUPआपण लुकअप टेबलमध्ये स्तंभ काढल्यास किंवा जोडल्यास कार्य करणे थांबवा किंवा चुकीची मूल्ये परत करा. कार्यासाठी VLOOKUPकोणताही स्तंभ घातला किंवा काढला तर सूत्राचा परिणाम बदलेल कारण वाक्यरचना VLOOKUPतुम्हाला संपूर्ण श्रेणी आणि विशिष्ट स्तंभ क्रमांक निर्दिष्ट करणे आवश्यक आहे ज्यामधून तुम्ही डेटा काढू इच्छिता.

उदाहरणार्थ, आपल्याकडे टेबल असल्यास A1:C10, आणि तुम्हाला स्तंभातून डेटा काढायचा आहे बी, नंतर तुम्हाला मूल्य सेट करणे आवश्यक आहे 2 वादासाठी col_index_num(स्तंभ_संख्या) कार्ये VLOOKUP, याप्रमाणे:

VLOOKUP("लुकअप मूल्य",A1:C10,2)
=VLOOKUP("लुकअप मूल्य";A1:C10;2)

तुम्ही नंतर स्तंभांमध्ये नवीन स्तंभ टाकल्यास एआणि बी, नंतर वितर्काचे मूल्य वरून बदलावे लागेल 2 वर 3 , अन्यथा सूत्र नुकतेच घातलेल्या स्तंभातून निकाल देईल.

वापरत आहे मॅच/INDEX, आपण निकाल विकृत न करता अभ्यासाअंतर्गत असलेल्या श्रेणीमध्ये स्तंभ काढू किंवा जोडू शकता, कारण इच्छित मूल्य असलेले स्तंभ थेट परिभाषित केले आहे. खरंच, हा एक चांगला फायदा आहे, विशेषत: जेव्हा आपल्याला मोठ्या प्रमाणात डेटासह कार्य करावे लागते. तुम्ही वापरत असलेल्या प्रत्येक वैशिष्ट्याचे निराकरण करण्याची काळजी न करता तुम्ही स्तंभ जोडू आणि काढू शकता VLOOKUP.

3. शोधलेल्या मूल्याच्या आकारावर मर्यादा नाही.वापरत आहे VLOOKUP, शोधलेल्या मूल्याच्या लांबीवरील 255-वर्ण मर्यादेची जाणीव ठेवा, अन्यथा तुम्हाला त्रुटी मिळण्याचा धोका आहे #मूल्य!(#मूल्य!). म्हणून, जर टेबलमध्ये लांब पंक्ती असतील तर, वापरणे हा एकमेव व्यवहार्य उपाय आहे INDEX/मॅच.

समजा तुम्ही खालील सूत्र वापरता: VLOOKUP, जे पासून सेलमध्ये शोधते B5आधी D10सेलमध्ये निर्दिष्ट केलेले मूल्य A2:

VLOOKUP(A2,B5:D10,3,FALSE)
=VLOOKUP(A2,B5:D10,3,FALSE)

सेलमध्ये मूल्य असल्यास सूत्र कार्य करणार नाही A2 255 वर्णांपेक्षा मोठे. त्याऐवजी, तुम्हाला एक समान सूत्र वापरण्याची आवश्यकता आहे INDEX/मॅच:

INDEX(D5:D10,MATCH(TRUE,INDEX(B5:B10=A2,0),0))
=INDEX(D5:D10,MATCH(TRUE,INDEX(B5:B10=A2,0),0))

4. उच्च धावण्याचा वेग.जर तुम्ही छोट्या स्प्रेडशीटवर काम करत असाल, तर एक्सेलच्या कार्यक्षमतेतील फरक बहुधा लक्षात येणार नाही, विशेषत: अलीकडील आवृत्त्यांमध्ये. तुम्ही हजारो पंक्ती आणि शेकडो शोध सूत्रे असलेल्या मोठ्या सारण्यांसह काम करत असल्यास, तुम्ही वापरता तेव्हा Excel अधिक वेगाने चालेल मॅचआणि INDEXऐवजी VLOOKUP. सर्वसाधारणपणे, अशी बदली एक्सेलची गती वाढवते 13% .

प्रभाव VLOOKUPवर्कबुकमध्ये शेकडो क्लिष्ट अॅरे फॉर्म्युले असल्यास एक्सेल कामगिरी विशेषतः लक्षात येण्याजोगी असते, जसे की VLOOKUP+SUM. वस्तुस्थिती अशी आहे की अॅरेमधील प्रत्येक मूल्य तपासण्यासाठी स्वतंत्र फंक्शन कॉल आवश्यक आहे VLOOKUP. त्यामुळे, अॅरेमध्ये जितकी अधिक मूल्ये असतील आणि तुमच्या टेबलमध्ये जितके अॅरे सूत्र असतील तितका एक्सेल मंद असेल.

दुसरीकडे, फंक्शन्ससह एक सूत्र मॅचआणि INDEXफक्त एक लुकअप करते आणि परिणाम देते, तेच काम लक्षणीयरीत्या वेगाने करते.

इंडेक्स आणि मॅच - सूत्रांची उदाहरणे

आता तुम्ही फंक्शन्स का शिकले पाहिजेत याची कारणे तुम्हाला समजली आहेत मॅचआणि INDEX, चला सर्वात मनोरंजक गोष्टीकडे जाऊया आणि आपण सराव मध्ये सैद्धांतिक ज्ञान कसे लागू करू शकता ते पाहू.

MATCH आणि INDEX वापरून डाव्या बाजूला कसे शोधायचे

कोणतेही पाठ्यपुस्तक VLOOKUPम्हणते की हे फंक्शन डावीकडे पाहू शकत नाही. त्या. जर वर पाहिलेला स्तंभ शोध श्रेणीतील सर्वात डावीकडे स्तंभ नसेल, तर येथून येण्याची शक्यता नाही VLOOKUPइच्छित परिणाम.

कार्ये मॅचआणि INDEXएक्सेल अधिक लवचिक आहे आणि काढण्यासाठी मूल्य असलेला स्तंभ कुठे आहे याची काळजी घेत नाही. उदाहरणार्थ, राज्यांच्या राजधानी आणि लोकसंख्येच्या टेबलवर परत जाऊ या. यावेळी आपण सूत्र लिहू मॅच/INDEX, जे लोकसंख्येच्या दृष्टीने रशियाची राजधानी (मॉस्को) कोणते स्थान व्यापते हे दर्शवेल.

आपण खालील आकृतीत पाहू शकता, सूत्र या कार्यासाठी एक उत्कृष्ट कार्य करते:

INDEX($A$2:$A$10,MATCH("रशिया",$B$2:$B$10,0))

आता हे सूत्र कसे कार्य करते हे समजून घेण्यात तुम्हाला कोणतीही अडचण नसावी:

• प्रथम, फंक्शन वापरू मॅच(मॅच) ज्याला सूचीमध्ये "रशिया" स्थान सापडते:

  सामना("रशिया",$B$2:$B$10.0))
  =MATCH("रशिया",$B$2:$B$10,0))

• पुढे, फंक्शनसाठी श्रेणी सेट करा INDEX(INDEX) पासून मूल्य पुनर्प्राप्त करण्यासाठी. आमच्या बाबतीत, हे A2:A10.
• मग आम्ही दोन्ही भाग जोडतो आणि सूत्र मिळवतो:

  INDEX($A$2:$A$10;MATCH("रशिया";$B$2:$B$10;0))
  =INDEX($A$2:$A$10,MATCH("रशिया",$B$2:$B$10,0))

सुगावा:योग्य उपाय म्हणजे नेहमी साठी निरपेक्ष संदर्भ वापरणे INDEXआणि मॅचजेणेकरुन फॉर्म्युला इतर सेलमध्ये कॉपी करताना शोध श्रेणी गमावल्या जाणार नाहीत.

Excel मध्ये INDEX आणि MATCH वापरून गणना (सरासरी, MAX, MIN)

तुम्ही इतर एक्सेल फंक्शन्स मध्ये नेस्ट करू शकता INDEXआणि मॅच, उदाहरणार्थ, किमान, कमाल किंवा सरासरी मूल्याच्या सर्वात जवळ शोधण्यासाठी. सूत्रांसाठी येथे काही पर्याय आहेत, जसे की सारणीवर लागू केले आहे:

1. MAX(MAX). सूत्र एका स्तंभात कमाल शोधते डी सीसमान ओळ:

INDEX($C$2:$C$10,MATCH(MAX($D$2:I$10),$D$2:D$10,0))
=INDEX($C$2:$C$10,MATCH(MAX($D$2:I$10),$D$2:D$10,0))

निकाल: बीजिंग

2. मि(MIN). सूत्र एका स्तंभात किमान शोधते डीआणि स्तंभातून मूल्य परत करते सीसमान ओळ:

INDEX($C$2:$C$10,MATCH(MIN($D$2:I$10),$D$2:D$10,0))
=INDEX($C$2:$C$10,MATCH(MIN($D$2:I$10),$D$2:D$10,0))

निकाल: लिमा

3. सरासरी(सरासरी). सूत्र श्रेणीतील सरासरीची गणना करते D2:D10, नंतर त्याच्या सर्वात जवळचा शोधतो आणि स्तंभातून मूल्य परत करतो सीसमान ओळ:

INDEX($C$2:$C$10,MATCH(Average($D$2:D$10),$D$2:D$10,1))
=INDEX($C$2:$C$10,MATCH(Average($D$2:D$10),$D$2:D$10,1))

निकाल: मॉस्को

INDEX आणि MATCH सह AVERAGE फंक्शन वापरताना लक्षात ठेवण्यासारखे मुद्दे

फंक्शन वापरणे सरासरीसह संयोजनात INDEXआणि मॅच, तिसरे फंक्शन आर्ग्युमेंट म्हणून मॅचबर्याचदा आपल्याला निर्दिष्ट करणे आवश्यक आहे 1 किंवा -1 जर तुम्हाला खात्री नसेल की पाहिलेल्या श्रेणीमध्ये सरासरीच्या बरोबरीचे मूल्य आहे. जर तुम्हाला खात्री असेल की असे मूल्य आहे, तर ठेवा 0 अचूक जुळणी शोधण्यासाठी.

• आपण निर्दिष्ट केल्यास 1 , लुकअप स्तंभातील मूल्ये चढत्या क्रमाने क्रमवारी लावली जाणे आवश्यक आहे आणि सूत्र सरासरीपेक्षा कमी किंवा समान कमाल मूल्य परत करेल.
• आपण निर्दिष्ट केल्यास -1 , लुकअप स्तंभातील मूल्ये उतरत्या क्रमाने क्रमवारी लावली जावीत आणि सरासरीपेक्षा मोठे किंवा समान किमान मूल्य परत केले जाईल.

आमच्या उदाहरणात, स्तंभातील मूल्ये डीचढत्या क्रमाने क्रमवारी लावली जाते, म्हणून आम्ही जुळणी प्रकार वापरतो 1 . सुत्र INDEX/शोधपोप"मॉस्को" परत करते कारण मॉस्कोची लोकसंख्या सरासरी (12,269,006) च्या सर्वात जवळची कमी आहे.

ज्ञात पंक्ती आणि स्तंभावर शोधण्यासाठी INDEX आणि MATCH कसे वापरावे

हे सूत्र 2D शोधाच्या समतुल्य आहे VLOOKUPआणि तुम्हाला विशिष्ट पंक्ती आणि स्तंभाच्या छेदनबिंदूवर मूल्य शोधण्याची परवानगी देते.

या उदाहरणात, सूत्र INDEX/मॅचया धड्यात आपण याआधी चर्चा केलेल्या सूत्रांशी अगदी समानता असेल, फक्त एका फरकासह. ओळखा पाहू?

जसे तुम्हाला आठवते, फंक्शन सिंटॅक्स INDEX(INDEX) तीन वितर्कांना अनुमती देते:

INDEX(अरे,पंक्ती_संख्या,)
INDEX(अॅरे, पंक्ती_संख्या, [स्तंभ_संख्या])

आणि तुमच्यापैकी ज्यांनी हे शोधून काढले त्यांचे अभिनंदन!

चला सूत्र टेम्पलेट लिहून सुरुवात करूया. हे करण्यासाठी, आम्ही आधीच परिचित सूत्र घेतो INDEX/मॅचआणि त्यात दुसरे फंक्शन जोडा मॅच, जे स्तंभ क्रमांक परत करेल.

INDEX(तुमचा टेबल ,(MATCH(, शोधण्यासाठी स्तंभ,0)),(मॅच(, शोधण्यासाठी स्ट्रिंग,0))
=INDEX(तुमचा टेबल ,(MATCH( अनुलंब शोधासाठी मूल्य,शोधण्यासाठी स्तंभ,0)),(मॅच( क्षैतिज शोधासाठी मूल्य,शोधण्यासाठी स्ट्रिंग,0))

कृपया लक्षात घ्या की द्विमितीय शोधासाठी, तुम्हाला युक्तिवादात संपूर्ण सारणी निर्दिष्ट करणे आवश्यक आहे रचना(अॅरे) फंक्शन्स INDEX(INDEX).

आता या पॅटर्नची चाचणी घेऊ. खाली तुम्हाला जगातील सर्वाधिक लोकसंख्या असलेल्या देशांची यादी दिसेल. समजा आमचे कार्य 2015 मध्ये युनायटेड स्टेट्सची लोकसंख्या शोधणे आहे.

ठीक आहे, चला सूत्र लिहूया. जेव्हा मला एक्सेलमध्ये नेस्टेड फंक्शन्ससह एक जटिल सूत्र तयार करण्याची आवश्यकता असते, तेव्हा मी प्रथम प्रत्येक नेस्टेड स्वतंत्रपणे लिहितो.

तर दोन फंक्शन्सने सुरुवात करूया मॅच, जे फंक्शनसाठी पंक्ती आणि स्तंभ क्रमांक परत करेल INDEX:

• स्तंभासाठी जुळणी- आम्ही एका स्तंभात पाहत आहोत बी, अधिक अचूक होण्यासाठी, श्रेणीमध्ये B2:B11, सेलमध्ये निर्दिष्ट केलेले मूल्य H2(संयुक्त राज्य). फंक्शन असे दिसेल:

  जुळणी($H$2,$B$1:$B$11.0)
  =MATCH($H$2,$B$1:$B$11,0)

  4 , कारण "USA" हा स्तंभातील सूचीचा 4था घटक आहे बी(शीर्षकासह).

• स्ट्रिंगसाठी जुळणी- आम्ही सेलचे मूल्य शोधत आहोत H3(2015) ओळीत 1 , म्हणजे पेशींमध्ये A1:E1:

  जुळणी($H$3,$A$1:$E$1,0)
  =MATCH($H$3,$A$1:$E$1,0)

  या सूत्राचा परिणाम होईल 5 कारण "2015" 5व्या स्तंभात आहे.

आता आपण फंक्शनमध्ये ही सूत्रे समाविष्ट करू INDEXआणि व्होइला:

INDEX($A$1:$E$11,MATCH($H$2,$B$1:$B$11.0),MATCH($H$3,$A$1:$E$1.0))
=INDEX($A$1:$E$11,MATCH($H$2,$B$1:$B$11,0),MATCH($H$3,$A$1:$E$1,0))

आम्ही फंक्शन्स पुनर्स्थित केल्यास मॅचत्यांनी परत केलेल्या मूल्यांवर, सूत्र सोपे आणि समजण्यासारखे होईल:

इंडेक्स($A$1:$E$11,4,5))
=INDEX($A$1:$E$11,4,5))

हे सूत्र छेदनबिंदूवरील मूल्य परत करते 4 थाओळी आणि 5 वाश्रेणीतील स्तंभ A1:E11, म्हणजेच सेलचे मूल्य E4. फक्त? होय!

INDEX आणि MATCH सह अनेक निकषांनुसार शोधा

पाठ्यपुस्तकात VLOOKUPआम्ही फंक्शनसह सूत्राचे उदाहरण दाखवले VLOOKUPअनेक निकषांनुसार शोधण्यासाठी तथापि, या सोल्यूशनची महत्त्वपूर्ण मर्यादा म्हणजे सहायक स्तंभ जोडण्याची आवश्यकता होती. चांगली बातमी: सूत्र INDEX/मॅचसहाय्यक स्तंभ तयार न करता, दोन स्तंभांमध्ये मूल्यांद्वारे शोधू शकता!

समजा आमच्याकडे ऑर्डरची यादी आहे आणि आम्हाला दोन निकषांनुसार बेरीज शोधायची आहे − खरेदीदाराचे नाव(ग्राहक) आणि उत्पादन(उत्पादन). एक खरेदीदार एकाच वेळी अनेक भिन्न उत्पादने खरेदी करू शकतो आणि पत्रकावरील टेबलमध्ये खरेदीदारांची नावे या वस्तुस्थितीमुळे ही बाब गुंतागुंतीची आहे. शोध टेबलयादृच्छिक क्रमाने व्यवस्था केली.

हे सूत्र आहे INDEX/मॅचसमस्या सोडवते:

(=INDEX("लूकअप टेबल"!$A$2:$C$13,MATCH(1,(A2="लूकअप टेबल"!$A$2:$A$13)*
(B2="लूकअप टेबल"!$B$2:$B$13),0),3))
(=INDEX("लूकअप टेबल"!$A$2:$C$13;MATCH(1;(A2="लूकअप टेबल"!$A$2:$A$13)*
(B2="लूकअप टेबल"!$B$2:$B$13);0);3))

हे सूत्र आम्ही आधी चर्चा केलेल्या इतरांपेक्षा अधिक क्लिष्ट आहे, परंतु कार्यांच्या ज्ञानाने सज्ज आहे INDEXआणि मॅचतुम्ही तिच्यावर मात कराल. सर्वात कठीण भाग म्हणजे कार्य मॅचमला वाटते ते आधी स्पष्ट करणे आवश्यक आहे.

MATCH(1,(A2="लूकअप टेबल"!$A$2:$A$13),0)*(B2="लूकअप टेबल"!$B$2:$B$13)
MATCH(1;(A2="लूकअप टेबल"!$A$2:$A$13);0)*(B2="लूकअप टेबल"!$B$2:$B$13)

वर दर्शविलेल्या सूत्रामध्ये, आपण शोधत असलेले मूल्य आहे 1 , आणि लुकअप अॅरे हा गुणाकाराचा परिणाम आहे. ठीक आहे, मग आपण काय गुणाकार करावे आणि का? चला हे सर्व क्रमाने घेऊ:

• स्तंभातील पहिले मूल्य मिळवा ए(ग्राहक) प्रति पत्रक मुख्य टेबलआणि शीटवरील टेबलमधील सर्व खरेदीदारांच्या नावांशी तुलना करा शोध टेबल(A2:A13).
• जुळणी आढळल्यास, समीकरण परत येते 1 (सत्य), आणि नसल्यास - 0 (खोटे).
• पुढे, आपण स्तंभ मूल्यांसाठी तेच करतो बी(उत्पादन).
• मग आम्ही प्राप्त परिणाम (1 आणि 0) गुणाकार करतो. जर दोन्ही स्तंभांमध्ये जुळणी आढळली तरच (म्हणजे दोन्ही निकष खरे आहेत) तुम्हाला मिळतील 1 . जर दोन्ही निकष खोटे असतील, किंवा त्यापैकी फक्त एक पूर्ण झाला असेल, तर तुम्हाला मिळेल 0 .

आम्ही का विचारले ते आता तुम्हाला समजले आहे 1 इच्छित मूल्य म्हणून? हे कार्य योग्य आहे मॅचदोन्ही निकषांची पूर्तता झाल्यावरच पद परत केले.

टीप:या प्रकरणात, तुम्ही तिसरा पर्यायी फंक्शन आर्ग्युमेंट वापरणे आवश्यक आहे INDEX. ते आवश्यक आहे, कारण पहिल्या युक्तिवादात, आम्ही संपूर्ण सारणी निर्दिष्ट करतो आणि कोणत्या स्तंभातून मूल्य काढायचे हे फंक्शनला सांगितले पाहिजे. आमच्या बाबतीत, हा स्तंभ आहे सी(सम) आणि म्हणून आम्ही ओळख करून दिली 3 .

आणि शेवटी, कारण आपल्याला अॅरेमधील प्रत्येक सेल तपासण्याची आवश्यकता आहे, हे सूत्र अॅरे सूत्र असणे आवश्यक आहे. ते बंद केलेल्या कुरळे ब्रेसेसद्वारे तुम्ही हे पाहू शकता. म्हणून जेव्हा तुम्ही फॉर्म्युला प्रविष्ट करणे पूर्ण कराल, तेव्हा दाबण्यास विसरू नका Ctrl+Shift+Enter.

सर्वकाही योग्यरित्या केले असल्यास, आपल्याला खालील चित्राप्रमाणे परिणाम मिळेल:

INDEX आणि MATCH Excel मध्ये IFERROR सह एकत्रित

जसे तुम्ही आधीच लक्षात घेतले असेल (आणि एकापेक्षा जास्त वेळा), जर तुम्ही चुकीचे मूल्य प्रविष्ट केले असेल, उदाहरणार्थ, जे पाहिल्या जात असलेल्या अॅरेमध्ये नाही, सूत्र INDEX/मॅचत्रुटी नोंदवते #N/A(#N/A) किंवा #मूल्य!(#मूल्य!). जर तुम्हाला अशा संदेशाला अधिक समजण्याजोगे काहीतरी बदलायचे असेल, तर तुम्ही यासह एक सूत्र समाविष्ट करू शकता INDEXआणि मॅचफंक्शन मध्ये एस्लीओशिब्का.

फंक्शन सिंटॅक्स एस्लीओशिब्काखूप सोपे:

IFERROR(मूल्य,मूल्य_जर_त्रुटी)
IFERROR(मूल्य, value_if_error)

कोठें वाद मूल्य(मूल्य) त्रुटीसाठी तपासले जाणारे मूल्य आहे (आमच्या बाबतीत, सूत्राचा परिणाम INDEX/मॅच); आणि युक्तिवाद value_if_error(value_if_error) फॉर्म्युलाने एरर टाकल्यास परत करायचे मूल्य आहे.

उदाहरणार्थ, तुम्ही फंक्शन टाकू शकता एस्लीओशिब्कायाप्रमाणे:

IFERROR(INDEX($A$1:$E$11,MATCH($G$2,$B$1:$B$11.0),MATCH($G$3,$A$1:$E$1.0)),
"कोणतीही जुळणी आढळली नाही. कृपया पुन्हा प्रयत्न करा!") =IFERROR(INDEX($A$1:$E$11,MATCH($G$2,$B$1:$B$11,0),MATCH($G$3,$A$1) :$E$1;0));
"कोणत्याही जुळण्या आढळल्या नाहीत. कृपया पुन्हा प्रयत्न करा!")

आणि आता, जर कोणी चुकीचे मूल्य प्रविष्ट केले तर, सूत्र खालील परिणाम देईल:

एरर आल्यास तुम्ही सेल रिकामा ठेवण्यास प्राधान्य दिल्यास, तुम्ही दुसऱ्या फंक्शन आर्ग्युमेंटचे मूल्य म्हणून अवतरण चिन्ह (“”) वापरू शकता. एस्लीओशिब्का. याप्रमाणे:

IFERROR(INDEX(अॅरे,MATCH(lookup_value, lookup_array,0),"")
IFERROR(INDEX(अॅरे,MATCH(lookup_value,lookup_array,0),"")

मला आशा आहे की या ट्यूटोरियलमध्ये वर्णन केलेले किमान एक सूत्र तुम्हाला उपयुक्त वाटले आहे. जर तुम्हाला इतर शोध कार्ये आली असतील ज्यासाठी तुम्हाला या धड्यातील माहितीमध्ये योग्य उपाय सापडला नाही, तर टिप्पण्यांमध्ये तुमच्या समस्येचे मोकळ्या मनाने वर्णन करा आणि आम्ही सर्व मिळून ते सोडवण्याचा प्रयत्न करू.

समजा तुम्हाला कर्मचार्‍यांचा फोन विस्तार त्यांचा नंबर वापरून शोधणे आवश्यक आहे आणि विक्रीच्या रकमेसाठी कमिशन दराचा अचूक अंदाज लावा. सूचीमधील विशिष्ट डेटा द्रुतपणे आणि कार्यक्षमतेने शोधण्यासाठी आणि डेटा योग्यरित्या वापरला जात आहे की नाही हे आपोआप तपासण्यासाठी तुम्ही डेटा शोधता. तुम्ही डेटा पाहिल्यानंतर, तुम्ही गणना करू शकता आणि रिटर्न व्हॅल्यू निर्दिष्ट करून परिणाम प्रदर्शित करू शकता. डेटाच्या सूचीमध्ये मूल्ये शोधण्याचे आणि परिणाम प्रदर्शित करण्याचे अनेक मार्ग आहेत.

या लेखात

अचूक जुळणी करून अनुलंब सूचीमध्ये मूल्ये शोधणे

हे कार्य पूर्ण करण्यासाठी तुम्ही VLOOKUP फंक्शन किंवा INDEX आणि MATCH फंक्शन्सचे संयोजन वापरू शकता.

VLOOKUP फंक्शन वापरण्याची उदाहरणे

VLOOKUP कार्य.

INDEXES आणि MATCHES ची उदाहरणे

याचा अर्थ काय आहे:

=INDEX(C2:C10 वरून एक मूल्य परत करणे आवश्यक आहे जे MATCH (अॅरे B2:B10 मधील पहिले मूल्य "कोबी") शी जुळेल)

सूत्र सेल C2:C10 मधील पहिले मूल्य शोधते कोबी(B7 मध्ये), आणि C7 मधील मूल्य मिळवते ( 100 ) शी संबंधित पहिले मूल्य आहे कोबी.

अधिक माहितीसाठी, इंडेक्स फंक्शन आणि मॅच फंक्शन पहा.

अंदाजे जुळणीनुसार अनुलंब सूचीमध्ये मूल्ये शोधणे

हे करण्यासाठी, VLOOKUP फंक्शन वापरा.

महत्त्वाचे:पहिल्या रांगेतील मूल्ये चढत्या क्रमाने लावलेली असल्याची खात्री करा.

वरील उदाहरणामध्ये, VLOOKUP फंक्शन A2:B7 या श्रेणीमध्ये 6 टर्डी असलेल्या विद्यार्थ्याचे नाव शोधते. साठी टेबलमध्ये कोणतीही नोंद नाही 6 उशिरा, म्हणून VLOOKUP 6 च्या खाली पुढील सर्वोच्च जुळणी शोधते आणि पहिल्या नावाशी संबंधित मूल्य 5 शोधते डेव्ह, आणि म्हणून परत येतो डेव्ह.

अधिक माहितीसाठी, VLOOKUP कार्य विभाग पहा.

अचूक जुळणीसह अज्ञात आकाराच्या सूचीमध्ये अनुलंब मूल्ये शोधणे

हे कार्य पूर्ण करण्यासाठी, OFFSET आणि MATCH कार्ये वापरा.

टीप:तुम्ही दररोज अपडेट करत असलेल्या बाह्य डेटा श्रेणीमध्ये डेटा असल्यास हा दृष्टिकोन वापरला जातो. तुम्हाला माहिती आहे की कॉलम B ची किंमत आहे, परंतु सर्व्हर डेटाच्या किती पंक्ती परत करत आहे हे तुम्हाला माहिती नाही आणि पहिला कॉलम वर्णक्रमानुसार क्रमवारी लावलेला नाही.

C1श्रेणीचा वरचा डावा सेल आहे (याला स्टार्ट सेल देखील म्हणतात).

जुळणी("संत्रा"; C2: C7; 0) C2:C7 श्रेणीमध्ये नारिंगी रंग शोधतो. तुम्ही सुरुवातीचा सेल श्रेणीमध्ये समाविष्ट करू नये.

1 - स्टार्ट सेलच्या उजवीकडे असलेल्या स्तंभांची संख्या ज्यासाठी रिटर्न व्हॅल्यू परत केले जावे. आमच्या उदाहरणात, रिटर्न व्हॅल्यू स्तंभ D मध्ये आहे, विक्री.

अचूक जुळणी करून क्षैतिजरित्या सूचीमध्ये मूल्ये शोधणे

हे कार्य करण्यासाठी, HLOOKUP फंक्शन वापरले जाते. खाली एक उदाहरण आहे.

HLOOKUP फंक्शन स्तंभ शोध करते विक्रीआणि निर्दिष्ट श्रेणीतील ओळ 5 वरून मूल्य परत करते.

अधिक माहितीसाठी, HLOOKUP फंक्शन्स विभाग पहा.

लुकअप विझार्ड वापरून लुकअप फॉर्म्युला तयार करा (फक्त एक्सेल 2007)

टीप:लुकअप विझार्ड अॅड-इन एक्सेल 2010 मध्ये नापसंत केले गेले आहे. ही कार्यक्षमता फंक्शन विझार्ड आणि उपलब्ध शोध आणि संदर्भ (संदर्भ) फंक्शन्सने बदलली आहे.

एक्सेल 2007 मध्ये, लुकअप विझार्ड वर्कशीट डेटावर आधारित एक लुकअप फॉर्म्युला तयार करतो ज्यामध्ये पंक्ती आणि स्तंभ शीर्षके आहेत. लुकअप विझार्ड तुम्हाला एका स्तंभातील मूल्य माहित असताना आणि त्याउलट सलग इतर मूल्ये शोधण्यात मदत करतो. लुकअप विझार्ड तयार केलेल्या सूत्रांमध्ये अनुक्रमणिका आणि MATCH वापरतो.

एक्सेल ऑफिस प्रोग्रामचा मुख्य उद्देश म्हणजे गणनेची अंमलबजावणी करणे. या प्रोग्रामच्या दस्तऐवजात (पुस्तक) संख्या, मजकूर किंवा सूत्रांनी भरलेल्या लांब सारण्यांसह अनेक पत्रके असू शकतात. स्वयंचलित द्रुत शोध आपल्याला त्यांच्यामध्ये आवश्यक सेल शोधण्याची परवानगी देतो.

साधा शोध

एक्सेल स्प्रेडशीटमध्ये मूल्य शोधण्यासाठी, होम टॅबवर, शोधा आणि बदला साधनाची ड्रॉप-डाउन सूची उघडा आणि आयटम शोधा क्लिक करा. कीबोर्ड शॉर्टकट Ctrl + F वापरून समान प्रभाव प्राप्त केला जाऊ शकतो.

सर्वात सोप्या प्रकरणात, दिसत असलेल्या शोधा आणि बदला विंडोमध्ये, आपण शोधत असलेले मूल्य प्रविष्ट करा आणि सर्व शोधा क्लिक करा.

तुम्ही बघू शकता, शोध परिणाम डायलॉग बॉक्सच्या तळाशी दिसू लागले आहेत. सापडलेली मूल्ये टेबलमध्ये लाल रंगात अधोरेखित केली आहेत. "सर्व शोधा" ऐवजी तुम्ही "पुढील शोधा" वर क्लिक केल्यास, या मूल्यासह प्रथम सेल शोधला जाईल आणि जेव्हा तुम्ही पुन्हा क्लिक कराल तेव्हा दुसरा.

मजकूर त्याच प्रकारे शोधला जातो. या प्रकरणात, शोध बारमध्ये शोध मजकूर टाइप केला जातो.

संपूर्ण एक्सेल स्प्रेडशीटमध्ये डेटा किंवा मजकूर शोधला नसल्यास, प्रथम शोध क्षेत्र निवडणे आवश्यक आहे.

प्रगत शोध

समजा तुम्हाला 3000 आणि 3999 मधील सर्व मूल्ये शोधायची आहेत. या प्रकरणात, तुम्ही शोध स्ट्रिंगमध्ये 3??? टाइप कराल. वाइल्डकार्ड "?" इतर कोणत्याही बदलते.

केलेल्या शोधाच्या परिणामांचे विश्लेषण करताना, हे लक्षात घेतले जाऊ शकते की, योग्य 9 परिणामांसह, प्रोग्रामने लाल रंगात अधोरेखित केलेले अनपेक्षित देखील तयार केले. ते सेल किंवा सूत्रामध्ये क्रमांक 3 च्या उपस्थितीशी संबंधित आहेत.

चुकीच्या गोष्टींकडे दुर्लक्ष करून, मिळालेल्या बहुतांश निकालांवर तुम्ही समाधानी राहू शकता. परंतु एक्सेल 2010 मधील शोध कार्य अधिक अचूकपणे कार्य करण्यास सक्षम आहे. डायलॉग बॉक्समधील हे पर्याय टूल आहे.

"पर्याय" वर क्लिक करून, वापरकर्ता प्रगत शोध करण्यास सक्षम आहे. सर्व प्रथम, "शोध क्षेत्र" आयटमकडे लक्ष द्या, ज्यामध्ये "सूत्र" मूल्य डीफॉल्टनुसार सेट केले आहे.

याचा अर्थ असा की शोध केला गेला, ज्यामध्ये मूल्य नाही, परंतु एक सूत्र आहे अशा सेलसह. त्यापैकी 3 क्रमांकाच्या उपस्थितीने तीन चुकीचे निकाल दिले. तुम्ही शोध स्कोप म्हणून "मूल्ये" निवडल्यास, फक्त डेटा शोधला जाईल आणि फॉर्म्युला सेलशी संबंधित चुकीचे परिणाम अदृश्य होतील.

पहिल्या ओळीवर फक्त उरलेल्या चुकीच्या निकालापासून मुक्त होण्यासाठी, प्रगत शोध विंडोमध्ये, "संपूर्ण सेल" आयटम निवडा. त्यानंतर, शोध परिणाम 100% अचूक होतो.

असा परिणाम ताबडतोब “संपूर्ण सेल” आयटम निवडून प्राप्त केला जाऊ शकतो (“शोध क्षेत्र” मध्ये “सूत्र” मूल्य सोडून देखील).

आता "शोध" आयटमकडे वळूया.

डीफॉल्ट "शीटवर" ऐवजी तुम्ही "पुस्तकात" मूल्य निवडल्यास, तुम्ही शोधत असलेल्या सेलच्या शीटवर असण्याची गरज नाही. स्क्रीनशॉट दाखवतो की वापरकर्त्याने रिकाम्या शीट 2 वर असताना शोध सुरू केला.

प्रगत शोध विंडोमधील पुढील आयटम "ब्राउझ" आहे, ज्याचे दोन अर्थ आहेत. डीफॉल्टनुसार, ते "पंक्तीद्वारे" वर सेट केले आहे, ज्याचा अर्थ पंक्तींद्वारे सेल स्कॅन करण्याचा क्रम आहे. दुसरे मूल्य निवडणे - "स्तंभांनुसार", केवळ शोधाची दिशा आणि परिणामांचा क्रम बदलेल.

Microsoft Excel दस्तऐवजांमध्ये शोधताना, तुम्ही दुसरे वाइल्डकार्ड देखील वापरू शकता - "*". विचार केला तर "?" कोणत्याही वर्णाचा अर्थ, नंतर "*" एक नव्हे तर कितीही वर्ण बदलतो. खाली लुईझियाना शोधाचा स्क्रीनशॉट आहे.

कधीकधी शोध केस-संवेदनशील असणे आवश्यक आहे. लुझियाना हा शब्द कॅपिटल केला तर शोध परिणाम बदलणार नाहीत. परंतु आपण प्रगत शोध विंडोमध्ये "केस सेन्सिटिव्ह" निवडल्यास, शोध अयशस्वी होईल. कार्यक्रम लुईझियाना आणि लुईझियाना या शब्दांचा विचार करेल आणि अर्थातच, त्यापैकी पहिला शब्द सापडणार नाही.

वाण शोधा

जुळण्या शोधत आहे

कधीकधी टेबलमधील डुप्लिकेट मूल्ये शोधणे आवश्यक असते. जुळण्या शोधण्यासाठी, तुम्हाला प्रथम शोध श्रेणी हायलाइट करणे आवश्यक आहे. त्यानंतर, त्याच होम टॅबवर, शैली गटामध्ये, कंडिशनल फॉरमॅटिंग टूल उघडा. पुढे, “सेल निवडीचे नियम” आणि “डुप्लिकेट व्हॅल्यूज” या आयटमची क्रमशः निवड करा.

परिणाम खालील स्क्रीनशॉटमध्ये दर्शविला आहे.

आवश्यक असल्यास, वापरकर्ता जुळलेल्या सेलचा व्हिज्युअल डिस्प्ले रंग बदलू शकतो.

गाळणे

शोधाचा दुसरा प्रकार म्हणजे फिल्टरिंग. समजा वापरकर्त्याला स्तंभ B मध्ये 3000 आणि 4000 मधील संख्यात्मक मूल्ये शोधायची आहेत.

जसे आपण पाहू शकता, प्रविष्ट केलेल्या स्थितीचे समाधान करणार्या फक्त पंक्ती प्रदर्शित केल्या जातात. बाकी सर्व तात्पुरते लपलेले होते. प्रारंभिक स्थितीकडे परत येण्यासाठी, चरण 2 पुन्हा करा.

उदाहरण म्हणून एक्सेल 2010 वापरून विविध शोध पर्यायांचा विचार केला गेला. इतर आवृत्त्यांच्या एक्सेलमध्ये कसे शोधायचे? आवृत्ती 2003 मध्ये फिल्टरिंगच्या संक्रमणामध्ये फरक आहे. "डेटा" मेनूमध्ये, "फिल्टर", "ऑटोफिल्टर", "कंडिशन" आणि "वापरकर्ता ऑटोफिल्टर" क्रमाने आदेश निवडा.

व्हिडिओ: एक्सेल स्प्रेडशीटमध्ये शोधा