এক্সেলে একাধিক কৃতেরিয়া দিয়ে লুকআপ ফর্মুলা তৈরি করার পদ্ধতি

জানতে হবে

• প্রথমে একটি INDEX ফাংশন তৈরি করুন, তারপর নেস্টেড MATCH ফাংশন শুরু করুন Lookup_value আর্গুমেন্ট এন্ট্রি করে।
• এরপর Lookup_array আর্গুমেন্ট এবং Match_type আর্গুমেন্ট যোগ করুন, তারপর কলাম রেঞ্জ নির্দিষ্ট করুন।
• তারপর নেস্টেড ফাংশনকে অ্যারে ফর্মুলায় রূপান্তর করুন Ctrl+Shift+Enter চাপে। অবশেষে ওয়ার্কশিটে সার্চ টার্ম যোগ করুন।

এই নিবন্ধে ব্যাখ্যা করা হয়েছে কীভাবে এক্সেলে একাধিক কৃতেরিয়া ব্যবহার করে লুকআপ ফর্মুলা তৈরি করতে হয় একটি ডেটাবেস বা ডেটা টেবিলে তথ্য খুঁজে বের করতে অ্যারে ফর্মুলা ব্যবহার করে। এই অ্যারে ফর্মুলা MATCH ফাংশনকে INDEX ফাংশনের ভেতরে নেস্ট করে তৈরি করা হয়। তথ্যটি Excel for Microsoft 365, Excel 2019, Excel 2016, Excel 2013, Excel 2010, এবং Excel for Mac-এ প্রযোজ্য।

টিউটোরিয়ালের সাথে সাথে চলে যান

এই টিউটোরিয়ালের ধাপগুলো অনুসরণ করতে, নিচের সেলগুলিতে স্যাম্পল ডেটা এন্ট্রি করুন, নিচের চিত্রে দেখানো মত। ৩ এবং ৪ নম্বর সারি খালি রাখা হয়েছে এই টিউটোরিয়ালে তৈরি হওয়া অ্যারে ফর্মুলার জন্য। (নোট: এই টিউটোরিয়ালে চিত্রে দেখানো ফরম্যাটিং অন্তর্ভুক্ত নয়।)

• শীর্ষের ডেটা রেঞ্জ D1 থেকে F2 সেলগুলিতে এন্ট্রি করুন।
• দ্বিতীয় রেঞ্জ D5 থেকে F11 সেলগুলিতে এন্ট্রি করুন।

এক্সেলে INDEX ফাংশন তৈরি করুন

INDEX ফাংশন এক্সেলের ছয়েক ফাংশনlerden একটি যachte একাধিক ফর্ম থাকে। ফাংশনটির একটি Array Form এবং একটি Reference Form রয়েছে। Array Form ডেটাবেস বা ডেটা টেবিল থেকে ডেটা রিটার্ন করে। Reference Form টেবিলে ডেটার সেল রেফারেন্স বা লোকেশন দেয়।

এই টিউটোরিয়ালে, Array Form ব্যবহার করা হয়েছে টাইটানিয়াম উইডজেটের সাপ্লায়ারের নাম খুঁজে বের করতে, সেল রেফারেন্সের বদলে।

INDEX ফাংশন তৈরি করতে নিচের ধাপগুলো অনুসরণ করুন:

1. F3 সেল সিলেক্ট করুন এক্টিভ সেল বানাতে। এই সেলে নেস্টেড ফাংশন এন্ট্রি করা হবে।

2. Formulas-এ যান।

   

3. Lookup & Reference বেছে নিন ফাংশন ড্রপ-ডাউন লিস্ট খোলার জন্য।

4. INDEX সিলেক্ট করুন Select Arguments ডায়ালগ বক্স খোলার জন্য।

5. array,row_num,column_num বেছে নিন।

6. OK ক্লিক করুন Function Arguments ডায়ালগ বক্স খোলার জন্য। Excel for Mac-এ Formula Builder খুলবে।

