Excel में VLOOKUP() को IF() के साथ कैसे संयोजित करें

यदि आप Excel में बड़े डेटासेट प्रबंधित करते हैं, तो आप जानते हैं कि उन्हें कुशलता से व्यवस्थित और विश्लेषित करना कितना चुनौतीपूर्ण होता है। लेकिन मेरे पास आपके लिए समाधान है: आप IF() स्टेटमेंट के भीतर VLOOKUP() फ़ंक्शन को नेस्ट करके विशिष्ट शर्तों के आधार पर डायनेमिक लुकअप बना सकते हैं। इस तरीके से, आप किसी शर्त के आधार पर अलग-अलग तालिकाओं में खोज कर सकते हैं और त्रुटियों को भी अधिक सुव्यवस्थित तरीके से संभाल सकते हैं।

विशेष रूप से, VLOOKUP() किसी तालिका के भीतर विशिष्ट डेटा पॉइंट्स ढूंढता है, और IF() स्टेटमेंट आपको उस डेटा के आधार पर शर्तित निर्णय लेने देता है। जैसा कि हम जानते हैं, दोनों फ़ंक्शन अत्यंत महत्त्वपूर्ण हैं। इस लेख में, हम टूलबॉक्स में एक और टूल जोड़ेंगे: आप सीखेंगे कि इन दोनों फ़ंक्शनों की शक्ति को साथ मिलाकर शर्तित लुकअप कैसे करें, और हम इसे व्यावहारिक उदाहरणों से समझाएँगे।

त्वरित उत्तर: VLOOKUP() और IF() को कैसे संयोजित करें

एक शर्तित लुकअप बनाने के लिए, IF() स्टेटमेंट से शुरू करें और उसके अंदर VLOOKUP() का उपयोग करें ताकि शर्त के आधार पर अलग-अलग परिणाम लौटाए जा सकें। हम नीचे दिए गए कोड का उपयोग करके किसी उत्पाद की मात्रा के आधार पर यह जाँच सकते हैं कि वह स्टॉक में है या नहीं। बस ये चरण अपनाएँ:

• IF() फ़ंक्शन से शुरू करें: =IF()

• IF() फ़ंक्शन के अंदर VLOOKUP() का उपयोग करें।

=IF(VLOOKUP(C2, $A$2:$B$6, 2, FALSE) > 0, "In Stock", "Out of Stock")संचालित द्वारा

Excel में VLOOKUP() और IF() स्टेटमेंट को समझना

VLOOKUP() आपको किसी तालिका में डेटा खोजने में मदद करता है, जबकि IF() स्टेटमेंट उस डेटा के आधार पर निर्णय लेने देता है। साथ मिलकर, ये स्प्रेडशीट में जानकारी का विश्लेषण करने में मदद करते हैं। आइए प्रत्येक फ़ंक्शन को अलग-अलग देखें और फिर उन्हें साथ मिलाएँ। 

Excel में VLOOKUP() क्या है?

VLOOKUP() किसी रेंज के पहले कॉलम में एक विशिष्ट मान खोजता है और उसी पंक्ति के किसी अन्य कॉलम से मान लौटाता है। यहाँ इसका सिंटैक्स है ताकि आप अपने बड़े डेटासेट के लिए VLOOKUP() फ़ॉर्मूला का उपयोग कर सकें।

=VLOOKUP(search_key, range, index, is_sorted)संचालित द्वारा

आइए इस सिंटैक्स को तोड़कर समझें:

• Search_key वह मान है जिसे आप खोजना चाहते हैं। यह कोई संख्या, टेक्स्ट, या वह सेल रेफरेंस हो सकता है जिसमें खोजा जाने वाला मान है।

• Range उन कोशिकाओं की रेंज को परिभाषित करता है जिसमें डेटा होता है। इस रेंज के पहले कॉलम में search_key होना चाहिए। 

• Index उस रेंज के कॉलम का नंबर है जिससे आप मान प्राप्त करना चाहते हैं। पहला कॉलम 1 है, दूसरा 2, और आगे इसी तरह।

