XLOOKUP - one of the most powerful function in Excel

Summary

XLOOKUP Microsoft Excel में एक नया function है जिसका उपयोग data में lookup करने के लिए किया जा सकता है। यह function Excel 365 में पेश किया गया था और Excel के latest versions में उपलब्ध है। यह कई अतिरिक्त सुविधाओं और क्षमताओं के साथ पारंपरिक lookup function का updated और बेहतर version है।

यह एक शक्तिशाली lookup formula है जो users को table में एक specific value खोजने और संबंधित value वापस करने की अनुमति देता है। यह Excel में VLOOKUP, HLOOKUP और LOOKUP functions का एक updated version है जो उनकी सीमाओं को पार करता है और users को अधिक लचीलापन और नियंत्रण प्रदान करता है।

Important points:

• Purpose : किसी range या array में value खोजें और किसी अन्य range से corresponding value लौटाएं, जो VLOOKUP या HLOOKUP की तुलना में अधिक flexibility प्रदान करता है।
• Category : Lookup & Reference functions
• Released date/version : Microsoft 365 on March 31, 2020
• Inputs/parameters required : 6 (3 mandatory & 3 optional)
• Output : Specified return range से value जो search range में match किए गए lookup value से मेल खाता है।

---

Syntax

XLOOKUP function का basic syntax इस प्रकार है:

=XLOOKUP(lookup_value,lookup_array,return_array,[if_not_found],[match_mode],[search_mode])

XLOOKUP function का उपयोग table या data की range में एक specific value खोजने के लिए किया जाता है, और उसी row या एक अलग column से संबंधित value लौटाता है। यहां बताया गया है कि प्रत्येक तर्क (argument) क्या दर्शाता है:

1. lookup_value: वह value जिसे आप lookup_array में देखना चाहते हैं। यह एक cell reference, एक text string या एक numerical value हो सकता है।
2. lookup_array: cells की वह range जिसे आप lookup_value के लिए खोजना चाहते हैं। यह एक single column या row, या cells की two-dimensional range हो सकती है।
3. return_array: cells की वह range जिसमें वे values होते हैं जिन्हें आप match मिलने पर वापस करना चाहते हैं। यह एक single column या row, या cells की two-dimensional range हो सकती है जो lookup_array से मेल खाती है।
4. [if_not_found]: एक optional argument जो specify करता है कि यदि कोई match नहीं मिलता है तो क्या वापस करना है। यह एक text string, एक numerical value या #N/A जैसा कोई error value हो सकता है।
5. [match_mode]: एक optional argument जो specify करता है कि lookup_value को lookup_array में values के साथ कैसे match किया जाए। यह exact match के लिए 0, approximate match के लिए 1, या exact or approximate match के लिए -1 हो सकता है।
6. [search_mode]: एक optional argument जो approximate match का उपयोग किए जाने पर search direction specify करता है। यह उस खोज के लिए 1 हो सकता है जो lookup_value से अधिक या उसके बराबर value पाता है, या -1 उस खोज के लिए हो सकता है जो lookup_value से कम या उसके बराबर values पाता है।

ध्यान दें कि optional arguments के चारों ओर square brackets का अर्थ है कि उन्हें formula से छोड़ा जा सकता है। lookup_array और return_array arguments आवश्यक हैं, जबकि अन्य arguments optional हैं।

XLOOKUP function का उपयोग विभिन्न प्रकार के scenarios में किया जा सकता है, जैसे database में किसी specific item को ढूँढना, tables के बीच data का मिलान करना, या test scores के आधार पर grades की गणना करना। function को लचीला और उपयोगकर्ता के अनुकूल होने के लिए design किया गया है, जिसमें case-insensitive खोज और data_array में किसी भी column या row से values वापस करने की क्षमता जैसी विशेषताएं हैं।

---

How to use

आरंभ करने के लिए यहां एक basic XLOOKUP tutorial है:

उदाहरण: मान लीजिए कि आपके पास निम्न data वाली एक table है:

Name	Grade
John	A
Maria	B
Jane	C

और आप "John" नाम के छात्र का grade देखना चाहते हैं। आप निम्न XLOOKUP formula का उपयोग कर सकते हैं:

=XLOOKUP("John", A2:A4,B2:B4)