7. Array টেক্সট বক্সে কারসর রাখুন।

8. ওয়ার্কশিটে D6 থেকে F11 সেলগুলো হাইলাইট করুন রেঞ্জ ডায়ালগ বক্সে এন্ট্রি করার জন্য।

   Function Arguments ডায়ালগ বক্স খোলা রেখে দিন। ফর্মুলা এখনো শেষ নয়। নিচের নির্দেশাবলি অনুসরণ করে ফর্মুলা সম্পন্ন করুন।

   

নেস্টেড MATCH ফাংশন শুরু করুন

এক ফাংশনকে অন্য ফাংশনের ভেতরে নেস্ট করার সময়, দ্বিতীয় বা নেস্টেড ফাংশনের ফর্মুলা বিল্ডার খোলা সম্ভব নয়। নেস্টেড ফাংশনটি প্রথম ফাংশনের আর্গুমেন্ট হিসেবে এন্ট্রি করতে হয়।

ফাংশন ম্যানুয়ালি লিখতে হলে, ফাংশনের আর্গুমেন্টগুলো কমা দিয়ে আলাদা করা হয়।

নেস্টেড MATCH ফাংশন এন্ট্রি করার প্রথম ধাপ হলো Lookup_value আর্গুমেন্ট এন্ট্রি করা। Lookup_value হলো সার্চ টার্মের লোকেশন বা সেল রেফারেন্স যা ডেটাবেসে মিলিয়ে খুঁজা হবে।

Lookup_value শুধু একটি সার্চ কৃতেরিয়া বা টার্ম গ্রহণ করে। একাধিক কৃতেরিয়া খুঁজতে হলে, Lookup_value এক্সটেন্ড করতে হয় দুটি বা ততোধিক সেল রেফারেন্স কনক্যাটেনেট বা যোগ করে অ্যাম্পারস্যান্ড সিম্বল (&) ব্যবহার করে।

1. Function Arguments ডায়ালগ বক্সে, Row_num টেক্সট বক্সে কারসর রাখুন।