• Is_sorted एक लॉजिकल मान है (TRUE या FALSE)। आप संख्याओं के लिए TRUE (या 1) और टेक्स्ट के लिए FALSE (या 0) का उपयोग कर सकते हैं। यदि TRUE है, तो VLOOKUP() मानता है कि रेंज का पहला कॉलम आरोही क्रम में सॉर्टेड है और निकटतम मिलान लौटाता है। यदि FALSE है, तो VLOOKUP() सटीक मिलान खोजता है। यदि आर्गुमेंट निर्दिष्ट नहीं है, तो डिफ़ॉल्ट TRUE होता है। 

आइए एक उदाहरण से समझें। यहाँ मेरे पास उत्पादों की सूची है जिनके ID और कीमतें दी गई हैं। मैं उनके नाम के आधार पर विशिष्ट उत्पादों की कीमतें खोजना चाहता हूँ।

उत्पादों की सूची उनके ID और कीमतों के साथ। स्रोत: लेखक द्वारा बनाई गई छवि।

शुरू करने के लिए, मैं विशेष रूप से टैबलेट की कीमत ढूँढना चाहता हूँ। इसके लिए, मैं किसी भी सेल में search_key मान दर्ज करता हूँ (मेरे मामले में, Tablet) ताकि उसकी कीमत मिल सके। 

जिस वस्तु की कीमत खोजनी है उसका मान दर्ज करें। स्रोत: लेखक द्वारा बनाई गई छवि।

फिर, मैं किसी सेल का चयन करके =VLOOKUP() टाइप करता हूँ।

VLOOKUP() फ़ॉर्मूला टाइप करना। स्रोत: लेखक द्वारा बनाई गई छवि।

अगला, मैं उस सेल का चयन करता हूँ जहाँ search_key दर्ज किया गया था।

वह कॉलम चुनना जहाँ उत्पाद स्थित है। स्रोत: लेखक द्वारा बनाई गई छवि।

फिर, मैं तालिका की रेंज का चयन करता हूँ। 

तालिका की रेंज चुनना। स्रोत: लेखक द्वारा बनाई गई छवि।

बाएँ से गिनते हुए, मैं उस कॉलम का नंबर दर्ज करता हूँ जहाँ से मुझे डेटा प्राप्त करना है। यहाँ, मुझे कीमत चाहिए, इसलिए मैं 2 दर्ज करता हूँ।

कॉलम इंडेक्स चुनना। स्रोत: लेखक द्वारा बनाई गई छवि।

फिर, मैं सटीक मिलान पाने के लिए FALSE टाइप करता हूँ।

सटीक मिलान के लिए FALSE टाइप करना। स्रोत: लेखक द्वारा बनाई गई छवि।

सभी मान भरने के बाद, मैं Enter दबाता हूँ:

VLOOKUP() का उपयोग करके उत्पाद की कीमत प्राप्त हुई। स्रोत: लेखक द्वारा बनाई गई छवि।

जैसा कि छवि में दिख रहा है, VLOOKUP() ने सफलतापूर्वक कीमत प्राप्त कर ली है।

Excel में IF() क्या है?

IF() स्टेटमेंट मानों की तुलना करते हैं और उन्हें निर्दिष्ट शर्त के अनुसार परखते हैं। इसका सिंटैक्स यह है:

=IF(logical_test, [value_if_true], [value_if_false])संचालित द्वारा

इसे समझने के लिए मुख्य हिस्से देखें: 

• Logical_test वह मान या अभिव्यक्ति है जिसे आप TRUE या FALSE के रूप में मूल्यांकित करना चाहते हैं। यही वह शर्त है जिसे आप जाँचना चाहते हैं।

• Value_if_true वह मान लौटाता है जब logical_test TRUE हो।

• Value_if_false वह मान लौटाता है जब logical_test FALSE हो।

आइए एक उदाहरण लें। यहाँ मैं विद्यार्थियों को उनके ग्रेड के आधार पर टिप्पणियाँ देना चाहता हूँ। इसलिए, मैं दो कॉलम के साथ शीट तैयार करता हूँ: STUDENTS और GRADES। 

विद्यार्थियों की सूची उनके ग्रेड के साथ। स्रोत: लेखक द्वारा बनाई गई छवि।

