যখন কখনো অন্য ওয়ার্কশিট বা একই ওয়ার্কশিট থেকে মান আনতে হয়, তখন আমরা এক্সেলের VLOOKUP ফাংশন-এর চিন্তা করি। কিন্তু VLOOKUP ফাংশনের মূল সমস্যাটি হলো এটি একবারে শুধু একটি মান রিটার্ন করতে ডিজাইন করা। তাই একাধিক মান আনার জন্য একটু ভিন্ন চিন্তা করতে হয় যা এই আর্টিকেলে বর্ণনা করা হয়েছে।
আমরা এক্সেলের অন্যান্য ফাংশন যেমন INDEX, SMALL, MATCH, ROW, COLUMNS ইত্যাদিও উপযুক্তভাবে কম্বাইন করে একাধিক মান আনায় করতে পারি। এছাড়া এক্সেলের বিভিন্ন ফিচার যেমন Advanced Filter, AutoFilter, এবং Format as Table টুলও ব্যবহার করা যায়। এই টিউটোরিয়ালে আমরা মোট ৮টি পদ্ধতি আলোচনা করব: ১টি পদ্ধতি VLOOKUP ফাংশন দিয়ে, ৪টি অন্যান্য এক্সেল ফাংশন দিয়ে, এবং ৩টি এক্সেল টুল ও অপশন দিয়ে একাধিক মান ভিবিলুকআপ ও রিটার্ন করার জন্য।
আপনাকে এক্সেল ফাইলটি ডাউনলোড করে এর সাথে অনুশীলন করার পরামর্শ দেওয়া হচ্ছে।
এক্সেলে VLOOKUP দিয়ে একাধিক মান রিটার্ন করার ৮টি পদ্ধতি
এই টিউটোরিয়ালে আমরা একটি Employee Database ব্যবহার করব সব ৮টি পদ্ধতি প্রদর্শনের জন্য।
আর আলাপ না করে সব ৮টি পদ্ধতি এক এক করে দেখি।
১. এক্সেলে VLOOKUP ফাংশন ব্যবহার করে একাধিক মান রিটার্ন করা
আমরা জানি VLOOKUP ফাংশন একবারে শুধু একটি মান রিটার্ন করতে পারে। কিন্তু আমাদের একাধিক মান দরকার। হ্যাঁ, অন্য অপশনও রয়েছে। কিন্তু যদি আপনি নির্দিষ্টভাবে VLOOKUP ফাংশনই ব্যবহার করতে চান, তাহলে নিরাশ হবেন না। এর ওপর একটি উপায় রয়েছে। এই বিভাগে আপনি শিখবেন কীভাবে ডেটাসেট একটু টুইক করে VLOOKUP ফাংশন দিয়ে একাধিক মান রিটার্ন করা যায়। এই পদ্ধতিতে ব্যবহার হয়েছে COUNTIF ও VLOOKUP ফাংশন। নিচের ধাপগুলো অনুসরণ করুন:
🔗 ধাপসমূহ:
প্রথমে সব ডিপার্টমেন্টের নাম ইউনিক করতে হবে। তার জন্য:
❶ E5 সেল সিলেক্ট করুন।
❷ এখন সেলে ফর্মুলা লিখুন
=B5&COUNTIF(B5:B$13,B5)❸ এরপর ENTER বাটন চাপুন।
❹ Fill Handle আইকন টেনে Dept._Unique কলামের শেষ পর্যন্ত আনুন।
❺ এখন C16 সেল সিলেক্ট করুন এবং ফর্মুলা লিখুন
=VLOOKUP(B16,E5:F13,2,0)❻ এরপর ENTER বাটন চাপুন।
❼ অবশেষে Fill Handle আইকন টেনে Employee কলামের শেষ পর্যন্ত আনুন।
হয়ে গেলো।
২. এক্সেলে ভিবিলুকআপ দিয়ে একাধিক মান ভার্টিক্যালি আনা
ধরুন, আপনি Engineering ডিপার্টমেন্টে কতজন কর্মচারী কাজ করে তা জানতে চান এবং তাদের নাম কলামে ভার্টিক্যালি সাজাতে চান। যদি তাই হয়, তাহলে নিচের ধাপগুলো অনুসরণ করুন। এই পদ্ধতিতে ব্যবহার হয়েছে INDEX, SMALL, MATCH, এবং ROW ফাংশন।
🔗 ধাপসমূহ:
❶ প্রথমে ফলাফল রাখতে F5 সেল সিলেক্ট করুন।
❷ এরপর ফর্মুলা লিখুন
=INDEX($C$5:$C$13, SMALL(IF(($E$5=$B$5:$B$13), MATCH(ROW($B$5:$B$13), ROW($B$5:$B$13)), ""), ROWS($A$1:A1)))❸ তারপর ENTER বাটন চাপুন।
❹ এখন Fill Handle আইকন Employee কলামে নিচের দিকে টেনে আনুন।
␥ ফর্মুলা ব্যাখ্যা
- ROW($B$5:$B$13) ▶ অ্যারে আকারে সারি নম্বর রিটার্ন করে: {5;6;7;8;9;10;11;12;13}
- MATCH(ROW($B$5:$B$13), ROW($B$5:$B$13)) ▶ পূর্বের অ্যারেটিকে নিচের আকারে রূপান্তর করে: {1;2;3;4;5;6;7;8;9}
- IF(($C$15=$B$5:$B$13), MATCH(ROW($B$5:$B$13), ROW($B$5:$B$13)), "") ▶ শর্ত পূরণকারী সারি নম্বর রিটার্ন করে, অন্যথায় খালি: {"";2;"";4;"";"";"";"";9}
- SMALL(IF(($E$5=$B$5:$B$13), MATCH(ROW($B$5:$B$13), ROW($B$5:$B$13)), "") ▶ অ্যারেতে থেকে প্রথম ছোট নম্বর রিটার্ন করে: {"";2;"";4;"";"";"";"";9}
- INDEX($C$5:$C$13, SMALL(IF(($E$5=$B$5:$B$13),MATCH(ROW($B$5:$B$13), ROW($B$5:$B$13)), "") ▶ SMALL ফাংশন দিয়ে রিটার্নকৃত সারি ইনডেক্স নম্বর অনুযায়ী কর্মচারির নাম রিটার্ন করে।
৩. এক্সেলে ভিবিলুকআপ দিয়ে একাধিক মান হরিজেন্টালি রিটার্ন করা
ধরুন, এখন আপনি Engineering ডিপার্টমেন্টের কর্মচারীদের নাম সারি দিয়ে হরিজেন্টালি সাজাতে চান। এই পদ্ধতিতে ব্যবহার হয়েছে INDEX, SMALL, MIN, এবং ROW ফাংশন।
🔗 ধাপসমূহ:
❶ প্রথমে ফলাফল রাখতে C16 সেল সিলেক্ট করুন।
❷ এরপর ফর্মুলা লিখুন
=INDEX($C$5:$C$13, SMALL(IF($C$15=$B$5:$B$13, ROW($B$5:$B$13)-MIN(ROW($B$5:$B$13))+1, ""), COLUMNS($A$1:A1)))❸ তারপর ENTER বাটন চাপুন।
❹ এখন Fill Handle আইকন Employee সারির ডানপাশে টেনে আনুন।
␥ ফর্মুলা ব্যাখ্যা
- ROW($B$5:$B$13) ▶ অ্যারে আকারে সারি নম্বর রিটার্ন করে: {5;6;7;8;9;10;11;12;13}
- SMALL(IF($C$15=$B$5:$B$13,ROW($B$5:$B$13)-MIN(ROW($B$5:$B$13))+1, "") ▶ অ্যারেতে থেকে প্রথম ছোট নম্বর রিটার্ন করে।
- INDEX($C$5:$C$13, SMALL(IF($C$15=$B$5:$B$13,ROW($B$5:$B$13)-MIN(ROW($B$5:$B$13))+1, "") ▶ SMALL ফাংশন দিয়ে রিটার্নকৃত সারি ইনডেক্স নম্বর অনুযায়ী কর্মচারির নাম রিটার্ন করে।
৪. শর্তসাপেক্ষ এক্সেলে ভিবিলুকআপ দিয়ে একাধিক মান আনা
এই বিভাগে আমরা Employee কলামে সেই সকল কর্মচারির নাম রিটার্ন করব যারা Engineering ডিপার্টমেন্টে কাজ করে এবং Morning শিফটেও কাজ করে। এই পদ্ধতিতে ব্যবহার হয়েছে INDEX, SMALL, IFERROR, এবং ROW ফাংশন।
🔗 ধাপসমূহ:
❶ প্রথমে ফলাফল রাখতে H5 সেল সিলেক্ট করুন।
❷ এরপর ফর্মুলা লিখুন
=IFERROR(INDEX($C$5:$C$13,SMALL(IF(1=((--($F$5=$B$5:$B$13))*(--($G$5=$D$5:$D$13))), ROW($C$5:$C$13)-4,""), ROW()-4)),"")❸ তারপর ENTER বাটন চাপুন।
❹ এখন Fill Handle আইকন Employee কলামে নিচের দিকে টেনে আনুন।
␥ ফর্মুলা ব্যাখ্যা
এই ফর্মুলা আগের দুটি ফর্মুলার চেয়ে গড়ে মিলেছে। এখানে অপবাদের মাত্রা:
- ROW($C$5:$C$13)-4 ▶ অ্যারেতে সারি নম্বরগুলো রিটার্ন করে: {5;6;7;8;9;10;11;12;13}। এখানে সংখ্যা ৪ বোঝায় Employee কলাম হেডারের সারি নম্বর।
- ROW()-4 ▶ এখানে সংখ্যা ৪ বোঝায় আউটপুট সারি শুরু হওয়ার আগের সারি নম্বর।
- IFERROR ▶ এখানে IFERROR ফাংশন ব্যবহার হয়েছে ত্রুটি হলে কাস্টম আউটপুট দেওয়ার জন্য।
৫. এক্সেলে ভিবিলুকআপ দিয়ে একাধিক মান একই সেলে একসাথে আনা (All in One Cell)
এখন আমরা Engineering ডিপার্টমেন্টে কাজ करनेवाले সকল কর্মচারির নাম একই সেলে একসাথে রাখব। এই পদ্ধতিতে ব্যবহার হয়েছে TEXTJOIN ও IF ফাংশন।
🔗 ধাপসমূহ:
❶ প্রথমে ফলাফল রাখতে H5 সেল সিলেক্ট করুন।
❷ এরপর ফর্মুলা লিখুন
=TEXTJOIN(",",TRUE,IF($B$5:$B$13=$C$15,$C$5:$C$13,""))❸ তারপর ENTER বাটন চাপুন।
হয়ে গেলো।
␥ ফর্মুলা ব্যাখ্যা
এখানে IF ফাংশন শর্ত মেনে চলা সকল কর্মচারির নাম রিটার্ন করে এবং TEXTJOIN ফাংশন IF ফাংশন দিয়ে রিটার্নকৃত সকল নাম কমা দিয়ে जोড়ে দেয়।
আরও পড়ুন: এক্সেলে একটি সেলে VLOOKUP দিয়ে একাধিক মান আনার ২টি সহজ পদ্ধতি
৬. AutoFilter ব্যবহার করে এক্সেলে ভিবিলুকআপ দিয়ে একাধিক মান রিটার্ন করা
যদি আপনি এক্সেল ফর্মুলা এড়িয়ে যেতে চান, তাহলে ডেটা টেবিলের মাধ্যমে AutoFilter ফিচার ব্যবহার করে একাধিক মান আনায় করতে পারেন। এখানে আমরা Engineering ডিপার্টমেন্টে কাজ করা সকল কর্মচারির নাম বের করার চেষ্টা করব।
🔗 ধাপসমূহ:
❶ প্রথমে সম্পূর্ণ ডেটা টেবিল সিলেক্ট করুন।
❷ তারপর Data রিবনে যান।
❸ এরপর Filter অপশনে ক্লিক করুন।
❹ এখন Department কলাম হেডারের নিচে ফিল্টার আইকনে ক্লিক করুন।
❺ পপ-আপ মেনু থেকে Engineering অপশনে টিক দিন।
❻ তারপর OK বাটন চাপুন।
শাবাশ! আপনি কাজটি সম্পন্ন করলেন। এখন ফলাফল নিচের মতো দেখতে পাবেন:
৭. Advanced Filter ব্যবহার করে এক্সেলে ভিবিলুকআপ দিয়ে একাধিক মান একসাথে এক্সট্র্যাক্ট করা
এক্সেলে Advanced Filter নামে একটি ফিচার রয়েছে যার মাধ্যমে ডেটা টেবিলে ভার্টিক্যালি লুকআপ করে একসাথে একাধিক মান এক্সট্র্যাক্ট করা যায়। নিচের উদাহরণে আমরা Department কলাম হেডারের নিচে Engineering ডিপার্টমেন্ট বেছে নিয়েছি। এটিকে ব্যবহার করে ডেটা টেবিল সার্চ করে বের করব কারা Engineering ডিপার্টমেন্টে কাজ করে। ধাপগুলো নিচে দেওয়া হলো:
🔗 ধাপসমূহ:
❶ প্রথমে সম্পূর্ণ ডেটা টেবিল সিলেক্ট করুন।
❷ তারপর Data রিবনে যান।
❸ এরপর Advanced অপশনে ক্লিক করুন।
❹ শেষ ধাপ সম্পন্ন হলে স্ক্রিনে Advanced Filter নামক একটি ডায়লগ বক্স আসবে।
❺ এখন List range: বক্সে $B$4:$C$13
এবং Criteria range: বক্সে 'Advanced Filter'!$E$4:$E$5
লিখুন।
❻ এরপর OK বাটন চাপুন।
সব ধাপ সম্পন্ন হলে আউটপুট নিচের মতো দেখতে পাবেন:
৮. Format as Table ব্যবহার করে এক্সেলে ভিবিলুকআপ দিয়ে একাধিক মান আনা
এই পদ্ধতিটি AutoFilter এবং Advanced Filter অপশনের বিকল্প হিসেবে ব্যবহার করা যায়। এই পদ্ধতিতে আমরা Format as Table অপশন ব্যবহার করব Engineering ক্যাটাগরির অধীনে সকল কর্মচারির নাম সোর্ট করার জন্য। ধাপগুলো নিচে দেওয়া হলো:
🔗 ধাপসমূহ:
❶ প্রথমে সম্পূর্ণ ডেটা টেবিল সিলেক্ট করুন।
❷ তারপর Home রিবনে যান।
❸ এরপর Format as a Table অপশন সিলেক্ট করুন।
❹ তারপর আপনার পছন্দমতো যেকোনো Table Style সিলেক্ট করুন।
এখন আপনার ডেটা টেবিল নিচের ছবির মতো দেখাবে।
❺ এই পর্যায়ে Department কলাম হেডারের নিচে Filter icon-এ ক্লিক করুন।
❻ পপ-আপ মেনু থেকে শুধু Engineering অপশনে টিক দিন।
❼ তারপর OK বাটন চাপুন।
সব ধাপ সম্পন্ন হলে আপনার ফলাফল নিচের মতো দেখাবে:
মনে রাখবেলা বিষয়গুলো
📌 VLOOKUP ফাংশন একা একাধিক মান আনায় করতে পারে না।
📌 ফাংশনগুলোর সিনট্যাক্স সম্পর্কে সতর্ক থাকুন।
📌 ফর্মুলায় ডেটা রেঞ্জ সাবধানে ইনসার্ট করুন।
উপসংহার
শেষ কথা, আমরা এক্সেলে ভিবিলুকআপ দিয়ে একাধিক মান রিটার্ন করার ৮টি স্বতন্ত্র পদ্ধতি আলোচনা করেছি। আপনাকে এই আর্টিকেলের সাথে সংযুক্ত প্র্যাকটিস ওয়ার্কবুক ডাউনলোড করে সব পদ্ধতি অনুশীলন করার পরামর্শ দেওয়া হচ্ছে। এবং কমেন্ট সেকশনে যেকোনো প্রশ্ন জিজ্ঞাসা করতে দ্বিধা করবেন না। আমরা যতদ্রুত সম্ভব সব প্রাসঙ্গিক প্রশ্নের উত্তর দেওয়ার চেষ্টা করব।
সম্পর্কিত আর্টিকেল
- এক্সেলে একাধিক সারির সাথে VLOOKUP করার ৫টি পদ্ধতি