2. MATCH( লিখুন।

3. D3 সেল সিলেক্ট করুন সেল রেফারেন্স ডায়ালগ বক্সে এন্ট্রি করার জন্য।

4. D3 সেল রেফারেন্সের পর & (অ্যাম্পারস্যান্ড) লিখুন দ্বিতীয় সেল রেফারেন্স যোগ করার জন্য।

5. E3 সেল সিলেক্ট করুন দ্বিতীয় সেল রেফারেন্স এন্ট্রি করার জন্য।

6. E3 সেল রেফারেন্সের পর , (কমা) লিখুন MATCH ফাংশনের Lookup_value আর্গুমেন্ট এন্ট্রি সম্পন্ন করার জন্য।

   

   টিউটোরিয়ালের শেষ ধাপে, Lookup_values D3 এবং E3 সেলগুলিতে এন্ট্রি করা হবে।

নেস্টেড MATCH ফাংশন সম্পন্ন করুন

এই ধাপে নেস্টেড MATCH ফাংশনের Lookup_array আর্গুমেন্ট যোগ করা হবে। Lookup_array হলো সেল রেঞ্জ যেখানে MATCH ফাংশন Lookup_value আর্গুমেন্ট খুঁজবে (পূর্বের ধাপে যোগ করা)।

যেহেতু Lookup_value আর্গুমেন্টে দুটি সার্চ ফিল্ড শনাক্ত করা হয়েছে, Lookup_array-ও একইভাবে করতে হবে। MATCH ফাংশন নির্দিষ্ট কৃতেরিয়ার জন্য একটি অ্যারে খুঁজে। একাধিক অ্যারে এন্ট্রি করতে, অ্যাম্পারস্যান্ড ব্যবহার করে অ্যারেগুলো কনক্যাটেনেট করুন।

1. Row_num টেক্সট বক্সে বর্তমান এন্ট্রির শেষে কারসর রাখুন। কারসার কমার পর থাকবে।

2. ওয়ার্কশিটে D6 থেকে D11 সেলগুলো হাইলাইট করুন রেঞ্জ এন্ট্রি করার জন্য। এই রেঞ্জটি প্রথম অ্যারে যা ফাংশনটি খুঁজবে।

3. D6:D11 সেল রেফারেন্সের পর & (একটি অ্যাম্পারস্যান্ড) লিখুন। এই সিম্বল ফাংশনকে দুটি অ্যারে খুঁজতে বাধ্য করে।

4. ওয়ার্কশিটে E6 থেকে E11 সেলগুলো হাইলাইট করুন রেঞ্জ এন্ট্রি করার জন্য। এই রেঞ্জটি দ্বিতীয় অ্যারে যা ফাংশনটি খুঁজবে।

5. E3 সেল রেফারেন্সের পর , (একটি কমা) লিখুন MATCH ফাংশনের Lookup_array আর্গুমেন্ট এন্ট্রি সম্পন্ন করার জন্য।

   

6. পরবর্তী ধাপের জন্য ডায়ালগ বক্স খোলা রেখে দিন।

MATCH Type আর্গুমেন্ট যোগ করুন

MATCH ফাংশনের তৃতীয় এবং চূড়ান্ত আর্গুমেন্ট হলো Match_type আর্গুমেন্ট। এই আর্গামেন্ট এক্সেলকে বলে Lookup_value কীভাবে Lookup_array-এ থাকা মানগুলোর সাথে মেলানো হবে। উপলব্ধ অপশন: ১, ০, বা -১।

এই আর্গুমেন্ট অপশনাল। যদি অপবাদ করা হয়, ফাংশন ডিফল্ট ভ্যালু ১ ব্যবহার করে।

• যদি Match_type = ১ বা অপবাদ করা হয়, MATCH সেরা বড় মান খুঁজে বের করে যা Lookup_value-র চেয়ে ছোট বা সমান। Lookup_array ডেটা আসেন্ডিং অর্ডারে সোর্ট করা থাকতে হবে।
• যদি Match_type = ০, MATCH প্রথম সেই মান খুঁজে বের করে যা Lookup_value-এর সমান। Lookup_array ডেটা যেকোনো ক্রমে সোর্ট করা থাকতে পারে।
• যদি Match_type = -১, MATCH সেরা ছোট মান খুঁজে বের করে যা Lookup_value-এর চেয়ে বড় বা সমান। Lookup_array ডেটা ডিসেন্ডিং অর্ডারে সোর্ট করা থাকতে হবে।

পূর্বের ধাপে Row_num লাইনে কমা দেওয়ার পর এই ধাপগুলো অনুসরণ করুন:

1. Row_num টেক্সট বক্সে কমার পর 0 (শূন্য) লিখুন। এই সংখ্যা নেস্টেড ফাংশনকে D3 এবং E3 সেলে এন্ট্রি করা টার্মগুলোর এক্স্যাক্ট ম্যাচ রিটার্ন করতে বাধ্য করে।

2. ) (একটি ক্লোজিং রাউন্ড ব্র্যাকেট) লিখুন MATCH ফাংশন সম্পন্ন করার জন্য।

   

3. পরবর্তী ধাপের জন্য ডায়ালগ বক্স খোলা রেখে দিন।

INDEX ফাংশন সম্পন্ন করুন

MATCH ফাংশন সম্পন্ন হয়েছে। এখন ডায়ালগ বক্সের Column_num টেক্সট বক্সে যান এবং INDEX ফাংশনের শেষ আর্গুমেন্ট এন্ট্রি করুন। এই আর্গুমেন্ট এক্সেলকে বলে কলাম নম্বর কত D6 থেকে F11 রেঞ্জে। এই রেঞ্জ থেকে ফাংশন ফলাফল রিটার্ন করবে। এই কেসে, টাইটানিয়াম উইডজেটের সাপ্লায়ার।

