Xlookup in Excel with example in Hindi

आपने एक्सेल में Lookup , Vlookup और Hlookup फार्मूला के बारे में सुना होगा और उसे इस्तेमाल भी किया होगा। ये सभी एक्सेल के बहुत स्ट्रांग functions है जो कि Data Analysis में प्रयोग किये जाते है। लेकिन क्या आप जानते है की एक्सेल के नए संस्करण 365 और 2019 में एक नया फंक्शन दिया गया है जिसका नाम है -Xlookup function

Xlookup एक्सेल में एक ऐसा फंक्शन है जिससे आप वो सभी काम कर सकते है जो आप Vlookup और Hlookup से करते है। तो आज की इस पोस्ट मे आपको एक्सेल के इस नए फंक्शन Xlookup के बारे में डिटेल में बताया जायेगा तो इसे अंत तक जरूर पढ़े।

What is XLookup Function in Hindi

Xlookup Function,Excel के New Version 365 और 2019 में दिया गया है यह फंक्शन एक्सेल फंक्शन के Lookup category के अंतर्गत दिया गया है। ये Function Vlookup और Hlookup दोनों का काम अकेले ही कर सकता है।

इस फंक्शन की ख़ास बात यह है कि हम Data में Left से Right की तरफ और right से left की तरफ सर्च कर सकते है। इससे हम 2 way lookup भी कर सकते है।

How to use Xlooup Function in Hindi

Xlookup Function का Use करने से पहले उसके Syntax को समझना बहुत जरूरी है।

Syntax of Xlookup function

=Xlookup(lookup_value, lookup_array, return_array)

Lookup_value – lookup value वह Value है जिसे हमें Data में Search करना है।

Lookup_array— Lookuparray में हम उस Range को लेते है जिसमे lookup value को सर्च करना है।

Return_array – returnarray में हम उस Column को लेते है ,जहा से हमें Result चाहिए।

not_found [optional] - अगर Xlookup को डाटा में कोई मैच नहीं मिलता है तो यह xlookup में दी गयी वैल्यू return करता है।

match-mode [optional]- एक्सेल में Xlookup function, lookup वैल्यू के लिए by default, exact मैच सर्च करता है। अगर आप इसे बदलना चाहते है तो match-mode आर्गुमेंट का प्रयोग कर सकते है।इसमें 4 options दिए गए है -

0- exact मैच

1- exact मैच या उससे next बड़ी वैल्यू

-1 exact मैच या उससे next छोटी वैल्यू

2 wildcard मैच

search-mode [optional]- search-mode आर्गुमेंट का उपयोग करके हम returning वैल्यूज के लिए सर्च आर्डर को निर्धारित कर सकते हैं। यह बताता है कि हमे डाटा को सर्च कैसे करना चाहिए। इसके अंतर्गत कुछ options दिए गए है जो इस प्रकार है -

[1] यह by डिफ़ॉल्ट वैल्यू है जिसमे Xlookup first वैल्यू से शरू करके से last तक सर्च करता है।

[-1] इसमें यह रिवर्स आर्डर में सर्च करता है यानी लास्ट वैल्यू से first वैल्यू तक

[2] Ascending आर्डर binary सर्च करने के लिए

[-2] Descending आर्डर में binary सर्च करने के लिए

अब देखते है कि Xlookup काम कैसे करता हैं-

Xlookup in Excel with Example in Hindi

यहाँ हम Xlookup को एक example से समझेंगे। इसके लिए हमने एक simple डेटा तैयार किया है जिसमे fruits के नाम उनके price और quantity दिए गए है।

अब हमे fruits के नाम के आधार पर उनके price निकालना है।

Col A Col B Col C

~~-------------------------------------------~~

Fruits Name |Price(/Kg)| Quantity
~~-------------------------------------------~~
Papaya      | 50       |      5
Melon    | 30       |      7
Orange       | 40       |      4
Mango      |   60       |      9

~~-------------------------------------------~~

Price निकालने के लिए हम Xlookup फार्मूला कुछ प्रकार लगाएंगे -

=XLOOKUP("Orange", A1:A4, B1:B4)

यहाँ पर "Orange" हमारी lookup वैल्यू है जिसका price हमे निकालना है। A1:A4 lookuparray है जिसमे lookup वैल्यू मौजूद है। अंत में हमने रेंज B1:B4 लिया है जो कि returnarray है जहा फ्रूट्स की price दिए गए है।

इस फार्मूला को लगाने के बाद जैसे ही आप enter key प्रेस करेंगे आपको रिजल्ट 40 मिलेगा जो कि orange की price है। जैसा कि आप ऊपर डेटा में देख सकते है।

यह Xlookup function का एक बेसिक example था जिसमे हमने समझा कि Xlookup से data सर्च करके उससे रिलेटेड इनफार्मेशन कैसे निकालते है। इस process में हमने Xlookup फंक्शन के पहले 3 arguments का इस्तेमाल किया।

अब हम देखेंगे कि Xlookup के बाकी optional आर्गुमेंट जैसे if_not_found, match_mode और search_mode का प्रयोग कैसे करते है -

if_not_found argument in Xlookup in Hindi

जब भी XLOOKUP को डाटा में lookup value नहीं मिलता है, तो यह हमे एक excel error ,#N/A return करता है। इस error को एक useful मैसेज में बदलने के लिए, if_not_found नाम के 4th आर्गुमेंट में,हम अपना कोई मैसेज टाइप कर सकते है।

उदाहरण के लिए , नीचे दिए गए data में अगर हम "Apple सर्च करने की कोशिश करते है तो यह हमे #N/A error return करता है क्यों कि यह हमारे data में available नहीं है। ऐसे स्थिति में हम 4th आर्गुमेंट में एक मैसेज दे सकते है।

