INDEX के साथ दो सटीक-मिलान MATCH फ़ंक्शन का उपयोग करके वह मात्रा पाएँ जहाँ किसी एसेट की पंक्ति किसी फ़ॉर्मैट के स्तंभ से मिलती है। इस गाइड में एक छोटी आर्ट-एसेट वर्कशीट बनाई गई है, फ़ॉर्मूला समझाया गया है और Mac के समर्थित संस्करणों के लिए नेस्टेड XLOOKUP विकल्प दिखाया गया है।

Excel for Mac में कौन-सा लुकअप तरीका काम करता है?

इस काम में आपके पास एसेट का नाम, जैसे Dot Grid, और फ़ॉर्मैट, जैसे PNG, होता है। आप इन दोनों के प्रतिच्छेद पर मौजूद एक मात्रा चाहते हैं। इसे आम तौर पर दो-तरफ़ा लुकअप कहा जाता है।

Microsoft के अनुसार INDEX और MATCH, Excel for Microsoft 365 for Mac, Excel 2024 for Mac और Excel 2021 for Mac में उपलब्ध हैं। इस लेख के दोनों तरीके Mac के इसी दस्तावेज़ित दायरे में काम करते हैं। Microsoft का INDEX फ़ंक्शन दस्तावेज़ और MATCH फ़ंक्शन दस्तावेज़ देखें।

XLOOKUP भी Mac के इन संस्करणों के लिए दस्तावेज़ित है। Microsoft स्पष्ट रूप से कहता है कि यह Excel 2016 और Excel 2019 में उपलब्ध नहीं है, भले ही ये संस्करण पृष्ठ की व्यापक लागू होने वाली सूची में दिखाई देते हों। यदि आपके संस्करण में XLOOKUP नहीं है, तो INDEX और MATCH वाला तरीका अपनाएँ। Microsoft: XLOOKUP फ़ंक्शन

अगर सीखते समय आप पंक्ति और स्तंभ की स्थितियाँ अलग-अलग देखना चाहते हैं, तो INDEX और MATCH से शुरू करें। यदि आप पहले से इस फ़ंक्शन का उपयोग करते हैं और इसका डिफ़ॉल्ट सटीक-मिलान पसंद करते हैं, तो नेस्टेड XLOOKUP चुनें।

छोटी आर्ट-एसेट मात्रा ग्रिड सेट अप करें

खाली वर्कशीट में, A1:D5 में नीचे दिया गया काल्पनिक डेटा दर्ज करें। नीचे दी गई तालिका का सबसे बायाँ स्तंभ वर्कशीट के स्तंभ A को दर्शाता है; पंक्ति संख्याओं के लिए अलग स्तंभ न जोड़ें। ये मात्राएँ केवल उदाहरण के लिए हैं।

एसेट SVG PNG JPG
Tile Arc 12 18 6
Dot Grid 8 24 10
Loop Ribbon 15 9 0
Soft Square 7 14 5

हर एसेट एक पंक्ति में और हर फ़ॉर्मैट एक स्तंभ में है। इस उदाहरण के लिए, एसेट के अलग-अलग नाम और फ़ॉर्मैट के अलग-अलग शीर्षक रखें। जहाँ मात्रा निश्चित रूप से शून्य हो, वहाँ स्पष्ट रूप से संख्यात्मक शून्य दर्ज करें; जिन मात्राओं की पुष्टि नहीं हुई है, उन्हें ग्रिड का उपयोग करने से पहले जाँच के लिए खाली छोड़ें।

इसी वर्कशीट में एक अलग लुकअप क्षेत्र बनाएँ:

सेल दर्ज करें
F1 खोजने के लिए एसेट
F2 Dot Grid
G1 खोजने के लिए फ़ॉर्मैट
G2 PNG
H1 मात्रा

तीनों स्रोत रेंज के काम अलग-अलग हैं: A2:A5 में एसेट के नाम हैं, B1:D1 में फ़ॉर्मैट के शीर्षक हैं और B2:D5 में केवल मात्राएँ हैं। शीर्षकों को मात्रा वाली रेंज से बाहर रखने पर सापेक्ष स्थितियाँ सही ढंग से मिलती हैं।

INDEX और दो MATCH फ़ंक्शन से मात्रा पाएँ