1. Column_num টেক্সট বক্সে কারসর রাখুন।

2. 3 (নাম্বার তিন) লিখুন। এই সংখ্যা ফর্মুলাকে বলে D6 থেকে F11 রেঞ্জের তৃতীয় কলামে ডেটা খুঁজতে।

   

3. পরবর্তী ধাপের জন্য ডায়ালগ বক্স খোলা রেখে দিন।

অ্যারে ফর্মুলা তৈরি করুন

ডায়ালগ বক্স বন্ধ করার আগে, নেস্টেড ফাংশনকে অ্যারে ফর্মুলায় রূপান্তর করুন। এই অ্যারে ফাংশনকে একাধিক টার্ম টেবিলে খুঁজতে সক্ষম করে। এই টিউটোরিয়ালে দুটি টার্ম মেলানো হয়েছে: কলাম ১ থেকে Widgets এবং কলাম ২ থেকে Titanium।

এক্সেলে অ্যারে ফর্মুলা তৈরি করতে CTRL, SHIFT, এবং ENTER কীগুলো একসাথে চাপুন। একবার চাপলে, ফাংশনটি কার্লি ব্র্যাকেট দিয়ে ঘিরে যাবে, যা বোঝায় ফাংশনটি এখন একটি অ্যারে।

1. ডায়ালগ বক্স বন্ধ করতে OK ক্লিক করুন। Excel for Mac-এ Done সিলেক্ট করুন।

2. F3 সেল সিলেক্ট করুন ফর্মুলা দেখতে, তারপর ফর্মুলা বারে ফর্মুলার শেষে কারসর রাখুন।

3. ফর্মুলাকে অ্যারে এ রূপান্তর করতে CTRL+SHIFT+ENTER চাপুন।

4. F3 সেলে #N/A এরর দেখাবে। এটি সেই সেল যেখানে ফাংশন এন্ট্রি করা হয়েছিল।

5. #N/A এরর F3 সেলে দেখাচ্ছে কারণ D3 এবং E3 সেলগুলো খালি। D3 এবং E3 হলো সেলগুলো যেখানে ফাংশন Lookup_value খুঁজে। এই দুটি সেলে ডেটা যোগ হলে এররটি ডেটাবেস থেকে তথ্যে রিপ্লেস হয়ে যাবে।

   

সার্চ কৃতেরিয়া যোগ করুন

শেষ ধাপ হলো ওয়ার্কশিটে সার্চ টার্ম যোগ করা। এই ধাপে কলাম ১ থেকে Widgets এবং কলাম ২ থেকে Titanium মেলানো হবে।

যদি ফর্মুলা দুটি টার্মই ডেটাবেসের উপযুক্ত কলামগুলোতে খুঁজে পায়, তবে এটি তৃতীয় কলাম থেকে মান রিটার্ন করবে।

1. D3 সেল সিলেক্ট করুন।

2. Widgets লিখুন।

3. E3 সেল সিলেক্ট করুন।

4. Titanium টাইপ করুন, এবং Enter চাপুন।

5. সাপ্লায়ারের নাম, Widgets Inc., F3 সেলে দেখাবে। ডেটাবেসে টাইটানিয়াম উইডজেট বিক্রেতা একমাত্র এটি।

6. F3 সেল সিলেক্ট করুন। ফাংশনটি ওয়ার্কশিটের উপরে ফর্মুলা বারে দেখাবে।

   {=INDEX(D6:F11,MATCH(D3&E3,D6:D11&E6:E11,0),3)}

   এই উদাহরণে টাইটানিয়াম উইডজেটের জন্য একমাত্র সাপ্লায়ার রয়েছে। যদি একাধিক সাপ্লায়ার থাকত, ডেটাবেসে প্রথমে লিস্ট করা সাপ্লায়ারটি ফাংশন দ্বারা রিটার্ন হতো।