मैं एक सेल चुनकर =IF() टाइप करता हूँ। मेरा लक्ष्य यह जाँचना है कि कुल अंक 50 से अधिक हैं तो Excellent प्रिंट करूँ, और यदि ग्रेड 50 से कम है तो Bad प्रिंट करूँ। शर्तें निर्दिष्ट करने के बाद, परिणाम पाने के लिए मैं Enter दबाता हूँ।

IF स्टेटमेंट का उपयोग करके सभी विद्यार्थियों को टिप्पणियाँ सौंपना। स्रोत: लेखक द्वारा बनाई गई छवि।

फिर, मैं फ़ॉर्मूला को ड्रैग करके अंतिम भरी हुई सेल तक कॉपी करता हूँ। आप परिणाम देख सकते हैं। मैंने एक ही फ़ॉर्मूला से सभी विद्यार्थियों को टिप्पणियाँ सौंप दी हैं। 

Excel में VLOOKUP() को IF() के साथ संयोजित करने के तरीके

आइए व्यावहारिक उदाहरणों से समझें कि VLOOKUP() IF() के साथ कैसे काम करता है।

शर्तित लुकअप

जैसा कि कहा, शर्तित लुकअप बनाने के लिए IF() स्टेटमेंट से शुरू करें और उसके अंदर VLOOKUP() का उपयोग करें ताकि शर्त के आधार पर अलग-अलग परिणाम लौटें।

1. IF() फ़ंक्शन से शुरू करें: =IF()।

2. IF() फ़ंक्शन के अंदर VLOOKUP() का उपयोग करें।

आइए एक नया उदाहरण आज़माएँ: यहाँ मेरे पास उत्पाद ऑर्डर की सूची है उनके संबंधित ऑर्डर समय के साथ। मैं देखना चाहता हूँ कि कोई विशिष्ट उत्पाद 12:00 PM से पहले ऑर्डर किया गया था या नहीं।

  

ऑर्डर ID और समय सहित उत्पाद सूची वाली तालिका। स्रोत: लेखक द्वारा बनाई गई छवि।

सबसे पहले, मैं एक कॉलम चुनकर निम्न फ़ॉर्मूला दर्ज करता हूँ: 

=IF(VLOOKUP(A3, A2:C5,3, FALSE) < TIME(12, 0, 0), "Ordered Before Noon", "Ordered After Noon")संचालित द्वारा

यहाँ, VLOOKUP() फ़ंक्शन कॉलम A में Banana खोजता है और कॉलम B से संबंधित ऑर्डर समय लौटाता है।

IF() स्टेटमेंट जाँचता है कि ऑर्डर समय 12 से कम है या नहीं। यदि हाँ, तो यह Ordered Before Noon लौटाता है। अन्यथा, यह Ordered After Noon लौटाता है।

IF() और VLOOKUP() फ़ॉर्मूला को मिलाकर डिलीवरी समय की गणना। स्रोत: लेखक द्वारा बनाई गई छवि।

आप देख सकते हैं कि मैंने शर्तित लुकअप की मदद से अपने मनचाहे उत्पाद डिलीवरी को कितनी आसानी से ट्रैक किया।

त्रुटि संभालना

त्रुटियों को संभालने के लिए, IF() स्टेटमेंट से शुरू करें और उसके अंदर VLOOKUP() के साथ ISNA() का उपयोग करें ताकि त्रुटियों की जाँच हो सके। उदाहरण के लिए, यदि कोई उत्पाद न मिले तो एक कस्टम संदेश दिखाने के लिए:

1. IF() फ़ंक्शन से शुरू करें: =IF()।

2. IF() फ़ंक्शन के अंदर त्रुटि जाँच के लिए VLOOKUP() के साथ ISNA() का उपयोग करें।

इसे एक उदाहरण से स्पष्ट करते हैं। मान लें कि मेरे पास उत्पाद कीमतों वाली एक तालिका है और मैं चाहता हूँ कि जब भी कोई उत्पाद न मिले तो एक कस्टम संदेश दिखे। 

उत्पादों और उनकी कीमतों की सूची वाली तालिका। स्रोत: लेखक द्वारा।

ऐसा करने के लिए, मैं एक सेल चुनकर कीमत खोजने हेतु निम्न फ़ॉर्मूला दर्ज करता हूँ:

