लुकअप फंक्शन डेटा पुनर्प्राप्ति को कुशल बनाते हैं। एक्सेल कई लुकअप विकल्प प्रदान करता है, और सही रणनीति चुनना मुश्क हो सकता है। इष्टतम दृष्टिकोण चुनने से सबसे विश्वसनीय परिणाम मिलता है। इस ट्यूटोरियल में, हम एक्सेल लुकअप फंक्शन फ्रेमवर्क बनाते हैं सही रणनीति चुनने के लिए। यह फ्रेमवर्क अनुभवी उपयोगकर्ताओं को डेटा आकार, पैमाने और इरादे के आधार पर यह तय करने में मदद करता है कि कौन सा दृष्टिकोण सबसे उपयुक्त है।
आप सही लुकअप रणनीति चुनने के लिए उपरोक्त फ्लोचार्ट का पालन कर सकते हैं।
XLOOKUP: वन-टू-वन लुकअप के लिए सर्वश्रेष्ठ (मॉडर्न एक्सेल)
पहले, तय करें कि आप क्या खोज रहे हैं। क्या आप एक मिलान रिकॉर्ड पुनर्प्राप्त कर रहे हैं, या आपको अपने मानदंड से मेल खाने वाले सभी रिकॉर्ड चाहिए?
XLOOKUP का उपयोग तब करें जब आप एक परिणाम, पठनीय फॉर्मूला, और वैकल्पिक "नहीं मिला" हैंडलिंग चाहते हैं।
एक नमूना बिक्री डेटासेट पर विचार करें जहां ऑर्डर, उत्पाद विवरण, और ग्राहक जानकारी अलग-अलग टेबल में रहती हैं। हम विभिन्न टेबल से बिक्री जानकारी पुनर्प्राप्त करने के लिए लुकअप रणनीतियों का पता लगाते हैं।
Products में UnitPrice लुकअप करें:
=XLOOKUP(D2, Products!$A$2:$A$7, Products!$D$2:$D$7, "Not found")
टेबल फॉर्मेट में समान फॉर्मूला:
=XLOOKUP(Orders[@SKU], Products[SKU], Products[UnitPrice], "SKU not found")
Orders में CustomerName खोजें:
=XLOOKUP(Orders[@CustomerID], Customers[CustomerID], Customers[CustomerName], "Customer not found")
ये फॉर्मूला विभिन्न टेबल में मानों को देखते हैं और एक मिलान रिकॉर्ड लौटाते हैं। XLOOKUP मॉडर्न एक्सेल में वन-टू-वन लुकअप के लिए आपका डिफ़ॉल्ट विकल्प हो सकता है। यह प्रदान करता है:
- if_not_found आर्गुमेंट के माध्यम से अंतर्निहित त्रुटि हैंडलिंग के साथ साफ, पठनीय सिंटैक्स
- बाएं और द्विदिश लुकअप के लिए मूल समर्थन
- आर्केन मैच-टाइप पैरामीटर के बिना सटीक और सन्निकट मिलान मोड
XLOOKUP का उपयोग करें जब आपके पास माइक्रोसॉफ्ट 365 या एक्सेल 2021+ हो और सीधे सिंगल-वैल्यू पुनर्प्राप्ति की आवश्यकता हो।
INDEX/MATCH: संगतता के लिए सर्वश्रेष्ठ और अभी भी शक्तिशाली
INDEX/MATCH चुनें यदि आपकी फाइल को पुराने एक्सेल संस्करणों में काम करना है, या जब आपकी टीम का मानक INDEX/MATCH है।
INDEX/MATCH के माध्यम से UnitPrice:
=INDEX(Products[UnitPrice], MATCH(Orders[@SKU], Products[SKU], 0))
यह फॉर्मूला पुराने संस्करणों के लिए एक ठोस विकल्प बना हुआ है, लेकिन कई टीमों के लिए XLOOKUP की तुलना में पढ़ना आसान नहीं है और बड़े पैमाने पर बनाए रखना कठिन है।
FILTER: वन-टू-मैनी परिणामों के लिए सर्वश्रेष्ठ (स्पिल्ड सूचियाँ)
FILTER चुनें जब आप रिकॉर्ड की एक डायनामिक सूची चाहते हैं जो स्वचालित रूप से अपडेट होती है। यह मानदंडों के आधार पर डेटा का एक सबसेट निकालता है और उन परिणामों को स्वचालित रूप से अपडेट रखता है। FILTER वन-टू-मैनी पुनर्प्राप्ति के लिए उद्देश्य-निर्मित है:
- सभी मिलान वाली पंक्तियों को एक डायनामिक स्पिल ऐरे के रूप में लौटाता है
- बूलियन AND/OR तर्क के साथ कई मानदंडों को संभालता है
- स्रोत डेटा बदलने पर स्वचालित रूप से विस्तार और संकुचन करता है
- अन्य डायनामिक ऐरे फंक्शन के साथ सहजता से एकीकृत होता है
एक CustomerID के लिए सभी ऑर्डर दिखाएं:
=FILTER(Orders, Orders[CustomerID]=H2, "No orders found")
SKU P-101 के लिए सभी पश्चिम क्षेत्र ऑर्डर:
=FILTER(Orders, (Orders[Region]=H9)*(Orders[SKU]=H7), "No matches")
ये फॉर्मूला XLOOKUP से भिन्न हैं क्योंकि FILTER एक सिंगल वैल्यू नहीं लौटाता—यह एक स्पिल्ड, टेबल-आकार का उत्तर लौटाता है। यह XLOOKUP/INDEX-MATCH से अलग काम है।
पावर क्वेरी: सर्वश्रेष्ठ जब लुकअप वास्तव में एक "जॉइन" हो (डेटा मॉडलिंग)
पावर क्वेरी बेहतर विकल्प बन जाती है जब आपको फ़िल्टर्ड परिणामों पर परिवर्तन करने की आवश्यकता होती है। इसका उपयोग करें जब,
- डेटा बड़ा हो,
- डेटा बाहरी स्रोतों से आता है और रिफ्रेश होना चाहिए,
- आप एक दोहराने योग्य परिवर्तन पाइपलाइन चाहते हैं,
- आप PivotTables/डैशबोर्ड के लिए एक साफ, मर्ज की गई टेबल बनाना चाहते हैं।
आइए एक टेबल बनाएं जिसमें उत्पाद और ग्राहक कॉलम शामिल हों (UnitPrice, Product, CustomerName, Segment)।
चरण (मर्ज/जॉइन):
- रेंज को टेबल में बदलें ( CTRL+T दबाएं)। हमारा डेटा पहले से ही टेबल फॉर्मेट में है।
- डेटा टैब >> डेटा प्राप्त करें >> टेबल/रेंज से चुनें (ऑर्डर से शुरू करें)।
- पावर क्वेरी एडिटर में: होम >> क्वेरी मर्ज करें चुनें
- ऑर्डर को उत्पाद टेबल के साथ मर्ज करें
- ऑर्डर[SKU] और उत्पाद[SKU] चुनें
- जॉइन प्रकार: लेफ्ट आउटर चुनें (सभी ऑर्डर रखें)
- OK पर क्लिक करें
-
- विस्तार आइकन पर क्लिक करें >> उत्पाद, श्रेणी, और UnitPrice चुनें
- OK पर क्लिक करें
- ऑर्डर को ग्राहक टेबल के साथ मर्ज करें
- ऑर्डर[CustomerID] को ग्राहक[CustomerID] से मिलान करें
-
- विस्तार आइकन पर क्लिक करें >> CustomerName, Segment चुनें
- OK पर क्लिक करें
- बंद करें और लोड करें पर क्लिक करके डेटा को एक्सेल में लोड करें।
यहां नया ऑर्डर-समृद्ध टेबल है, जिसमें उत्पाद और ग्राहक विवरण शामिल हैं।
आप एक बार मर्ज करते हैं, कभी भी रिफ्रेश करते हैं, और हजारों पंक्तियों और कई कॉलम में फॉर्मूला दोहराने से बचते हैं।
वन-टू-मैनी के लिए FILTER और पावर क्वेरी के बीच चयन
दोनों FILTER और पावर क्वेरी उन्नत, स्वचालित परिणाम प्रदान करते हैं। कभी-कभी उपयोगकर्ता वन-टू-मैनी के लिए एक चुनने में दुविधा का सामना करते हैं।
- FILTER चुनें जब एक इंटरैक्टिव शीट अनुभव की आवश्यकता हो। मानदंड सेल बदलें, और सूची तुरंत अपडेट होती है। यह इंटरैक्टिव डैशबोर्ड के लिए उपयोगी है।
- पावर क्वेरी चुनें जब रिपोर्टिंग (PivotTables, चार्ट, मॉडल टेबल) को खिलाने के लिए एक रिफ्रेश करने योग्य डेटासेट की आवश्यकता हो, विशेष रूप से बड़े डेटा के साथ।
टिप्स और ट्रिक्स
- यदि आउटपुट एक सेल है, तो XLOOKUP चुनें (या संगतता के लिए INDEX/MATCH)।
- यदि आउटपुट एक सूची/टेबल है जो स्वचालित रूप से बढ़नी चाहिए, तो FILTER चुनें।
- यदि कार्य मूल रूप से डेटासेट जोड़ रहा है और रिफ्रेश करने योग्य/स्केलेबल होना चाहिए, तो पावर क्वेरी का उपयोग करें।
- यदि आप कई कॉलम (मूल्य, श्रेणी, नाम, सेगमेंट…) में वही लुकअप कर रहे हैं, तो हम फॉर्मूला दोहराने के बजाय पावर क्वेरी मर्ज की पुरजोर सलाह देते हैं।
निष्कर्ष
यह ट्यूटोरियल फ्लोचार्ट आपको सही लुकअप रणनीति चुनने में मदद करता है। पहले तय करें कि आपके डेटा को किस प्रकार के लुकअप की आवश्यकता है। कोई सार्वभौमिक "सर्वश्रेष्ठ" लुकअप फंक्शन नहीं है—संदर्भ इष्टतम दृष्टिकोण निर्धारित करता है। फ्रेमवर्क में महारत हासिल करें, न कि केवल फंक्शन में, और आपकी लुकअप रणनीतियाँ आपकी आवश्यकताओं के साथ स्केल होंगी। सही लुकअप विधि चुनने की रणनीतिक समझ विकसित करें, और आपके समाधान तेजी से बनेंगे, बनाए रखने में आसान होंगे, और आवश्यकताओं के विकसित होने पर अधिक मजबूत होंगे।
मुफ्त एडवांस्ड एक्सेल अभ्यास समाधान के साथ प्राप्त करें!