formula का परिणाम "A" होगा, जो "John" नामक छात्र का grade है।

नोट: XLOOKUP function case-insensitive है, इसलिए यह सही परिणाम लौटाएगा चाहे आप search key में "John" या "john" type करें।

XLOOKUP उदाहरण:

Left-to-Right Lookup:

left-to-right lookup का अर्थ है कि return array lookup array के दाईं ओर है। left-to-right देखने के लिए, आप निम्न formula का उपयोग कर सकते हैं:

=XLOOKUP(lookup_value, lookup_array, return_array)

Products	Prices
Apple	2.99
Papaya	3.5
Cherry	5
Coconut	2.5
Blueberry	6.5
Apple	4.5
Guava	5.5
Mango	6.1
Pineapple	8.5

उदाहरण के लिए, यदि आपके पास products और उनकी कीमतों की एक सूची है, और आप उस product की कीमत खोजना चाहते हैं जिसका नाम "Apple" है, तो आप निम्न formula का उपयोग कर सकते हैं:

=XLOOKUP("Apple", B2:B10, A2:A10)

यहाँ, B2:B10 lookup array है जिसमें products के नाम हैं, और A2:A10 return array है जिसमें उनकी कीमतें शामिल हैं। यदि यह lookup array में पाया जाता है तो formula Apple का मूल्य लौटाएगा। यदि Apple नहीं मिलता है, तो formula एक #N/A error लौटाएगा।

Right-to-Left Lookup:

right-to-left lookup का अर्थ है कि return array lookup array के बाईं ओर है। दाएँ-से-बाएँ देखने के लिए, आप निम्न formula का उपयोग कर सकते हैं:

=XLOOKUP(lookup_value, lookup_array, return_array)

Price	Product
100	Apple
200	Mango
100	Banana
300	Cherry
400	Papaya

उदाहरण के लिए, यदि आपके पास products और उनकी कीमतों की एक सूची है, जिसमें products के नाम दाईं ओर लिखे गये हैं और उनकी कीमतें बाईं ओर लिखें गये हैं और आप "Apple" की कीमत खोजना चाहतें हैं, तो आप निम्न formula का उपयोग कर सकते हैं:

=XLOOKUP("Apple", B2:B10, A2:A10)

यहाँ, B2:B10 lookup array है जिसमें products की कीमतें शामिल हैं, और A2:A10 return array है जिसमें उनके नाम शामिल हैं। formula पहले product की कीमत लौटाएगा जो lookup value से right-to-left दिशा में मेल खाता है। यदि कई matches हैं, तो यह return array में पहले match की कीमत लौटाएगा।

XLOOKUP with If_Not_Found:

lookup array में lookup value नहीं मिलने पर display करने के लिए संदेश specify करने के लिए आप [if_not_found] argument का उपयोग कर सकते हैं। उदाहरण के लिए:

=XLOOKUP("Apple", B2:B10, A2:A10, "Not Found")

यहां, " Not Found " display करने का संदेश है जब Apple lookup array में नहीं मिलता है। Apple मिल जाए तो formula उसकी कीमत लौटा देगा।

XLOOKUP with Match_Mode:

[match_mode] argument आपको XLOOKUP के लिए भिन्न match modes specify करने की अनुमति देता है। तीन match modes हैं: 1 exact match के लिए, 2 wildcard match के लिए, और -1 binary search के लिए।

Exact Match:

exact match करने के लिए, आप निम्न formula का उपयोग कर सकते हैं:

=XLOOKUP(lookup_value, lookup_array, return_array, "", 1)

उदाहरण के लिए, यदि आपके पास products और उनकी कीमतों की एक सूची है, और आप उस product की कीमत खोजना चाहते हैं जिसका नाम "Apple" है, तो आप निम्न formula का उपयोग कर सकते हैं:

=XLOOKUP("Apple", B2:B10, A2:A10, "", 1)

यहाँ, B2:B10 lookup array है जिसमें products के नाम हैं, और A2:A10 return array है जिसमें उनकी कीमतें शामिल हैं। यदि यह lookup array में पाया जाता है तो formula Apple का मूल्य लौटाएगा। यदि Apple नहीं मिलता है, तो formula एक #N/A error लौटाएगा।

Wildcard Match:

wildcard match करने के लिए, आप निम्न formula का उपयोग कर सकते हैं:

=XLOOKUP(lookup_value&"*", lookup_array, return_array, "", 2)

उदाहरण के लिए, यदि आपके पास products और उनकी कीमतों की एक सूची है, और आप उस product की कीमत का पता लगाना चाहते हैं, जिसका नाम "App" से शुरू होता है, तो आप निम्न formula का उपयोग कर सकते हैं:

=XLOOKUP("App" & "*", B2:B10, A2:A10, "", 2)

यहाँ, B2:B10 lookup array है जिसमें products के नाम हैं, और A2:A10 return array है जिसमें उनकी कीमतें शामिल हैं। formula पहले product की कीमत लौटाएगा जिसका नाम "App" से शुरू होता है। यदि कई matches हैं, तो यह return array में पहले match का मूल्य लौटाएगा।

---

Error Handling

XLOOKUP में, error handling built-in होती है और विभिन्न scenarios को manage करने में मदद करता है जहाँ lookup fail हो सकते हैं। यहाँ बताया गया है कि प्रत्येक प्रकार की error handling कैसे काम करती है:

1. if_not_found Argument:
• यह आपको एक value specify करने की अनुमति देता है (उदाहरण के लिए, "Not Found") यदि lookup को कोई मिलान नहीं मिलता है।
• उदाहरण: यदि value नहीं मिलता है, तो error दिखाने के बजाय, formula if_not_found में specified की गई कोई भी चीज़ लौटाता है।
  =XLOOKUP("P104", A2:A4, B2:B4, "Not Found")
2. #N/A error:
• यह error तब होती है जब XLOOKUP search range में lookup value नहीं ढूँढ पाता है।
• आप इसे if_not_found argument का उपयोग करके या custom message के लिए formula को IFNA के साथ लिख कर संभाल सकते हैं।
  =IFNA(XLOOKUP("P104", A2:A4, B2:B4), "Value Not Found")
3. #VALUE! error:
• यह तब होता है जब आप lookup या return range में incompatible array आकार प्रदान करते हैं।
• सुनिश्चित करें कि lookup और return array के dimensions मेल खाते हैं।
4. #REF! error:
• यह error तब होती है जब XLOOKUP में reference किया गया cell हटा दिया जाता है या invalid होता है।
• यह सुनिश्चित करने के लिए range की दोबारा जाँच करें कि वे valid data की ओर इशारा करते हैं।

if_not_found का उपयोग करने से सबसे सरल error handling मिलती है, जिससे missing values के लिए अन्य function में wrapping की आवश्यकता नहीं होती है।

---

निष्कर्ष (Conclusion)

XLOOKUP एक शक्तिशाली function है जो पारंपरिक VLOOKUP और HLOOKUP functions की तुलना में table या range में values की खोज करने के लिए अधिक लचीला और कुशल तरीका प्रदान करता है। exact और wildcard matches, बाएं से दाएं और दाएं से बाएं lookups और customizable error handling करने की अपनी क्षमता के साथ, XLOOKUP data analyze और reporting के लिए एक मूल्यवान उपकरण है।

---

XLOOKUP with other functions

XLOOKUP with IFERROR and SUM

आप ज़्यादा advance operation के लिए XLOOKUP को IFERROR, SUM या LEFT जैसे दूसरे functions के साथ जोड़ सकते हैं। यहाँ IFERROR और SUM के साथ XLOOKUP का इस्तेमाल करने का एक उदाहरण दिया गया है:

उदाहरण:

मान लीजिए कि आपके पास column A में Product ID, column B में Price और column C में Quantity वाली एक table है। आप किसी specific Product ID के लिए price देखना चाहते हैं, उसे quantity से multiply करना चाहते हैं, और कई products के लिए result को जोड़ना चाहते हैं, जब कोई Product ID नहीं मिलती है तो error को handle करना चाहते हैं।

A	B	C
Product ID	Price	Quantity
P101	10	2
P102	15	3
P103	20	1

Formula:

=SUM(IFERROR(XLOOKUP({"P101","P104"}, A2:A4, B2:B4) * XLOOKUP({"P101","P104"}, A2:A4, C2:C4), 0))

Explanation:

• XLOOKUP({"P101","P104"}, A2, B2): Product ID P101 और P104 के लिए price देखता है।
• XLOOKUP({"P101","P104"}, A2, C2): Product ID P101 और P104 के लिए quantity देखता है।
• IFERROR(..., 0): यदि Product ID नहीं मिलती है (जैसे P104), तो यह error के बजाय 0 लौटाता है।
• SUM(...): Valid products के लिए price और quantity को multiply करके कुल price की calculation करता है।

क्या XLOOKUP multiple columns को handle कर सकता है?

हाँ, XLOOKUP multiple columns को handle कर सकता है, लेकिन एक specific तरीके से। यदि आप return range में उन columns की पूरी array specify करते हैं तो यह multiple columns से data लौटा सकता है।

उदाहरण:

मान लीजिए कि आपके पास Column A से D में निम्न data है:

A	B	C	D
Product ID	Price	Color	Stock
P101	10	Red	20
P102	15	Blue	10
P103	20	Green	5

आप Product ID "P102" के लिए Price (Column B) और Stock (Column D) देखना चाहते हैं।

Formula:

=HSTACK(XLOOKUP("P102", A2:A4, B2:B4), XLOOKUP("P102", A2:A4, D2:D4))

Explanation:

• XLOOKUP("P102", A2, B2): P102 के लिए price देखता है।
• XLOOKUP("P102", A2, D2): P102 के लिए stock देखता है।
• HSTACK: दो result (Price और Stock) को एक ही output में horizontal रूप से stack करता है।

Output:

यह {15, 10} लौटाएगा, जो Product ID P102 के लिए Price और Stock को represent करता है।

यदि आप Excel के ऐसे version का उपयोग कर रहे हैं जो HSTACK का support नहीं करता हैं, तो आप text output के लिए TEXTJOIN जैसे concatenate function का उपयोग करके result को combine कर सकते हैं या multiple return column के लिए अलग-अलग cell का उपयोग कर सकते हैं।

TEXT के साथ XLOOKUP (Output को Format करें)

आप XLOOKUP के result को format करने के लिए TEXT का उपयोग कर सकते हैं। उदाहरण के लिए, आप currency formatting के साथ price display करना चाहते हैं।

उदाहरण:

Product ID "P102" के लिए price देखें और इसे currency के रूप में format करें।

Formula:

=TEXT(XLOOKUP("P102", A2:A4, B2:B4), "$0.00")

Explanation:

• XLOOKUP("P102", A2, B2): P102 के लिए price ढूँढता है।
• TEXT(..., "$0.00"): Price को दो decimal places के साथ currency के रूप में format करता है।

Output:

$15.00

MATCH के साथ XLOOKUP (Dynamic Lookup Column)

आप XLOOKUP को MATCH के साथ जोड़कर dynamic रूप से चुन सकते हैं कि किसी condition या किसी अन्य lookup के आधार पर किस column से data प्राप्त करना है।

A	B	C
Product ID	Description	Color
P101	Widget	Red
P102	Gadget	Blue
P103	Tool	Green

उदाहरण:

User input के आधार पर product description (Column B) या color (Column C) ढूँढ़ें।

Formula:

=XLOOKUP("P101", A2:A4, B2:C4, , 0, MATCH("Color", {"Description", "Color"}, 0))

Explanation:

• XLOOKUP("P101", A2, B2): दो-column array में value ढूँढ़ता है।
• MATCH("Color", {"Description", "Color"}, 0): Dynamic रूप से चुनता है कि description लौटाना है या color।
• यह P101 के लिए color लौटाएगा।

Output:

Red

XLOOKUP के साथ LEFT (Extract Partial Text निकालें)

XLOOKUP द्वारा लौटाए गए text के भाग को निकालने के लिए XLOOKUP के साथ LEFT का उपयोग करें।

उदाहरण:

Product description देखें लेकिन केवल पहले 3 character display करें।

Formula:

=LEFT(XLOOKUP("P102", A2:A4, B2:B4), 3)

Explanation:

• XLOOKUP("P102", A2, B2): P102 ("Gadget") के लिए description ढूँढता है।
• LEFT(..., 3): Description के पहले 3 character निकालता है।

Output:

"Gad"

क्या XLOOKUP wildcard कर सकता है?

हां, XLOOKUP partial या flexible match करने के लिए Excel में wildcard को handle कर सकता है। Wildcard आपको text के भीतर pattern खोजने की अनुमति देते हैं, जिससे exact match न होने पर values को देखना आसान हो जाता है।