=(VLOOKUP(B7, $A$2:$B$5, 2, FALSE)संचालित द्वारा

इसके बाद, मैं किसी भी त्रुटि को संभालने के लिए इसे ISNA() और IF() के साथ संयोजित करता हूँ।

=IF(ISNA(VLOOKUP(B7, $A$2:$B$5, 2, FALSE)), "Product Not Found", (VLOOKUP(B7, $A$2:$B$5, 2, FALSE)))संचालित द्वारा

IF() और ISNA() फ़ॉर्मूला को मिलाकर त्रुटियाँ संभालना। स्रोत: लेखक द्वारा।

यहाँ, ISNA() जाँचता है कि क्या VLOOKUP() फ़ंक्शन #N/A त्रुटि लौटाता है, जो उपलब्ध न होने पर होता है। दूसरे शब्दों में, यदि ISNA() का मान TRUE आता है, तो IF() स्टेटमेंट Product Not Found लौटाता है; अन्यथा, यह VLOOKUP() से प्राप्त कीमत लौटाता है।

डायनेमिक कॉलम इंडेक्सिंग

VLOOKUP() के लिए कॉलम इंडेक्स को डायनेमिक रूप से चुनने के लिए, IF() स्टेटमेंट से शुरू करें और उसके अंदर VLOOKUP() का उपयोग करें ताकि किसी शर्त के आधार पर अलग-अलग कॉलम चुने जा सकें। उदाहरण के लिए, किसी थ्रेशहोल्ड मान के आधार पर अलग-अलग कॉलम देखने के लिए:

1. IF() फ़ंक्शन से शुरू करें: =IF()।

2. IF() फ़ंक्शन के अंदर, थ्रेशहोल्ड जाँचने और कॉलम चुनने के लिए VLOOKUP() का उपयोग करें।

यहाँ मेरे पास एक उत्पाद तालिका है जिसमें कॉलम A में उत्पाद नाम, कॉलम B में उत्पाद कीमतें, और कॉलम C में स्टॉक मात्रा है। मैं यह देखना चाहता हूँ कि कीमत 50 से ऊपर या नीचे होने के आधार पर उत्पाद की कीमत या स्टॉक मात्रा देखें।

उत्पादों, उनके ID और कीमतों की सूची वाली तालिका। स्रोत: लेखक द्वारा बनाई गई छवि।

मैं एक सेल चुनकर निम्न फ़ॉर्मूला दर्ज करता हूँ:

=IF(VLOOKUP(B9, $B$9:$D$14, 2, FALSE) > 50, VLOOKUP(B9,$B$9:$D$14, 3, FALSE), VLOOKUP(B9,$B$9:$D$14, 2, FALSE))संचालित द्वारा

नेस्टेड VLOOKUP() के साथ IF लागू करना। स्रोत: लेखक द्वारा बनाई गई छवि।

यह फ़ॉर्मूला इस तरह काम करता है: 

• (VLOOKUP(B9, $B$9:$D$14, 2, FALSE) B9 में दिए गए उत्पाद को कॉलम A से खोजता है और कॉलम C से कीमत लौटाता है।

• IF() स्टेटमेंट जाँचता है कि कीमत $50 से अधिक है या नहीं।

• यदि सही है, तो हमारा कोड स्टॉक मात्रा लौटाता है: VLOOKUP(B9,$B$9:$D$14, 3, FALSE)।

• यदि गलत है, तो हमारा कोड उत्पाद ID लौटाता है: VLOOKUP(B9,$B$9:$D$14, 2, FALSE))।

यदि आप बहुत बड़े डेटासेट के साथ काम कर रहे हैं, तो फ़ॉर्मूला को ड्रैग करके अंतिम भरी हुई सेल तक कॉपी कर सकते हैं।

परिणाम दिखाना। स्रोत: लेखक द्वारा बनाई गई छवि।

बस इतना ही। जैसा कि आप देख सकते हैं, हम संयुक्त फ़ॉर्मूला का उपयोग करके विशिष्ट शर्तों के आधार पर मनचाही जानकारी प्राप्त कर सकते हैं।

VLOOKUP() और IF() का उपयोग करते हुए उन्नत तकनीकें

अब जब आपको IF() स्टेटमेंट को VLOOKUP() के साथ संयोजित करने की बुनियादी समझ है, तो आइए कुछ उन्नत तकनीकें सीखें जिनके उदाहरण मैंने स्वयं आज़माए हैं।

एकाधिक मानदंडों का संयोजन

यदि आप एक से अधिक मानदंडों के आधार पर डेटा खोज रहे हैं, तो आप IF() स्टेटमेंट के भीतर कई VLOOKUP() फ़ंक्शनों को संयोजित कर सकते हैं ताकि यह जाँच सकें कि सभी शर्तें पूरी होती हैं या नहीं। 

यहाँ मेरे पास ग्राहक खरीदारी डेटा और मेंबरशिप स्थिति वाली तालिका है। और मैं जाँचना चाहता हूँ कि कोई ग्राहक लॉयल्टी प्रोग्राम के लिए पात्र था या नहीं, जिसके लिए TOTAL PURCHASE ($) में कम से कम 500 और MEMBERSHIP STATUS के रूप में Gold आवश्यक है।

ग्राहकों का विवरण वाली तालिका। स्रोत: लेखक द्वारा बनाई गई छवि।

मैं Eligibility नाम का एक और कॉलम बनाता हूँ, जिसमें पात्रता मानदंड दिखते हैं। फिर, मैं निम्न फ़ॉर्मूला टाइप करके Enter दबाता हूँ: 

=IF(AND(VLOOKUP(B2, $B$2:$D$11, 2, FALSE) >= 500, VLOOKUP(B2, $B$2:$D$11, 3, FALSE) = "Gold"), "Eligible", "Not Eligible")संचालित द्वारा

कई मानदंडों को मिलाकर ग्राहकों की पात्रता जाँचना। स्रोत: लेखक द्वारा बनाई गई छवि।

यह फ़ॉर्मूला इस तरह काम करता है: 

• VLOOKUP(B2, $B$2:$D$11, 2, FALSE) >= 500 जाँचता है कि ग्राहक की कुल खरीदारी कम से कम $500 है।

• VLOOKUP(B2, $B$2:$D$11, 3, FALSE) = "Gold") जाँचता है कि John की मेंबरशिप स्थिति Gold थी।

