এক্সেল ডেটা ম্যানেজমেন্ট এবং ডেটা অ্যানালাইসিসের জন্য ব্যাপক লুকআপ টেকনিক অফার করে। VLOOKUP ডেটা রিট্রিভের জন্য সাধারণত ব্যবহৃত হয়, কিন্তু এর লিমিটেশন আছে যেন: লুকআপ কলাম বাম পাশে থাকতে হয় এবং ইনফ্লেক্সিবল এরর হ্যান্ডলিং। এগুলো উলঘন করতে, XLOOKUP এবং INDEX-MATCH-MATCH-এর মতো উন্নত ফাংশনগুলো বেশি ফ্লেক্সিবিলিটি, কন্ট্রোল এবং দক্ষতা প্রদান করে। চলুন আপনাকে VLOOKUP-এর পেরে উন্নত লুকআপ টেকনিক দেখাই XLOOKUP, এবং INDEX-MATCH-MATCH ব্যবহার করে যাতে আপনি এদের কার্যকরভাবে লিভারেজ করতে পারেন।
চলুন একটি সেলস ডেটাসেট বিবেচনা করি প্র্যাক্টিক্যাল উদাহরণের সাথে উন্নত লুকআপ টেকনিক প্রয়োগ করার জন্য।
XLOOKUP এডভান্সড লুকআপ টেকনিক
XLOOKUP এক্সেলে সিংগল- বা মাল্টিপল-কলাম লুকআপের জন্য একটি ভার্সেটাইল ফাংশন। এটি যেকোনো ডিরেকশনে সার্চ করতে পারে (লेफ্ট-টু-রাইট, রাইট-টু-লেফ্ট, ভার্টিক্যাল, হরিজন্ট্যাল), কাস্টম এরর মেসেজ অফার করে, এবং লুকআপের জন্য সোর্টেড ডেটা দরকার নয়। XLOOKUP ফাংশন শুধু Excel 2021 এবং MS Office 365 ব্যবহারকারীদের জন্য উপলব্ধ। XLOOKUP ফাংশন লুকআপ ভ্যালুর ডেটা চেঞ্জেস অটো আপডেট করে।
সিনট্যাক্স:
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
- lookup_value: সার্চ করার মান।
- lookup_array: সার্চ করার রেঞ্জ বা অ্যারে।
- return_array: রেজাল্ট রিটার্ন করার রেঞ্জ বা অ্যারে।
- [if_not_found]; [ঐচ্ছিক]: কোনো ম্যাচ না পাওয়া গেলে রিটার্ন করার মান।
- [match_mode]; [ঐচ্ছিক]: ম্যাচ টাইপ স্পেসিফাই করুন। আপনি একজ্যাক্ট, 와일্ডকার্ড, বা অ্যप्रক্সিমেট ম্যাচিং বেছে নিতে পারেন।
- 0 – একজ্যাক্ট ম্যাচ (ডিফল্ট)।
- 1 – একজ্যাক্ট বা পরবর্তী বড় মান।
- -1 – একজ্যাক্ট বা পরবর্তী ছোট মান।
- 2 – 와일্ডকার্ড ম্যাচ।
- [search_mode]; [ঐচ্ছিক]: সার্চ ডিরেকশন ডিসাইড করুন, যেন: ফার্স্ট-টু-লাস্ট বা লাস্ট-টু-ফার্স্ট।
- 1 – ফার্স্ট থেকে লাস্ট সার্চ।
- -1 – লাস্ট থেকে ফার্স্ট সার্চ।
- 2 – বাইনারি সার্চ (অসেন্ডিং অর্ডার)।
- -2 – বাইনারি সার্চ (ডিসেন্ডিং অর্ডার)।
১. সিংগল ক্রাইটেরিয়ার জন্য XLOOKUP ব্যবহার
চলুন আমাদের সেলস ডেটাসেট থেকে $১০০-এর সবচেয়ে নিকটবর্তী সেলস অ্যামাউন্ট খুঁজে বের করি যাতে দেখতে পাই কাস্টমার কে অর্ডারে প্রায় $১০০ খরচ করেছে। নিচের ফর্মুলা ইনসার্ট করুন।
ফর্মুলা:
=XLOOKUP(100, G2:G71, A2:G71, “Not Found”, 1)
এই ফর্মুলা G2:G71 রেঞ্জে ১০০ সার্চ করে এবং A2:G71 থেকে সংশ্লিষ্ট রো রিটার্ন করে। যদি ১০০-এর নিকটবর্তী মান না পাওয়া যায়, তবে “Not Found” দেখাবে।
আউটপুট:
1007 1/4/2024 Daniel Martinez East 39.99 3 119.97
২. একাধিক ক্রাইটেরিয়ারসহ XLOOKUP ব্যবহার
আপনি একাধিক ক্রাইটেরিয়া কনক্যাটেনেট করে XLOOKUP ব্যবহার করে আরও কমপ্লেক্স লুকআপ পারফর্ম করতে পারেন। চলুন ফর্মুলাটি এক্সপ্লোর করি।
ফর্মুলা:
=XLOOKUP(“Melissa Lopez” & “West”, C2:C71 & D2:D71, A2:G71)
এই ফর্মুলা কাস্টমারের নাম এবং রিজন কনক্যাটেনেট করে এবং কনক্যাটেনেটেড লুকআপ অ্যারে মধ্যে কম্বাইনড ভ্যালু খুঁজে বের করে। এটি সিলেক্ট করা রেঞ্জ থেকে সংশ্লিষ্ট মান রিটার্ন করে।
আউটপুট:
1012 1/6/2024 Melissa Lopez West 79.99 2 159.98
INDEX-MATCH-MATCH এডভান্সড লুকআপ টেকনিকের জন্য
INDEX-MATCH-MATCH তখন ব্যবহার করা হয় যখন আপনাকে রো এবং কলাম উভয় ক্রাইটেরিয়া ভিত্তিক করে ভ্যালু লুকআপ করতে হয়, ২D ডেটা টেবিলের জন্য আদর্শ।
সিনট্যাক্স:
=INDEX(array, MATCH(row_lookup_value, row_lookup_array, 0), =MATCH(column_lookup_value, column_lookup_array, 0))
- array: রিট্রিভ করার ভ্যালু সম্বলিত সেল রেঞ্জ।
- MATCH(row_lookup_value, row_lookup_array, 0): রো নম্বর রিটার্ন করে।
- row_lookup_value: রোতে সার্চ করার মান।
- row_lookup_array: সার্চ করার রো রেঞ্জ।
- 0 – একজ্যাক্ট ম্যাচ
- MATCH(column_lookup_value, column_lookup_array, 0): কলাম নম্বর রিটার্ন করে।
- column_lookup_value: কলামে সার্চ করার মান।
- column_lookup_array: সার্চ করার কলাম রেঞ্জ।
- 0 – একজ্যাক্ট ম্যাচ
১. রো এবং কলামের জন্য ২D লুকআপস
চলুন INDEX-MATCH-MATCH ফর্মুলা ব্যবহার করে একটি কাস্টমারের নির্দিষ্ট সেলস অ্যামাউন্ট দেখি। নিচের ফর্মুলা ইনসার্ট করুন।
=INDEX(A2:G71, MATCH(“John Smith”, C2:C71, 0), MATCH(“Sales Amount”, A1:G1, 0))
এই ফর্মুলা C2:C71 রেঞ্জে John Smith এবং A1:G1-এ “Sales Amount” সার্চ করে। তারপর A2:G71-এ ইন্টারসেক্টিং ভ্যালু রিটার্ন করে।
আউটপুট:
99.98
২. ৩D লুকআপের জন্য অ্যাডভান্সড INDEX-MATCH-MATCH ফর্মুলা
যেহেতু INDEX-MATCH-MATCH সাধারণত টু-ডাইমেনশনাল লুকআপের জন্য, আপনি একাধিক MATCH ফাংশনসহ অ্যারে ফর্মুলা ব্যবহার করে এটিকে থ্রি-ডাইমেনশনাল লুকআপে এক্সটেন্ড করতে পারেন। এটি বড় ডেটাসেট এবং কমপ্লেক্স ক্রাইটেরিয়া ম্যাচিং-এর জন্য আদর্শ, VLOOKUP-এর চেয়ে ফ্লেক্সিবিলিটি এবং ক্যাপাবিলিটিতে ওটপারফর্ম করে।
ফর্মুলা:
=INDEX(G2:G71, MATCH(1, (C2:C71=”John Smith”) * (D2:D71=”South”) * (F2:F71=4), 0))
এই ফর্মুলা তিনটি ক্রাইটেরিয়া ম্যাচ করে Sales Amount খুঁজে বের করে: কলাম C-এ কাস্টমার নাম, কলাম D-এ রিজন, এবং কলাম F-এ কোয়ান্টিটি। কন্ডিশনগুলো গুণফল হিসেবে কম্বাইন করা হয়েছে AND ফাংশন হিসেবে, MATCH-কে সক্ষম করে সেই রো খুঁজে বের করতে যেখানে সব ক্রাইটেরিয়া মেট হয়, এবং INDEX G2:G71 থেকে সংশ্লিষ্ট মান রিটার্ন করে।
আউটপুট:
129.99
XLOOKUP এবং INDEX-MATCH ব্যবহারের সুবিধা
কেন VLOOKUP-এর পরিবর্তে XLOOKUP বেছে নেবেন?
- Bidirectional Search: XLOOKUP উভয় ডিরেকশনে ভ্যালু লুকআপ করতে পারে, যেখানে VLOOKUP শুধু লেফ্ট টু রাইট সার্চ করতে পারে।
- No Column Index Number: কলাম ইন্ডেক্স নম্বর স্পেসিফাই করার প্রয়োজন নেই, তাই কলাম চেঞ্জের প্রভাব পড়ে না।
- Default Exact Match: ডিফল্টরূপে XLOOKUP একজ্যাক্ট ম্যাচ সার্চ করে, এরর মিনিমাইজ করে।
- Wildcard Searches in XLOOKUP: টেক্সট সার্চ পারফর্ম করার সময় XLOOKUP-এ * এবং ?-র মতো 와일্ডকার্ড ব্যবহার করতে পারেন।
- Error Handling: কোনো ম্যাচ না পাওয়া গেলে কী রিটার্ন করবে সেটি আপনি에ক্সপ্রেস করতে পারেন।
VLOOKUP-এর ওপর INDEX-MATCH-এর সুবিধা
- Flexible Table Structure: কলাম ইনসার্শন বা ডিলিশন থেকে INDEX-MATCH প্রভাবিত হয় না।
- Left Lookup: VLOOKUP-এর বিপরীতে, INDEX-MATCH বাম পাশে লুকআপ করতে পারে।
- Performance: বড় ডেটাসেটে বেশি দক্ষ, কারণ MATCH কিছু কেসে VLOOKUP রিক্যালকুলেট করার চেয়ে দ্রুত।
উপসংহার
XLOOKUP এবং INDEX-MATCH-MATCH-এর মতো উন্নত লুকআপ টেকনিক এক্সেলে অ্যাডভান্সড ডেটা হ্যান্ডলিং-এর জন্য অপরিহার্য। এই ফাংশনগুলো ডেটা চেঞ্জেস অটোমেটিক্যালি আপডেট করে। এই ফাংশনগুলো ট্র্যাডিশনাল VLOOKUP-এর চেয়ে ফ্লেক্সিবিলিটি, অ্যাকুরেসি এবং পারফরম্যান্স প্রদান করে যেগুলো যেকোনো ব্যক্তির জন্য জরুরি যিনি তাদের এক্সেল স্কিল লেভেল আপ করতে চান।
Get FREE Advanced Excel Exercises with Solutions!