Excel में Wildcards:

• * (asterisk): Characters के किसी भी sequence से match खाता है।
• ? (question mark): किसी भी single character से match खाता है।

XLOOKUP में wildcard enable करने के लिए, आपको wildcard match के लिए match_mode argument को 2 पर set करना होगा।

उदाहरण:

मान लें कि आपके पास column A से B में निम्न data है, और आप description पर partial match का उपयोग करके किसी product को देखना चाहते हैं:

A	B
Product ID	Description
P101	Widget
P102	Gadget
P103	Tool

आप ऐसे product की खोज करना चाहते हैं जिसके description में "Wid" शामिल हो।
Formula:

=XLOOKUP("*Wid*", B2:B4, A2:A4, , 2)

Explanation:

• "Wid": Lookup value, "Wid" से पहले या बाद में किसी भी text से मिलान करने के लिए wildcard के रूप में * का उपयोग करना।
• B2:B4 : Description के लिए search करने की range (search range)।
• A2:A4 : Return range जहाँ संबंधित Product ID मिलेगी।
• , 2 : Wildcard matching mode enable करता है।

Output:

P101

यह P101 लौटाता है, क्योंकि "Widget" में "Wid" text शामिल है।

Another Example: Searching with a Single Character Wildcard

अगर आप "G" से शुरू होने वाला कोई description ढूँढना चाहते हैं और दूसरे स्थान पर कोई भी character है, तो आप ? wildcard का इस्तेमाल कर सकते हैं।

Formula:

=XLOOKUP("G?dget", B2:B4, A2:A4, , 2)

Explanation:

• "G?dget": Lookup value जहाँ ? किसी भी single character से मेल खाता है।
• यह "Gadget" से मेल खाएगा।

Output:

P102

Wildcard XLOOKUP को अत्यधिक flexible बनाते हैं, खासकर जब आपको approximate text match खोजने की आवश्यकता होती है।

Using XLOOKUP with an Array

XLOOKUP arrays के साथ काम कर सकता है, जिससे आप एक साथ कई lookup कर सकते हैं। जब आप lookup value के रूप में एक array पास करते हैं, तो XLOOKUP प्रत्येक lookup value के अनुरूप result की एक array लौटाएगा। यह उन situations के लिए उपयोगी है जहाँ आपको एक बार में एक table से कई result प्राप्त करने की आवश्यकता होती है।

कल्पना करें कि आपके पास data की निम्नलिखित table है:

A	B
Product ID	Price
P101	10
P102	15
P103	20

आप एक साथ कई products की price देखना चाहते हैं, जैसे कि P101 और P103, और दोनों value लौटाना चाहते हैं।

Formula:

=XLOOKUP({"P101", "P103"}, A2:A4, B2:B4)

Explanation:

• {"P101", "P103"}: यह lookup values (multiple product IDs) की एक array है जिसे आप खोजना चाहते हैं।
• A2:A4 : वह range जहाँ XLOOKUP product ID खोजेगा।
• B2:B4 : वह return range जहाँ XLOOKUP संबंधित price ढूँढ़ेगा।

Output:

Result एक array होगा:

{10, 20}

जहाँ 10 P101 का price है और 20 P103 का price है।

XLOOKUP with Arrays and Multiple Return Columns

आप lookup values की एक array के लिए data के multiple columns लौटाने के लिए XLOOKUP का भी उपयोग कर सकते हैं।

A	B	C
Product ID	Price	Stock
P101	10	100
P102	15	200
P103	20	150

आप P101 और P102 जैसे कई product IDs के लिए Price और Stock दोनों को प्राप्त करना चाहते हैं।

Formula:

=XLOOKUP({"P101", "P102"}, A2:A4, B2:C4)

Explanation:

• {"P101", "P102"}: Lookup value की एक array।
• B2:C4 : Return range दो columns (Price और Stock) तक फैली हुई है, इसलिए XLOOKUP प्रत्येक product ID के लिए दोनों values लौटाएगा।

Output:

Result एक array होगा:

{10, 15; 100, 200}

इसका मतलब है:

• P101 के लिए, price 10 है और stock 100 है।
• P102 के लिए, price 15 है और stock 200 है।