• AND दोनों शर्तों को मिलाकर सुनिश्चित करता है कि दोनों सही हों।

• IF() तब Eligible लौटाता है जब दोनों शर्तें सही हों। अन्यथा, यह Not Eligible लौटाता है।

गणनाओं के लिए IF() के साथ VLOOKUP()

आप VLOOKUP() से कोई मान ढूँढ सकते हैं और फिर उस मान के आधार पर गणनाएँ करने के लिए IF() स्टेटमेंट लगा सकते हैं। 

यहाँ मैं उत्पादों और उनकी कीमतों के साथ एक शीट तैयार करता हूँ। फिर, मैं $100 से ऊपर के उत्पादों पर 10% छूट लागू करना चाहता हूँ।

उत्पादों और उनकी कीमतों की सूची वाली तालिका। स्रोत: लेखक द्वारा बनाई गई छवि।

तो, मैं DISCOUNT नाम का एक और कॉलम बनाता हूँ और उस कॉलम में छूट दिखाने के लिए निम्न फ़ॉर्मूला लिखता हूँ।

=IF(VLOOKUP(A2, $A$2:$B$10, 2, FALSE) > 100, VLOOKUP(A2, $A$2:$B$10, 2, FALSE) * 0.9, VLOOKUP(A2, $A$2:$B$10, 2, FALSE))संचालित द्वारा

अब, यह फ़ॉर्मूला इस तरह काम करता है:

• VLOOKUP(A2, $A$2:$B$10, 2, FALSE) ने उत्पाद की कीमत प्राप्त की जो 56 है।

• =IF(VLOOKUP(A2, $A$2:$B$10, 2, FALSE) > 100, ..., ...) ने जाँचा कि उत्पाद की कीमत 100 से अधिक है या नहीं। 

• चूँकि शर्त सही है, VLOOKUP(A2, $A$2:$B$10, 2, FALSE) * 0.9 ने 10% छूट लागू की। 

• (VLOOKUP(A2, $A$2:$B$10, 2, FALSE)) ने उन उत्पादों की वास्तविक कीमतें प्रिंट कीं जहाँ छूट की शर्त सही नहीं थी।

