এক্সেল ডেটা ম্যানেজমেন্ট এবং ডেটা বিশ্লেষণের জন্য ব্যাপক লুকআপ কৌশল অফার করে। 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: এর মধ্যে অনুসন্ধান করার জন্য পরিসীমা বা অ্যারে৷ ৷
- রিটার্ন_অ্যারে: যে পরিসর বা অ্যারে থেকে এটি ফলাফল প্রদান করবে।
- [if_not_found]; [ঐচ্ছিক]: কোনো মিল না পাওয়া গেলে ফেরত দিতে হবে।
- [ম্যাচ_মোড]; [ঐচ্ছিক]: মিলের ধরন উল্লেখ করুন। আপনি সঠিক, ওয়াইল্ডকার্ড বা আনুমানিক মিল বেছে নিতে পারেন।
- 0 - সঠিক মিল (ডিফল্ট)।
- 1 - সঠিক বা পরবর্তী বড়।
- -1 - সঠিক বা পরবর্তী ছোট।
- 2 – ওয়াইল্ডকার্ড ম্যাচ।
- [সার্চ_মোড]; [ঐচ্ছিক]: অনুসন্ধানের দিক নির্ধারণ করুন, যেমন প্রথম থেকে শেষ বা শেষ থেকে প্রথম
- 1 – প্রথম থেকে শেষ পর্যন্ত অনুসন্ধান করুন।
- -1 - শেষ থেকে প্রথম পর্যন্ত অনুসন্ধান করুন৷
- 2 – বাইনারি সার্চ (অধিক্রমের ক্রম)।
- -2 – বাইনারি সার্চ (অবরোহী ক্রম)।
1. একক মানদণ্ডের জন্য XLOOKUP ব্যবহার করা হচ্ছে
যে কোনো অর্ডারে প্রায় $100 খরচ করে এমন গ্রাহকের দিকে তাকানোর জন্য আমাদের বিক্রয় ডেটাসেট থেকে $100-এর নিকটতম বিক্রির পরিমাণ খুঁজে বের করা যাক। নিম্নলিখিত সূত্র সন্নিবেশ করান.
সূত্র:
=XLOOKUP(100, G2:G71, A2:G71, “পাওয়া যায়নি”, 1)
এই সূত্রটি G2:G71 পরিসরে 100 অনুসন্ধান করে এবং A2:G71 থেকে সংশ্লিষ্ট সারিটি ফেরত দেয়। যদি 100-এর মধ্যে সবচেয়ে কাছেরটি খুঁজে না পাওয়া যায়, তাহলে এটি "নট ফাউন্ড" প্রদর্শন করবে।
আউটপুট:
1007 1/4/2024 ড্যানিয়েল মার্টিনেজ পূর্ব 39.99 3 119.97
2. একাধিক মানদণ্ডের সাথে XLOOKUP ব্যবহার করা
আপনি XLOOKUP ব্যবহার করে একাধিক মানদণ্ড সংযুক্ত করে আরও জটিল লুকআপ সম্পাদন করতে পারেন৷ আসুন সূত্রটি অন্বেষণ করি।
সূত্র:
=XLOOKUP("মেলিসা লোপেজ" এবং "ওয়েস্ট", C2:C71 এবং D2:D71, A2:G71)
এই সূত্রটি গ্রাহকের নাম এবং অঞ্চলকে একত্রিত করে এবং সংযুক্ত লুকআপ অ্যারেগুলির মধ্যে সম্মিলিত মান খোঁজে। এটি নির্বাচিত পরিসর থেকে সংশ্লিষ্ট পুনরুদ্ধার করে।
আউটপুট:
1012 1/6/2024 মেলিসা লোপেজ ওয়েস্ট 79.99 2 159.98
উন্নত লুকআপ টেকনিকের জন্য INDEX-MATCH-MATCH
INDEX-MATCH-MATCH ব্যবহার করা হয় যখন আপনাকে সারি এবং কলাম উভয় মানদণ্ডের উপর ভিত্তি করে মানগুলি সন্ধান করতে হবে, 2D ডেটা টেবিলের জন্য আদর্শ৷
সিনট্যাক্স:
=INDEX(অ্যারে, MATCH(row_lookup_value, row_lookup_array, 0), =MATCH(column_lookup_value, column_lookup_array, 0))
- অ্যারে: সেল পরিসরে আপনি যে মানগুলি পুনরুদ্ধার করতে চান তা রয়েছে৷ ৷
- MATCH(row_lookup_value, row_lookup_array, 0): সারি নম্বর প্রদান করে।
- row_lookup_value: সারিগুলিতে অনুসন্ধান করার জন্য মান৷
- row_lookup_array: সারির রেঞ্জের মধ্যে অনুসন্ধান করা হয়।
- 0 - সঠিক মিল
- ম্যাচ(কলাম_লুকআপ_মান, কলাম_লুকআপ_অ্যারে, 0): কলাম নম্বর প্রদান করে।
- কলাম_লুকআপ_মান: কলামগুলিতে অনুসন্ধান করার জন্য মান৷
- কলাম_লুকআপ_অ্যারে: কলামের মধ্যে অনুসন্ধানের ব্যাপ্তি।
- 0 - সঠিক মিল
1. সারি এবং কলামের জন্য 2D লুকআপ
আসুন INDEX-MATCH-MATCH সূত্র ব্যবহার করে একটি নির্দিষ্ট বিক্রয় পরিমাণের একজন গ্রাহকের জন্য বিক্রয়ের পরিমাণ দেখি। নিম্নলিখিত সূত্র সন্নিবেশ করান.
=INDEX(A2:G71, MATCH("জন স্মিথ", C2:C71, 0), MATCH("বিক্রয় পরিমাণ", A1:G1, 0))
এই সূত্রটি C2:C71 রেঞ্জে জন স্মিথ এবং A1:G1-এ "বিক্রয় পরিমাণ" অনুসন্ধান করে৷ তারপরে এটি A2:G71 এ ছেদকারী মানটিতে ফিরে আসবে।
আউটপুট:
99.98
2. 3D লুকআপের জন্য উন্নত INDEX-MATCH-MATCH সূত্র
যেহেতু INDEX-MATCH-MATCH সাধারণত দ্বি-মাত্রিক লুকআপের জন্য হয়, আপনি একাধিক MATCH ফাংশন সহ একটি অ্যারে সূত্র ব্যবহার করে এটিকে ত্রিমাত্রিক লুকআপে প্রসারিত করতে পারেন। এটি বৃহৎ ডেটাসেট এবং জটিল মানদণ্ড মেলানোর জন্য আদর্শ, নমনীয়তা এবং সক্ষমতায় VLOOKUP-কে ছাড়িয়ে যায়৷
সূত্র:
=INDEX(G2:G71, MATCH(1, (C2:C71="John Smith") * (D2:D71="South") * (F2:F71=4), 0))
এই সূত্রটি তিনটি মানদণ্ডের সাথে মিল করে বিক্রয়ের পরিমাণ অনুসন্ধান করে:কলাম C-তে গ্রাহকের নাম, কলাম D-এ অঞ্চল এবং কলাম F-এ পরিমাণ। শর্তগুলিকে AND ফাংশন হিসাবে গুণন ব্যবহার করে একত্রিত করা হয়, MATCH-কে সেই সারি খুঁজে পেতে অনুমতি দেয় যেখানে সমস্ত মানদণ্ড পূরণ করা হয়, এবং INDEX corresponG7 থেকে মান ফেরত দেয়।
আউটপুট:
129.99
XLOOKUP এবং INDEX-MATCH ব্যবহার করার সুবিধা
কেন VLOOKUP থেকে XLOOKUP বেছে নিন?
- দ্বিমুখী অনুসন্ধান :XLOOKUP উভয় দিক থেকে মান খুঁজে পেতে পারে, যখন VLOOKUP শুধুমাত্র বাম থেকে ডানে অনুসন্ধান করতে পারে৷
- কোন কলাম সূচক নম্বর নেই৷ :কলামের সূচী নম্বর নির্দিষ্ট করার দরকার নেই তাই কলামের পরিবর্তন এটিকে প্রভাবিত করবে না।
- ডিফল্ট সঠিক মিল :ডিফল্টরূপে, XLOOKUP একটি সঠিক মিলের জন্য অনুসন্ধান করে, ত্রুটিগুলি হ্রাস করে৷
- XLOOKUP-এ ওয়াইল্ডকার্ড অনুসন্ধানগুলি৷ :আপনি * এবং এর মত ওয়াইল্ডকার্ড ব্যবহার করতে পারেন? টেক্সট সার্চ করার সময় XLOOKUP দিয়ে।
- ত্রুটি হ্যান্ডলিং৷ :কোন মিল না পাওয়া গেলে কী ফেরত দিতে হবে তা উল্লেখ করতে পারেন।
VLOOKUP-এর উপর INDEX-MATCH এর সুবিধাগুলি
- নমনীয় টেবিল গঠন :INDEX-MATCH কলাম সন্নিবেশ বা মুছে ফেলার দ্বারা প্রভাবিত হয় না৷
- বাম সন্ধান৷ :VLOOKUP এর বিপরীতে, INDEX-MATCH বাম দিকে দেখতে পারে।
- পারফরম্যান্স :VLOOKUP পুনঃগণনার তুলনায় MATCH কিছু ক্ষেত্রে দ্রুততর হওয়ায় বড় ডেটাসেটে আরও দক্ষ৷
উপসংহার
এক্সেলে উন্নত ডেটা পরিচালনার জন্য XLOOKUP এবং INDEX-MATCH-MATCH-এর মতো উন্নত লুকআপ কৌশলগুলি অপরিহার্য৷ এই ফাংশনগুলি স্বয়ংক্রিয়ভাবে ডেটা পরিবর্তনগুলি আপডেট করে। এই ফাংশনগুলি নমনীয়তা, নির্ভুলতা এবং পারফরম্যান্স প্রদান করে যা ঐতিহ্যগত VLOOKUP কে ছাড়িয়ে যায়, যা তাদের Excel দক্ষতাকে সমতল করতে চাওয়া সকলের জন্য গুরুত্বপূর্ণ করে তোলে৷
সমাধান সহ বিনামূল্যে উন্নত এক্সেল ব্যায়াম পান!