Col A              Col B           Col C
~~-------------------------------------------~~
Fruits Name|Price (/Kg)| Quantity
~~-------------------------------------------~~
Papaya        | 50       |      5
Melon    | 30       |      7
Orange       | 40       |      4
Mango      |     60       |      9

~~-------------------------------------------~~

4th आर्गुमेंट का प्रयोग करके हम मैसेज इस प्रकार देंगे -

=XLOOKUP("Apple",A1:A4,B1:B4,"Data not found")

इसके बाद Xlookup #N/A को "Data not found" मैसेज से बदल देगा। यह एक्सेल के IFERROR function के similar है।

Note - if_not_found तर्क केवल #N/A error को रोकता है, सभी errors को नहीं।

match_mode argument in Xlookup in Hindi

यह Xlookup फंक्शन का 5th optional आर्गुमेंट है। वैसे exact मैच करना XLOOKUP का डिफ़ॉल्ट behaviour है, लेकिन कभी-कभी कुछ स्थितियों में, एक exact मैच काम नहीं करेगा। जैसे कि example के तौर पर आप नीचे स्क्रीनशॉट में देख सकते है जहा स्टूडेंट्स के grade निकाले गए है।

exact मैच सिर्फ वही काम करता है जहा पर lookup वैल्यू पूरी तरह से डाटा में मैच हो रही हो (row 2 John), लेकिन दूसरे cases में यह #N/A error हमे return करता है।

अगर हम #N/A error के बजाय सही ग्रेड निकालना है, तो हमें Xlookup में approximate मैच करना होगा। Approx मैच निकालने के लिए, हमे match_mode आर्गुमेंट को या तो -1 या 1 पर सेट करने की आवश्यकता है, जो कि डेटा के व्यवस्थित होने के आधार पर depend करता है।

हमारे मामले में, lookup लिस्ट, ग्रेड के निचले सीमाओं की सूची करती है। इसलिए, जब exact मैच नहीं मिलता है, तो हम match_mode को -1 पर सेट करते हैं ताकि हम अगले small वैल्यू की सर्च कर सकें-

उदाहरण के रूप में, Bob का score 98 (E2) है। यह फार्मूला, A2:A6 रेंज में इस lookup value को सर्च करता है लेकिन यह नहीं मिलता। फिर यह अगले smaller वैल्यू को सर्च करता है और 90 को पाता है, जिसका मतलब Grade A है।

यदि हमारी lookup table में grades के ऊपरी सीमाएं शामिल होतीं, और exact match असफल होता है तो अगले large value की सर्च के लिए match_mode को 1 पर सेट करेंगे।

नीचे इमेज में आप देख सकते है कि फार्मूला 98(cell E2) को सर्च करता है और फिर से इसे यह lookup table में नहीं मिलता। इस बार, यह अगले large वैल्यू को सर्च करने का प्रयास करता है और 100 प्राप्त करता है, जो Grade A को दिखाता है।

फार्मूला- =XLOOKUP(E2,$A$2:$A$6,$B$2:$B$6,,1)

How to use wildcard in Xlookup in excel in Hindi (Xlookup में wildcard का प्रयोग कैसे करे ?)

Xlookup फंक्शन द्वारा ,एक partial मैच (आंशिक मेल) सर्च करने के लिए, match_mode आर्गुमेंट को 2 पर सेट करें, जिससे XLOOKUP फ़ंक्शन wildcard characters (*, ?) को accept कर सके।

यह कैसे काम करता है, इसे समझने के लिए हमने यहाँ एक example लिया है, जिसमे कॉलम A में कुछ स्मार्टफोन मॉडल्स दिए गए हैं और कॉलम B में, उनकी बैटरी क्षमता है। हमे किसी विशेष स्मार्टफोन की बैटरी के बारे में जानकारी चाहिए।

पर समस्या यह है कि हमे कि आप कॉलम A में दिए गए Model का नाम पूरी तरह पता नहीं है। इस समस्या को सुलझाने के लिए, उस हिस्से को टाइप करें जो निश्चित रूप से टेबल में है और बाकि characters को वाइल्डकार्ड्स से बदलें।

उदाहरण के लिए, iPhone X की बैटरी के बारे में इनफार्मेशन प्राप्त करने के लिए इस फार्मूला का उपयोग करें:

=XLOOKUP("*iphone X*",A2:A8,B2:B8,,2) या

=XLOOKUP("*"&E1&"",A2:A8,B2:B8,,2)

search_mode argument in Xlookup in Hindi

Xlookup फंक्शन का यह सबसे लास्ट और optional आर्गुमेंट है जिसके द्वारा सर्च करने की दिशा निर्धारित की जाती है। उदाहरण के लिए ,अगर आप reverse आर्डर में वैल्यू सर्च करना चाहते है तो search_mode ऑप्शन का use कर सकते है।

[1] यह डेटा में top to bottom या left से right की ओर सर्च करता है।

[-1] यह data में वैल्यू को bottom तो top यानि last वैल्यू से first वैल्यू की तरफ सर्च करता है। ]

उदाहरण के लिए हमने यहाँ एक डाटा लिया है,जिसमे एक विशेष salesperson का last sale निकालना है।

इसके लिए हमनें फार्मूला =XLOOKUP(G1, B2:B9, , ,-1) लगाया है। (जहा G1 lookup_value , lookup_array के लिए B2:B9, और return_array के लिए D2:D9) और 6th आर्गुमेंट में -1 डाला हैं।