H2 चुनें, यह पूरा फ़ॉर्मूला दर्ज करें और Return दबाएँ:

=INDEX($B$2:$D$5,MATCH(F2,$A$2:$A$5,0),MATCH(G2,$B$1:$D$1,0))

नतीजा 24 होना चाहिए: PNG स्तंभ में Dot Grid की मात्रा। Mac के लिए Microsoft के निर्देशों में परिणाम वाला सेल चुनना, = से शुरू होने वाला फ़ॉर्मूला दर्ज करना और Return दबाना बताया गया है। Microsoft: फ़ॉर्मूला दर्ज करें

दोनों स्थितियाँ समझें

पहला MATCH, A2:A5 में एसेट की स्थिति खोजता है; दूसरा B1:D1 में फ़ॉर्मैट की स्थिति खोजता है। आखिरी 0 सटीक मिलान माँगता है। यह तर्क न देने पर MATCH क्रमबद्ध डेटा की शर्त के साथ अनुमानित मिलान करता है। Microsoft: MATCH फ़ंक्शन

दिए गए इनपुट के लिए, अंदर की दोनों गणनाएँ हैं:

=MATCH(F2,$A$2:$A$5,0)

नतीजा: 2।

=MATCH(G2,$B$1:$D$1,0)

नतीजा: 2।

इसलिए पूरा फ़ॉर्मूला मात्रा वाले ब्लॉक में पंक्ति 2, स्तंभ 2 चुनता है:

=INDEX($B$2:$D$5,2,2)

यह वर्कशीट का सेल C3 है, जिसमें 24 है। INDEX दी गई रेंज के भीतर की स्थितियों का उपयोग करता है, इसलिए यहाँ उसकी दूसरी पंक्ति वर्कशीट की पंक्ति 3 है। Microsoft के दस्तावेज़ के अनुसार, दोनों स्थितियाँ देने पर उनके प्रतिच्छेद का मान मिलता है। Microsoft: INDEX फ़ंक्शन

फ़ॉर्मूला कॉपी करते समय स्रोत रेंज स्थिर रखें

डॉलर के चिह्न स्रोत रेंज को लॉक करते हैं। इनपुट के संदर्भ F2 और G2 सापेक्ष रहते हैं, इसलिए H2 को नीचे H3 तक कॉपी करने पर अगला फ़ॉर्मूला उसी स्रोत ग्रिड का उपयोग करते हुए F3 और G3 पढ़ता है। Mac पर, आप फ़ॉर्मूला बार में किसी संदर्भ को चुनकर संदर्भ के प्रकार बदलने के लिए Command+T भी दबा सकते हैं। Microsoft: सापेक्ष और निरपेक्ष संदर्भों के बीच स्विच करें

उदाहरण के लिए, F3 में Tile Arc और JPG को G3 में दर्ज करें, फिर H2 को H3 में कॉपी करें। नया नतीजा 6 होना चाहिए। इससे मूल एसेट ग्रिड बदले बिना अनुरोधों की एक छोटी सूची मिलती है।

Mac के समर्थित संस्करणों में नेस्टेड XLOOKUP का उपयोग करें

उसी वर्कशीट और लुकअप इनपुट के साथ, आप इसके बजाय H2 में यह फ़ॉर्मूला दर्ज कर सकते हैं:

=XLOOKUP(F2,$A$2:$A$5,XLOOKUP(G2,$B$1:$D$1,$B$2:$D$5))

यह Dot Grid और PNG के लिए 24 लौटाता है। Microsoft के दस्तावेज़ में नेस्टेड XLOOKUP का एक उदाहरण है, जो प्रतिच्छेद का मान लौटाने के लिए लंबवत और क्षैतिज, दोनों लुकअप करता है। सटीक मिलान डिफ़ॉल्ट है। Microsoft: XLOOKUP फ़ंक्शन

इस उदाहरण में, अंदर का लुकअप PNG मात्रा वाला स्तंभ चुनता है: 18, 24, 9 और 14। बाहरी लुकअप Dot Grid के अनुरूप प्रविष्टि चुनता है। वही तीन स्रोत रेंज रखने से इस तरीके की तुलना पहले वाले फ़ॉर्मूले से करना आसान रहता है।

बड़ी ग्रिड का उपयोग करने से पहले नतीजा जाँचें