इसी तरह मुझे उत्पाद के लिए फ़ॉर्मूला का अंतिम परिणाम 135 मिला। सभी उत्पादों के लिए मनचाहे परिणाम पाने के लिए, आप फ़ॉर्मूला को नीचे तक ड्रैग करके अंतिम भरी हुई सेल तक कॉपी कर सकते हैं।

VLOOKUP() को IF के साथ उपयोग करके उन उत्पादों पर छूट की गणना करना जिनकी कीमत 100 से अधिक है। स्रोत: लेखक द्वारा बनाई गई छवि।

बड़े डेटा सेट संभालना

आप बहुत बड़े डेटासेट पर काम करते समय डेटा प्राप्ति और त्रुटि संभाल को सुव्यवस्थित करने के लिए VLOOKUP() को IF() फ़ंक्शन के साथ भी मिला सकते हैं।

यह रहा हमारा अंतिम उदाहरण: मेरे पास कर्मचारियों की जानकारी वाली शीट है, और मैं किसी कर्मचारी का विभाग प्राप्त करना चाहता हूँ तथा वह स्थिति भी सँभालना चाहता हूँ जब कर्मचारी ID मौजूद न हो।

कर्मचारियों की सूची उनकी ID और विभागों के साथ। स्रोत: लेखक द्वारा बनाई गई छवि।

तो, मैं एक सेल चुनकर निम्न फ़ॉर्मूला टाइप करता हूँ: 

=IFERROR(VLOOKUP(E3, $A$2:$C$18, 3, FALSE), "Not Found")संचालित द्वारा

Enter दबाने के बाद, मैं फ़ॉर्मूला को अन्य कोशिकाओं में भी कॉपी करने के लिए ड्रैग करता हूँ।

ID के माध्यम से कर्मचारी का विभाग प्राप्त करना और न मिलने पर त्रुटियों को सँभालना। स्रोत: लेखक द्वारा बनाई गई छवि।

और आप ऊपर परिणाम देख सकते हैं। यह फ़ॉर्मूला इस प्रकार काम करता है: 

• VLOOKUP(E3, $A$2:$C$18, 3, FALSE) कर्मचारी का विभाग प्राप्त करता है।

• IFERROR त्रुटियों को सँभालता है। त्रुटि संदेश (#N/A) दिखाने के बजाय, यह एक दोस्ताना और अनुकूलित संदेश दिखाता है।

संक्षेप में, अब आपको शीट्स में गहराई तक जाने की ज़रूरत नहीं। क्योंकि जब आप VLOOKUP() और IF() स्टेटमेंट को मिलाते हैं, तो Excel में सबसे बड़े डेटा सेट को भी आसानी से संभाल सकते हैं। 

अंतिम विचार

VLOOKUP() को IF() स्टेटमेंट के साथ मिलाकर आप अधिक सटीक और त्रुटि-प्रतिरोधी स्प्रेडशीट बना सकते हैं। सुनिश्चित करें कि आप मेरे द्वारा साझा किए गए उदाहरणों के साथ प्रयोग करें ताकि देख सकें कि ये तकनीकें डेटा प्रबंधन कार्यों को कैसे सरल बनाती हैं और आपकी Excel कौशल को कैसे निखारती हैं।

यदि आप अपनी कौशल बढ़ाना चाहते हैं, तो हमारा Introduction to Excel कोर्स देखें और फिर मूलभूत बातों में महारत हासिल करने के लिए Excel Fundamentals स्किल ट्रैक पर अपग्रेड करें। एक बार जब आप इन फ़ंक्शनों में सहज हो जाएँ, तो हमारी Data Analysis in Excel कोर्स के माध्यम से अपनी विश्लेषणात्मक क्षमताएँ आगे बढ़ाएँ। लेकिन यदि आप वित्तीय पक्ष पर अधिक हैं, तो वित्तीय इनसाइट्स को एकीकृत करने के लिए हमारा Financial Modeling in Excel कोर्स देखें। 

इसके अलावा, हमारा Data Preparation in Excel कोर्स आपके डेटा क्लीनिंग प्रोसेस को सुव्यवस्थित करने में मदद करेगा, और Data Visualization in Excel कोर्स आपको अपने डेटा को प्रभावशाली तरीके से प्रस्तुत करने में मदद करेगा।