Excel-এ VLOOKUP মাস্টার করুন: দক্ষ ডেটা রিট্রিভালের সম্পূর্ণ গাইড

আপনার কি কখনো বড় একটি স্প্রেডশীটে ডেটা রয়েছে Excel-এ এবং সেটি থেকে নির্দিষ্ট তথ্য ফিল্টার ও এক্সট্রাক্ট করার সহজ উপায় খুঁজছেন? আপনি যদি Excel-এ VLOOKUP ব্যবহার শিখেন, তবে শুধু একটি শক্তিশালী Excel ফাংশন দিয়ে এই লুকআপ করতে পারবেন।

Excel-এ VLOOKUP ফাংশন অনেকের ভয় আনে কারণ এর অনেক প্যারামিটার রয়েছে এবং এটিকে একাধিকভাবে ব্যবহার করা যায়। এই আর্টিকেলে আপনি VLOOKUP-এর সকল ব্যবহার ও এর শক্তি সম্পর্কে জানবেন।

Excel-এ VLOOKUP প্যারামিটারসমূহ

আপনি যেকোনো সেলে =VLOOKUP( টাইপ করা শুরু করলে সব উপলব্ধ ফাংশন প্যারামিটার দেখাবে একটি পপ-আপ।

প্রতিটি প্যারামিটার এবং তাদের অর্থ পরীক্ষা করি:

lookup_value: স্প্রেডশীট থেকে যে মানটি আপনি খুঁজছেন
table_array: সিটের वो রেঞ্জ যেটি আপনি সার্চ করতে চান
col_index_num: वो কলাম যেখান থেকে আপনি রেজাল্ট পেতে চান
[range_lookup]: ম্যাচ মোড (TRUE = এপ্রক্সিমেট, FALSE = একজ্যাক্ট)

এই চারটি প্যারামিটার দিয়ে আপনি ভড় ডেটাসেটের ভিতরে ডেটা সার্চের জন্য বহু উপকারী খোঁজ করতে পারেন।

একটি সহজ VLOOKUP Excel উদাহরণ

VLOOKUP বেসিক Excel ফাংশনগুলোতে একটি নয়, তাই একটি সহজ উদাহরণ দিয়ে শুরু করি।

নিচের উদাহরণের জন্য আমরা যুক্তরাষ্ট্রের স্কুলের SAT স্কোরের একটি বড় স্প্রেডশীট ব্যবহার করব। এই স্প্রেডশীটে ৪৫০টিরও বেশি স্কুল রয়েছে পড়ালেখা, ম্যাথ এবং রাইটিং-এর индивидуয়াল SAT স্কোরসহ। ডাউনলোড করে ফলো করতে পারেন। একটি এক্সটার্নাল কানেকশন ডেটা পুল করে, তাই ফাইল খোলার সময় ওয়ার্নিং আসবে কিন্তু সেটি সেফ।

এত বড় ডেটাসেটে সার্চ করে আপনার পছন্দের স্কুল খুঁজে বের করা খুব টাইম কনসামিং।

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

এরপর VLOOKUP ফাংশন ব্যবহার করে এই তিনটি ফিল্ড কাজ করাতে হবে। Reading ফিল্ডে VLOOKUP ফাংশন নিচের মতো তৈরি করুন:

=VLOOKUP( টাইপ করুন
School ফিল্ডটি সিলেক্ট করুন, এই উদাহরণে I2। কমা টাইপ করুন।
ডেটা যা সার্চ করতে চান সেসব সেলের পুরো রেঞ্জ সিলেক্ট করুন। কমা টাইপ করুন।

রেঞ্জ সিলেক্ট করার সময় আপনি সার্চ কলাম থেকে শুরু করতে পারেন (এই কেসে স্কুল নামের কলাম) এবং তারপর অন্যান্য সকল কলাম এবং রো সিলেক্ট করুন যেগুলো ডেটা ধারণ করে।

নোট: Excel-এ VLOOKUP ফাংশন শুধু সার্চ কলামের ডান পাশের সেলগুলো সার্চ করতে পারে। এই উদাহরণে স্কুল নামের কলাম ডেটার বাম পাশে থাকতে হবে যা আপনি লুকআপ করছেন।

Reading স্কোর পেতে হলে লेफটমোস্ট সিলেক্টেড কলাম থেকে ৩ নম্বর কলাম সিলেক্ট করতে হবে। তাই 3 টাইপ করুন এবং আবার কমা টাইপ করুন।
শেষে একজ্যাক্ট ম্যাচের জন্য FALSE টাইপ করুন এবং ) দিয়ে ফাংশন বন্ধ করুন।

আপনার ফাইনাল VLOOKUP ফাংশনটি এরকম দেখাবে:

=VLOOKUP(I2,B2:G461,3,FALSE)

প্রথমে Enter চাপলে Reading ফিল্ডে #N/A দেখাবে। কারণ School ফিল্ড খালি এবং VLOOKUP-কে খুঁজে বের করার জন্য কিছু নেই। কিন্তু যেকোনো হাই স্কুলের নাম লিখলে আপনি সেই রোয়ের Reading স্কোরের সঠিক রেজাল্ট দেখতে পাবেন।

VLOOKUP-এর কেস-সেনসিটিভ সমস্যা কীভাবে সমাধান করবেন

আপনি লক্ষ্য করতে পারেন যদি আপনি ডেটাসেটে যেভাবে লিস্ট করা আছে সেভাবে কেসে স্কুলের নাম না লিখেন, তবে কোনো রেজাল্ট দেখতে পাবেন না।

এটি হওয়ার কারণ VLOOKUP ফাংশন কেস-সেনসিটিভ। বড় ডেটাসেটের জন্য এটি বিরক্তিকর, যেখানে সার্চ কলামটি ক্যাপিটালাইজেশন কেসে ইনকনসিস্টেন্ট।

এটি এড়াতে, আপনি যা সার্চ করছেন সেটিকে লুকআপের আগে লোয়ারকেসে কনভার্ট করতে ফোর্স করতে পারেন। একে করতে সার্চ কলামের পাশে একটি নতুন কলাম তৈরি করুন। ফাংশনটি টাইপ করুন:

=TRIM(LOWER(B2))

এটি স্কুলের নাম লোয়ারকেসে কনভার্ট করবে এবং এক্সট্রা ক্যারেক্টার (স্পেস) রিমুভ করবে নামের লেফট বা রাইট সাইড থেকে।

Shift কী ধরে মাউস কার্সরটি প্রথম সেলের নিচের ডান কোণে রেখে ডাবল ক্লিক করুন পুরো কলাম অটোফিল করার জন্য।

শেষে, যেহেতু VLOOKUP এই সেলগুলোর ফর্মুলা ব্যবহার করার চেষ্টা করবে টেক্সটের বদলে, তাই সেগুলোকে ভ্যালু ওনলিতে কনভার্ট করতে হবে। একে করতে পুরো কলাম কপি করুন, প্রথম সেলে রাইট-ক্লিক করুন এবং Paste Values Only সিলেক্ট করুন।

এখন যেহেতু আপনার সব ডেটা এই নতুন কলামে ক্লিন হয়ে গেছে, আপনার VLOOKUP ফাংশনটি কিছুটা মডিফাই করুন এই নতুন কলাম ব্যবহার করার জন্য B2-এর বদলে C2 থেকে লুকআপ রেঞ্জ শুরু করে।

=VLOOKUP(I2,C2:G461,3,FALSE)

এখন আপনি লক্ষ্য করবেন যদি আপনি সবসময় লোয়ারকেসে সার্চ টাইপ করেন, তবে আপনি সবসময় ভালো সার্চ রেজাল্ট পাবেন।

এটি একটি হ্যান্ডি Excel টিপ VLOOKUP-এর কেস-সেনসিটিভ হওয়ার ফ্যাক্ট অপসারণের জন্য।

VLOOKUP এপ্রক্সিমেট ম্যাচ

একজ্যাক্ট ম্যাচ লুকআপ উদাহরণ এই আর্টিকেলের প্রথম সেকশনে বর্ণিত হলো সহজ, কিন্তু এপ্রক্সিমেট ম্যাচ কিছুটা কমপ্লেক্স।

এপ্রক্সিমেট ম্যাচ নাম্বার রেঞ্জের মাধ্যমে সার্চ করার জন্য সেরা। এটা সঠিকভাবে করার জন্য সার্চ রেঞ্জটি সঠিকভাবে সোর্টেড থাকতে হবে। এর সেরা উদাহরণ হলো একটি নাম্বার গ্রেডের সাথে মিলে যাওয়া লেটার গ্রেড সার্চ করার জন্য VLOOKUP ফাংশন।

যদি একজন শিক্ষকের বছর भर হোমওয়ার্ক গ্রেডের একটি লম্বা লিস্ট থাকে ফাইনাল এভারেজড কলামসহ, তবে সেটার সাথে মিলে যাওয়া লেটার গ্রেড স্বয়ংক্রিয়ভাবে আসা চমৎকার হতো।

এটি VLOOKUP ফাংশন দিয়ে সম্ভব। শুধু ডান পাশে একটি লুকআপ টেবিল থাকতে হবে যেটিতে প্রতিটি নাম্বার স্কোর রেঞ্জের জন্য উপযুক্ত লেটার গ্রেড থাকবে।

এখন VLOOKUP ফাংশন এবং এপ্রক্সিমেট ম্যাচ ব্যবহার করে আপনি সঠিক নাম্বার রেঞ্জের সাথে মিলে যাওয়া সঠিক লেটার গ্রেড খুঁজে পেতে পারেন।

এই VLOOKUP ফাংশনে:

lookup_value: F2, ফাইনাল এভারেজড গ্রেড
table_array: I2:J8, লেটার গ্রেড লুকআপ রেঞ্জ
index_column: ২, লুকআপ টেবিলের দ্বিতীয় কলাম
[range_lookup]: TRUE, এপ্রক্সিমেট ম্যাচ

G2-এ VLOOKUP ফাংশন ফিনিশ করে Enter চাপলে, আপনি আগের সেকশনে বর্ণিত পদ্ধতিতে বাকি সেলগুলো ফিল আপ করতে পারেন। আপনি সকল লেটার গ্রেড সঠিকভাবে ফিল্ড হয়ে দেখতে পাবেন।

মনে রাখবেন Excel-এ VLOOKUP ফাংশন গ্রেড রেঞ্জের বটম এন্ড থেকে সার্চ করে যেটি এসাইন করা লেটার স্কোর থেকে পরবর্তী লেটার স্কোরের রেঞ্জের টপ পর্যন্ত।

তাই "C" হলো লোয়ার রেঞ্জের (৭৫) এসাইন করা লেটার, এবং B তার নিজের লেটার রেঞ্জের মিনিমামে এসাইন করা। VLOOKUP ৬০ (D) এর রেজাল্ট "ফাইন্ড" করবে ৬০ থেকে ৭৫-এর যেকোনো মানের জন্য ক্লোসেস্ট এপ্রক্সিমেট ভ্যালু হিসেবে।

Excel-এ VLOOKUP একটি খুব শক্তিশালী ফাংশন যা অনেক বছর ধরে উপলব্ধ। এটি Excel ওয়ার্কবুকে যেকোনো জায়গায় ম্যাচিং ভ্যালু খুঁজে বের করার জন্যও উপকারী।

তবে মনে রাখবেন, যেসব Microsoft ব্যবহারকারীর पास মাসিক Office 365 সাবস্ক্রিপশন রয়েছে তাদের কাছে nieuwere XLOOKUP ফাংশন অ্যাক্সেসIBLE। এই ফাংশনে বেশি প্যারামিটার এবং অতিরিক্ত নমনীয়তা রয়েছে। সেমি-অ্যানুয়াল সাবস্ক্রিপশন ব্যবহারকারীদের জুলাই ২০২০-এ আপডেট রোল আউট হওয়ার জন্য অপেক্ষা করতে হবে।