ডেটা ম্যাপিং এক্সেল-এর একটি অপরিহার্য অংশ। তাই ডেটা ম্যাপ করার কিছু হ্যান্ডি মেথড জানা আপনার অনেক সময় বাঁচাতে পারে এবং ওয়ার্কফ্লো উন্নত করতে পারে। এই কথা ধরে এই আর্টিকেলে ৪টি উপকারী পদ্ধতি দেখানো হয়েছে এক্সেল VLOOKUP-এ ডেটা ম্যাপ করার। 더불어, আমরা এখানে একটি ভ্যালুর Nth occurrence কীভাবে পেতে হয় এবং VLOOKUP ফাংশন ব্যবহার করে এরর হাইড করা সেটাও আলোচনা করব।
নিচের লিংক থেকে প্র্যাকটিস ওয়ার্কবুক ডাউনলোড করতে পারেন।
VLOOKUP ব্যবহার করে এক্সেলে ডেটা ম্যাপ করার ৪টি পদ্ধতি
এই আর্টিকেলে আমরা VLOOKUP ফাংশনকে MATCH, COUNTIF, INDIRECT, এবং IF ফাংশন-এর সাথে কম্বাইন করে ডেটা ম্যাপ করব। আর দেরি না করে শুরু করা যাক! এখানে আমরা Microsoft Excel 365 ভার্সন ব্যবহার করেছি, আপনি সুবিধা অনুযায়ী অন্য ভার্সনও ব্যবহার করতে পারেন।
পদ্ধতি-১: VLOOKUP ফাংশন ব্যবহার করে এক্সেলে ডেটা ম্যাপ করা
সবচেয়ে সহজ পদ্ধতিটিই থেকে শুরু করি অর্থাৎ VLOOKUP ফাংশন ব্যবহার করে এক্সেলে ডেটা ম্যাপ করা। চলুন শুরু করি। B4:D14 সেল রেঞ্জে দেখানো ডেটাসেটটি বিবেচনা করুন। এখানে ডেটাসেটটি কর্মচারীদের ID, তাদের নাম, এবং যেখানে তারা কাজ করে সে ডিপার্টমেন্ট-কে দেখাচ্ছে।
ধাপসমূহ:
- প্রথমে G5 সেলে নেভিগেট করুন এবং নিচের এক্সপ্রেশনটি লিখুন।
=VLOOKUP(G4,B5:D14,3,FALSE)
এই ফর্মুলাতে, G4 সেলটি ID 1008 কে রেফার করে এবং B5:D14 সেল রেঞ্জটি ID, নাম, এবং ডিপার্টমেন্ট কলামকে রিপ্রেজেন্ট করে।
ফর্মুলা ব্রেকডাউন:
- VLOOKUP(G4,B5:D14,3,FALSE) → একটি টেবিলের বামপাশের কলামে একটি মান খুঁজে বের করে, এবং তারপর আপনার নির্দিষ্ট কলাম থেকে একই সারির একটি মান রিটার্ন করে। এখানে, G4 ( lookup_value আর্গুমেন্ট) B5:D14 (table_array আর্গুমেন্ট) অ্যারে থেকে ম্যাপ করা হয়েছে। এরপর, 3 (col_index_num আর্গুমেন্ট) লুকআপ ভ্যালুর কলাম নম্বরকে রিপ্রেজেন্ট করে। শেষে, FALSE (range_lookup আর্গুমেন্ট) লুকআপ ভ্যালুর Exact match-কে বোঝায়।
- আউটপুট → Marketing
শেষে, ফলাফল নিচের ছবির মতো দেখাবে।
পদ্ধতি-২: VLOOKUP এবং MATCH ফাংশন দিয়ে ডেটা ম্যাপ করা (Two-way VLOOKUP)
দ্বিতীয় পদ্ধতিতে, আমরা একটি নির্দিষ্ট সারি এবং কলামের ইন্টারসেকশনে একটি মান ম্যাপ করতে VLOOKUP এবং MATCH ফাংশন কম্বাইন করব। এটি Two-way VLOOKUP-ও বলা হয়। চলুন এটি একশনে দেখি। B4:E12 সেলে দেখানো Sales List ডেটাসেটটি ধরে নিই। এখানে আমাদের Items এবং January, February, এবং March-এ Units Sold-এর লিস্ট আছে।
ধাপসমূহ:
- প্রথমে Item এবং Month সিলেক্ট করুন, উদাহরণস্বরূপ আমরা Television এবং March বেছে নিলাম।
- তারপর H6 সেলে যান এবং নিচের ফর্মুলা লিখুন।
=VLOOKUP(H4, B6:E10, MATCH(H5, B5:E5, 0), FALSE)
এখানে, H4 এবং H5 সেলগুলো যথাক্রমে Item এবং Month-কে রেফার করে যখন B5:E5 কলাম হেডারগুলোকে রিপ্রেজেন্ট করে।
ফর্মুলা ব্রেকডাউন:
- MATCH(H5, B5:E5, 0) → একটি অ্যারে থেকে দেওয়া ভ্যালুর সাথে মিলে যাওয়া আইটেমের রিলেটিভ পজিশন রিটার্ন করে। এখানে, H5 হলো lookup_value আর্গুমেন্ট যা March মাস-কে রেফার করে। এরপর, B5:E5 lookup_array আর্গুমেন্টকে রিপ্রেজেন্ট করে যেখানে থেকে ভ্যালু ম্যাচ করা হয়। শেষে, 0 অপশনাল match_type আর্গুমেন্ট যা Exact match ক্রাইটেরিয়া ইন্ডিকেট করে।
- আউটপুট → 4
- VLOOKUP(H4, B6:E10, MATCH(H5, B5:E5, 0), FALSE) → হয়ে যায়
- VLOOKUP(H4, B6:E10, 4, FALSE) → এখানে, H4 ( lookup_value আর্গুমেন্ট) B6:E10 (table_array আর্গুমেন্ট) অ্যারে থেকে ম্যাপ করা হয়েছে। এরপর, 4 (col_index_num আর্গুমেন্ট) লুকআপ ভ্যালুর কলাম নম্বরকে রিপ্রেজেন্ট করে। শেষে, FALSE (range_lookup আর্গুমেন্ট) লুকআপ ভ্যালুর Exact match-কে বোঝায়।
- আউটপুট → 243
শেষে, আপনার রেজাল্ট নিচের ছবির মতো দেখাবে।
পদ্ধতি-৩: VLOOKUP এবং COUNTIF ফাংশন ব্যবহার করে ডেটা ম্যাপ করা
এক্সেলে ডেটা ম্যাপ করার আরেকটি পদ্ধতি হচ্ছে COUNTIF ফাংশন-কে VLOOKUP ফাংশনের ভেতরে ইনকর্পোরেট করা। এটি সহজ ও সহজ, শুধু এই ধাপগুলো অনুসরণ করুন। B4:C11 সেলে দেখানো Best Selling Books ডেটাসেটটি বিবেচনা করুন। এই ডেটাসেটে আমাদের Best Seller-এর নাম এবং তার Price USD-তে আছে।
ধাপসমূহ:
- শুরু করতে F5 সেলে যান এবং নিচের এক্সপ্রেশনটি লিখুন।
=IF(COUNTIF(B5:B9,F4),VLOOKUP(F4,B5:C9,2,TRUE),0)
এই এক্সপ্রেশনে, B5:C9 সেল রেঞ্জটি Best Seller Book এবং Price কলামকে রিপ্রেজেন্ট করে। বিপরীতে, F4 সেলটি Best Seller-কে রেফার করে (এখানে এটি House of Wisdom)।
ফর্মুলা ব্রেকডাউন:
- COUNTIF(B5:B9,F4) → একটি রেঞ্জের মধ্যে দেওয়া কন্ডিশন পূরণ করা সেলের সংখ্যা কাউন্ট করে। এখানে, B5:B9 হলো range আর্গুমেন্ট যা Best Seller Books-কে রেফার করে। এরপর, F4 criteria আর্গুমেন্টকে রিপ্রেজেন্ট করে যা ম্যাচ করা ভ্যালুর কাউন্ট রিটার্ন করে।
- আউটপুট → 1
- VLOOKUP(F4,B5:C9,2,TRUE) → এখানে, F4 ( lookup_value আর্গুমেন্ট) B5:C9 (table_array আর্গুমেন্ট) অ্যারে থেকে ম্যাপ করা হয়েছে। এরপর, 2 (col_index_num আর্গুমেন্ট) লুকআপ ভ্যালুর কলাম নম্বরকে রিপ্রেজেন্ট করে। শেষে, TRUE (range_lookup আর্গুমেন্ট) লুকআপ ভ্যালুর Approximate match-কে বোঝায়।
- আউটপুট → 25
- IF(COUNTIF(B5:B9,F4),VLOOKUP(F4,B5:C9,2,TRUE),0) → হয়ে যায়
- IF(1,25,0) → চেক করে কন্ডিশন পূরণ হয়েছে কিনা এবং TRUE হলে একটা ভ্যালু এবং FALSE হলে অন্য ভ্যালু রিটার্ন করে। এখানে, 1 হলো logical_test আর্গুমেন্ট যা IF ফাংশনকে 25 (value_if_true আর্গুমেন্ট) রিটার্ন করতে বাধ্য করে অন্যথায় এটি 0 (value_if_false আর্গুমেন্ট) রিটার্ন করে।
- আউটপুট → $25
ফলস্বরূপ, ফলাফল নিচের স্ক্রিনশটের মতো দেখাবে।
পদ্ধতি-৪: VLOOKUP এবং INDIRECT ফাংশন ব্যবহার করে এক্সেলে ডেটা ম্যাপ করা
আপনি INDIRECT এবং VLOOKUP ফাংশন কম্বাইন করে এক্সেলে ডেটা ম্যাপ করতে পারেন। এখন ধাপগুলো পরীক্ষা করি। B4:I10 সেলে দেখানো Grocery List ডেটাসেটটি ধরে নিই। ডেটাসেটটি US-এর ৩টি সিটিতে একই Items-এর Price দেখাচ্ছে।
ধাপসমূহ:
- প্রথমে B5 সেলে যান >> Data ট্যাবে ক্লিক করুন >> তারপর Data Validation ড্রপ-ডাউন চাপুন।
এটি Data Validation ডায়ালগ বক্স খুলবে।
- দ্বিতীয়, Allow ড্রপ-ডাউনে List অপশন বেছে নিন >> Source ফিল্ডে B6:B10 সেলগুলো সিলেক্ট করুন >> OK বাটনে ক্লিক করুন।
- তৃতীয়, B6:C10 সেলগুলো সিলেক্ট করুন >> Formulas ট্যাবে যান >> Define Name অপশনে ডবল ক্লিক করুন।
এটি Edit Name উইজার্ড খুলবে।
- এখানে ডেটা রেঞ্জের জন্য উপযুক্ত একটি নাম দিন (এই কেসে, Boston) >> OK বাটনে ক্লিক করুন।
একইভাবে Atlanta-এর জন্য Named Range ডিফাইন করুন।
একইভাবে Denver-এর জন্যও Named Range ডিফাইন করুন।
- চতুর্থ, C14 সেলে নেভিগেট করুন >> Data tab-এ, এবং Data Validation বাটনে ক্লিক করুন।
- একইভাবে, List অপশন বেছে নিন >> স্ক্রিনশটের মতো Named Ranges ইনপুট করুন।
এখন ড্রপ-ডাউন থেকে Item এবং Location বেছে নিন। উদাহরণস্বরূপ, আমরা Tomato এবং Atlanta বেছে নিলাম।
- उसके बाद, D14 সেলে নিচের এক্সপ্রেশনটি টাইপ করুন।
=VLOOKUP(B14,INDIRECT(C14),2,FALSE)
এখানে, B14 এবং C14 সেলগুলো যথাক্রমে Item এবং Location-কে পয়েন্ট করে।
ফর্মুলা ব্রেকডাউন:
- INDIRECT(C14) → এক টেক্সট স্ট্রিং দ্বারা স্পেসিফাইড রেফারেন্স রিটার্ন করে। এখানে, C14 হলো ref_text আর্গুমেন্ট যা Named Range Boston-কে রেফার করে।
- VLOOKUP(B14,INDIRECT(C14),2,FALSE) → এখানে, B14 ( lookup_value আর্গুমেন্ট) Named Range, INDIRECT(C14) অর্থাৎ table_array আর্গুমেন্ট থেকে ম্যাপ করা হয়েছে। এরপর, 2 (col_index_num আর্গুমেন্ট) লুকআপ ভ্যালুর কলাম নম্বরকে রিপ্রেজেন্ট করে। শেষে, FALSE (range_lookup আর্গুমেন্ট) লুকআপ ভ্যালুর Exact match-কে বোঝায়।
- আউটপুট → $1.2
উপরোক্ত ধাপগুলো পুরা করার পর ফলাফল নিচের ছবির মতো দেখাবে।
বরং, আপনি এই পদ্ধতিটি সেক্ষেত্রেও ব্যবহার করতে পারেন যদি আপনার ডেটা মাল্টিপল ওয়ার্কশিট-এ থাকে এবং আপনি লুকআপ ভ্যালু বেসড নির্দিষ্ট ওয়ার্কশিট থেকে সেটা ম্যাপ করতে চান।
Nth Occurrence পেতে VLOOKUP ফাংশন প্রয়োগ করা
ধরুন আপনার কাছে নিচের B4:D13 সেলে দেখানো Stationery Sales লিস্ট আছে। এখন আপনি জানতে চান একই কাস্টমারের ২য় বা ৩য় আইটেম কী কেনা হয়েছে। এই সমস্যা সমাধান করতে আমরা COUNTIF এবং VLOOKUP ফাংশন ব্যবহার করতে পারি। চলুন বিস্তারিত প্রক্রিয়া দেখি।
ধাপসমূহ:
- প্রথমে Column B-এ একটি কলাম ইনসার্ট করুন এবং এর হেডার নাম দিন Helper Column >> B5 সেলে নিচের ফর্মুলা লিখুন।
=C5&COUNTIF($C$5:C5, C5)
📄 নোট: দয়া করে নিশ্চিত করুন যে Helper Column ডেটাসেটের সবচেয়ে বামপাশে ইনসার্ট করা হয়েছে, কারণ ডিফল্টভাবে VLOOKUP ফাংশন বাম থেকে ডানে খোঁজ করে।
এখানে, C5 সেলটি Name John-কে রেফার করে এবং COUNTIF ফাংশনটি $C$5:C5 রেঞ্জ থেকে John-এর occurrence কাউন্ট করে। শেষে, Ampersand (&) অপারেটর টেক্সট এবং নাম্বার কম্বাইন করে।
- এরপর Name এবং Instance ইনপুট করুন, উদাহরণস্বরূপ এখানে এটি Julie, এবং 3 >> H6 সেলে যান এবং নিচের এক্সপ্রেশনটি টাইপ করুন।
=VLOOKUP(H4&H5, B5:E13, 3, FALSE)
এই ফর্মুলাতে, H4 এবং H5 সেলগুলো যথাক্রমে Name এবং Instance-কে ইন্ডিকেট করে।
উপরোক্ত ধাপগুলো পুরা করার পর আউটকাম নিচের স্ক্রিনশটের মতো দেখাবে।
VLOOKUP এবং IF ফাংশন ব্যবহার করে #N/A এরর হাইড করা
আপনি ISNA এবং VLOOKUP ফাংশনগুলোকে IF ফাংশন-এ ব্যবহার করে #N/A এরর হাইড করতে পারেন যখন একটি invalid lookup value দেওয়া হয়। এখন আমি নিচে প্রক্রিয়াটি দেখাচ্ছি।
ধাপসমূহ:
- প্রথমে G5 সেলে নেভিগেট করুন এবং নিচের এক্সপ্রেশনটি লিখুন।
=IF(ISNA(VLOOKUP(G4, B5:D14,3,FALSE)), "",VLOOKUP(G4, B5:D14,3,FALSE))
এই ফর্মুলাতে, G4 সেলটি ID 1008-কে রেফার করে এবং B5:D14 সেল রেঞ্জটি ID, নাম, এবং ডিপার্টমেন্ট কলামকে রিপ্রেজেন্ট করে।
ফর্মুলা ব্রেকডাউন:
- ISNA(VLOOKUP(G4, B5:D14,3,FALSE)) → চেক করে একটি মান #NA কিনা, এবং TRUE বা FALSE রিটার্ন করে। এখানে, G4 ( lookup_value আর্গুমেন্ট) B5:D14 (table_array আর্গুমেন্ট) অ্যারে থেকে ম্যাপ করা হয়েছে। এরপর, 3 (col_index_num আর্গুমেন্ট) লুকআপ ভ্যালুর কলাম নম্বরকে রিপ্রেজেন্ট করে। শেষে, FALSE (range_lookup আর্গুমেন্ট) লুকআপ ভ্যালুর Exact match-কে বোঝায়।
- আউটপুট → FALSE
- IF(ISNA(VLOOKUP(G4, B5:D14,3,FALSE)), “”,VLOOKUP(G4, B5:D14,3,FALSE)) → হয়ে যায়
- IF(FALSE, “”,VLOOKUP(G4, B5:D14,3,FALSE)) → চেক করে কন্ডিশন পূরণ হয়েছে কিনা এবং TRUE হলে একটা ভ্যালু এবং FALSE হলে অন্য ভ্যালু রিটার্ন করে। এখানে, FALSE হলো logical_test আর্গুমেন্ট যা IF ফাংশনকে VLOOKUP ফাংশন থেকে ভ্যালু (value_if_true আর্গুমেন্ট) রিটার্ন করতে বাধ্য করে অন্যথায় এটি ব্ল্যাঙ্ক “” (value_if_false আর্গুমেন্ট) রিটার্ন করে।
- আউটপুট → HR
অবশেষে, আপনি নিচের ছবিতে দেখানো রেজাল্ট পাবেন।
এছাড়া, যদি আমরা একটি অবৈধ ID নম্বর দিই (ID 1012), ফর্মুলাটি #N/A এরর দেখানোর বদলে ব্ল্যাঙ্ক ভ্যালু রিটার্ন করবে যা নিচের স্ক্রিনশটে দেখানো হয়েছে।
প্র্যাকটিস সেকশন
প্রতিটি শিটের ডান পাশে আমরা একটি Practice সেকশন দিয়ে দিয়েছি যাতে আপনি নিজে প্র্যাকটিস করতে পারেন। দয়া করে নিজে করে দেখুন।
উপসংহার
আশা করি VLOOKUP ব্যবহার করে এক্সেলে ডেটা ম্যাপ করা-র সকল পদ্ধতি এখন আপনাকে আপনার এক্সেল স্প্রেডশিটে আরও কার্যকরভাবে প্রয়োগ করতে উদ্বুদ্ধ করবে। আপনার কোনো প্রশ্ন বা ফিডব্যাক থাকলে কমেন্ট সেকশনে জানাতে পারেন। অথবা আপনি এই ওয়েবসাইটে এক্সেল ফাংশন-সম্পর্কিত অন্যান্য আর্টিকেল চেক আউট করতে পারেন।