छोटी स्वीकृति-जाँच के रूप में नीचे दिए गए मामलों का उपयोग करें। हर पंक्ति के लिए F2 और G2 बदलें, फिर H2 की तुलना अपेक्षित नतीजे से करें। मूल उदाहरण बनाए रखना हो, तो बाद में Dot Grid और PNG फिर दर्ज करें।

एसेट इनपुट फ़ॉर्मैट इनपुट अपेक्षित नतीजा जाँच
Dot Grid PNG 24 बीच का प्रतिच्छेद
Tile Arc JPG 6 पहला एसेट और आखिरी फ़ॉर्मैट
Soft Square SVG 7 आखिरी एसेट और पहला फ़ॉर्मैट
Loop Ribbon JPG 0 स्पष्ट रूप से दर्ज शून्य मात्रा
Tile Arcs PNG #N/A एसेट का नाम मौजूद नहीं
Dot Grid TIFF #N/A फ़ॉर्मैट का शीर्षक मौजूद नहीं

अगर कोई संख्या गलत है, तो लुकअप का तरीका बदलने से पहले रेंज की सीमाएँ जाँचें। इस ग्रिड के लिए, A2:A5 को B2:D5 की चार पंक्तियों के साथ और B1:D1 को उसके तीन स्तंभों के साथ संरेखित होना चाहिए। उदाहरण के लिए, केवल एसेट लुकअप रेंज में A1 शामिल करने पर गणना की गई स्थिति इच्छित मात्रा वाली पंक्ति से हट जाएगी।

इस खास ग्रिड में पंक्ति 6 पर एक और एसेट जोड़ते समय, एसेट रेंज को A2:A6 और मात्रा वाले ब्लॉक को B2:D6 तक बढ़ाएँ। स्तंभ E में फ़ॉर्मैट जोड़ते समय, शीर्षकों वाली रेंज को B1:E1 और मात्रा वाले ब्लॉक को B2:E5 तक बढ़ाएँ। इन दोनों संबंधित बदलावों से फ़ॉर्मूलों के लिए इस्तेमाल किया गया लेआउट बना रहता है।

एक जैसे नामों पर भी ध्यान दें। सटीक-मिलान वाला MATCH पहली मेल खाती स्थिति लौटाता है और बड़े व छोटे अक्षरों में अंतर नहीं करता; यह टेक्स्ट खोजों में * और ? को वाइल्डकार्ड की तरह भी समझता है। इस सेटअप में ऐसे अलग नाम रखें जिनमें ये चिह्न न हों। Microsoft: MATCH फ़ंक्शन

अनुपलब्ध नाम के लिए उपयोगी संदेश दिखाएँ

बुनियादी लुकअप काम करने लगे, तो आप मेल न मिलने की त्रुटि को पढ़ने योग्य संदेश से बदल सकते हैं:

=IFNA(INDEX($B$2:$D$5,MATCH(F2,$A$2:$A$5,0),MATCH(G2,$B$1:$D$1,0)),"Check asset or format")

IFNA में दिया फ़ॉर्मूला #N/A नतीजा देता है, तो आपका संदेश लौटाता है; अन्यथा वह नतीजा बनाए रखता है। यह Microsoft 365 for Mac, Excel 2024 for Mac और Excel 2021 for Mac के लिए दस्तावेज़ित है। Microsoft: IFNA फ़ंक्शन

इस उदाहरण में, Tile Arcs से एसेट या फ़ॉर्मैट जाँचें मिलता है, जबकि Loop Ribbon के साथ JPG देने पर अब भी 0 लौटता है। इस अंतर से अनुपलब्ध नाम और दर्ज की गई शून्य मात्रा अलग रहते हैं। यदि स्रोत मात्रा में ही #N/A है, तो यह रैपर उस त्रुटि को भी बदल देगा; इसलिए जब नाम सही दिखें, तो स्रोत सेल की जाँच करें।

रोज़मर्रा के उपयोग के लिए, एसेट के नाम अलग-अलग रखें, तीनों स्रोत रेंज का संरेखण बनाए रखें और ग्रिड बदलने के बाद कुछ ज्ञात प्रतिच्छेद जाँचें। फिर आप एसेट और फ़ॉर्मैट के इनपुट बदलकर हर अनुरोधित आर्ट-एसेट मात्रा पा सकते हैं।

स्रोत