VLOOKUP ফাংশন মাইক্রোসফট এক্সেলের সবচেয়ে শক্তিশালী, ফ্লেক্সিবল, এবং অত্যন্ত উপকারী ফাংশনlerden একটি যা কোনো ভ্যালু সার্চ এবং রিট্রিভ করতে ব্যবহৃত হয় - এক্সাক্টলি ম্যাচড ভ্যালু বা কLOSEST ম্যাচড ভ্যালু - একটি সংশ্লিষ্ট ভ্যালু লুকআপ করে। কিন্তু কখনো কখনো শুধু একটি VLOOKUP ব্যবহার করলে কাজ হয় না। সেক্ষেত্রে আমরা একাধিক VLOOKUP প্রয়োগ করতে পারি। এই আর্টিকেলে আমরা দেখাব কীভাবে এক্সেলে Nested VLOOKUP ইমপ্লিমেন্ট করতে হয়।
ওয়ার্কবুক ডাউনলোড করুন
এখান থেকে ফ্রি প্র্যাকটিস এক্সেল ওয়ার্কবুক ডাউনলোড করতে পারেন।
এক্সেলে VLOOKUP
VLOOKUP বলতে বোঝায় 'Vertical Lookup'। এটি একটি ফাংশন যা এক্সেলকে একটি কলামে নির্দিষ্ট ভ্যালু সার্চ করতে বাধ্য করে, যাতে অপর কলাম থেকে একই সারির একটি ভ্যালু রিটার্ন করা যায়।
জেনেরিক ফর্মুলা:
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])এখানে,
| Arguments | Definition |
|---|---|
| lookup_value | যে ভ্যালুটি আপনি ম্যাচ করার চেষ্টা করছেন |
| table_array | যে ডেটা রেঞ্জে আপনি আপনার ভ্যালু সার্চ করতে চান |
| col_index_num | lookup_value-এর সংশ্লিষ্ট কলাম |
| range_lookup | এটি একটি বুলিয়ান ভ্যালু: TRUE বা FALSE। FALSE (অথবা 0) মানে এক্সাক্ট ম্যাচ এবং TRUE (অথবা 1) মানে অ্যপ্রক্সিমেট ম্যাচ। |
এক্সেলে Nested VLOOKUP ব্যবহার করার ৩টি ক্রাইটেরিয়া
এই সেকশনে আপনি জানব কীভাবে প্রোডাক্ট প্রাইস এবং সেলস ভ্যালু নেস্টেড VLOOKUP প্রয়োগ করে এক্সেলে বের করতে হয়। আমরা আপনাকে এছাড়া IFERROR Function-কে Nested VLOOKUP-এর সাথে কীভাবে কম্বাইন করতে হয় সেটিও জানাব।
১. প্রোডাক্ট প্রাইস বের করার জন্য নেস্টেড VLOOKUP ইমপ্লিমেন্টেশন
নিচের ডেটাসেটটি বিবেচনা করুন। Result Table-এ, আমরা প্রোডাক্ট ID-এর ভিত্তিতে Price পেতে চাই। কিন্তু those beiden একসাথে কোনো একক টেবিলে নেই। ID রয়েছে Table 1-এ এবং Price রয়েছে Table 2-এ। তাই আমরা এখানে কী করব: Table 1-এ ID সার্চ করব এবং ম্যাচড ভ্যালুর ভিত্তিতে Table 2- থেকে Price এক্সট্র্যাক্ট করব এবং Result Table-এর Price কলামে ফলাফল দেখাব।
Steps:
- আপনি যে সেলে ID-এর Price চান সেটে ক্লিক করুন (যেমন Result Table-এ ID A101-এর পাশের সেল, I5)
- এবং নিচের ফর্মুলা লিখুন,
=VLOOKUP(VLOOKUP(H5, $B$5:$C$9, 2, FALSE), $E$5:$F$9, 2, FALSE)এখানে,
- H5 = A101, Result Table-এ লুকআপ ভ্যালু হিসেবে সংরক্ষিত ID
- $B$5:$C$9 = Table 1-এ লুকআপ ভ্যালু সার্চ করার ডেটা রেঞ্জ
- $E$5:$F$9 = Table 2-এ লুকআপ ভ্যালু সার্চ করার ডেটা রেঞ্জ
- 2 = লুকআপ ভ্যালু সার্চ করার কলাম ইনডেক্স নম্বর
- FALSE = যেহেতু আমরা এক্সাক্ট ম্যাচ চাই, তাই আর্গুমেন্ট হিসেবে FALSE দিয়েছি।
- কিবোর্ডে Ctrl + Shift + Enter চাপুন।
আপনি রিজাল্ট সেল (I5)-এ ID A101-এর Price ($50) পাবেন।
- Fill Handle দিয়ে সারি নিচে ড্র্যাগ করুন যাতে ফর্মুলা বাকি সারিগুলোতে প্রয়োগ হয় এবং Result Table-এ সব প্রোডাক্ট ID-এর Price পাওয়া যায়।
উপরের ছবি দেখুন, আমরা Nested VLOOKUP ফর্মুলা রান করে Result Table-এ সব প্রোডাক্ট ID-এর Price পেয়েছি।
ফর্মুলা ব্রেকডাউন:
- VLOOKUP(H5, $B$5:$C$9, 2, 0)
- Output: “Football”
- Explanation: H5 = A101, ডেটা রেঞ্জ $B$5:$C$9-এ সার্চ করুন, যেটি কলাম ইনডেক্স 2 (B Column), FALSE সাহায্যে প্রোডাক্ট নাম “Football”-এর এক্সাক্ট ম্যাচ পান।
- VLOOKUP(VLOOKUP(H5, $B$5:$C$9,2,0), $E$5:$F$9, 2, 0) -> হয়ে যায়
- VLOOKUP(“Football”, $E$5:$F$9, 2, 0)
- Output: $50
- Explanation: “Football”, ডেটা রেঞ্জ $E$5:$F$9-এ সার্চ করুন, Price-এর এক্সাক্ট ম্যাচ (FALSE) পান, $50।
২. সেলস ভ্যালু পাওয়ার জন্য নেস্টেড VLOOKUP অ্যাপ্লিকেশন
নিচের ডেটাসেটটি বিবেচনা করুন। Result Table-এ, আমরা Product নামের ভিত্তিতে Sales ভ্যালু পেতে চাই। তাই আমরা এখানে কী করব: Table 1-এ Product নাম সার্চ করব এবং ম্যাচড ভ্যালুর ভিত্তিতে Table 2- থেকে Sales ভ্যালু এক্সট্র্যাক্ট করব এবং Result Table-এর Sales কলামে ফলাফল দেখাব।
Steps:
- আপনি যে সেলে Product-এর Sales ভ্যালু চান সেটে ক্লিক করুন (যেমন Result Table-এ Product Football-এর পাশের সেল, J5)
- এবং নিচের ফর্মুলা লিখুন,
=VLOOKUP(VLOOKUP(I5,$B$5:$C$9,2,0),$E$5:$G$9,2,0)এখানে,
- I5 = Football, Result Table-এ লুকআপ ভ্যালু হিসেবে সংরক্ষিত Product নাম
- $B$5:$C$9 = Table 1-এ লুকআপ ভ্যালু সার্চ করার ডেটা রেঞ্জ
- $E$5:$G$9 = Table 2-এ লুকআপ ভ্যালু সার্চ করার ডেটা রেঞ্জ
- 2 = লুকআপ ভ্যালু সার্চ করার কলাম ইনডেক্স নম্বর
- 0 = যেহেতু আমরা এক্সাক্ট ম্যাচ চাই, তাই আর্গুমেন্ট হিসেবে 0 বা FALSE দিয়েছি।
- কিবোর্ডে Ctrl + Shift + Enter চাপুন।
আপনি রিজাল্ট সেল (J5)-এ Football-এর Sales ভ্যালু ($1,000) পাবেন।
- Fill Handle দিয়ে সারি নিচে ড্র্যাগ করুন যাতে ফর্মুলা বাকি সারিগুলোতে প্রয়োগ হয় এবং Result Table-এ সব Product-এর Sales ভ্যালু পাওয়া যায়।
উপরের ছবি দেখুন, আমরা Nested VLOOKUP ফর্মুলা রান করে Result Table-এ সব Product-এর Sales ভ্যালু পেয়েছি।
ফর্মুলা ব্রেকডাউন:
- VLOOKUP(I5, $B$5:$C$9, 2, 0)
- Output: “David”
- Explanation: I5 = Football, ডেটা রেঞ্জ $B$5:$C$9-এ সার্চ করুন, যেটি কলাম ইনডেক্স 2 (B Column), 0 সাহায্যে সেলসম্যান নাম “David”-এর এক্সাক্ট ম্যাচ পান।
- VLOOKUP(VLOOKUP(I5, $B$5:$C$9, 2, 0), $E$5:$G$9, 2, 0) -> হয়ে যায়
- VLOOKUP(“David”, $E$5:$G$9, 2, 0)
- Output: $1,000
- Explanation: “David”, ডেটা রেঞ্জ $E$5:$G$9-এ সার্চ করুন, Sales ভ্যালু $1,000-এর এক্সাক্ট ম্যাচ (0) পান।
৩. নেস্টেড VLOOKUP এবং IFERROR ফাংশনের কম্বিনেশন
এই সেকশনে, আমরা দেখব কীভাবে Nested VLOOKUP এবং IFERROR ফাংশন কম্বাইন করে এক সিংগেল লুকআপ ভ্যালুর ভিত্তিতে একাধিক টেবিল থেকে নির্দিষ্ট ফলাফল এক্সট্র্যাক্ট করতে হয়।
নিচের ডেটাসেট দেখুন যেখানে Productগুলো ৩টি আলাদা টেবিলে বিতরণ করা আছে। আমরা ID (A106) ভিত্তিতে একটি নির্দিষ্ট Product সেই ৩টি টেবিলের মাঝ থেকে সার্চ করব এবং ফলাফল Result Table-এর Product কলামে প্রদর্শন করব।
Steps:
- আপনি যে সেলে Product নাম চান সেটে ক্লিক করুন (যেমন Result Table-এ ID A106-এর পাশের সেল, L5)
- এবং নিচের ফর্মুলা লিখুন,
=IFERROR(VLOOKUP(K5,$B$5:$C$7,2,0),IFERROR(VLOOKUP(K5,$E$5:$F$7,2,0),VLOOKUP(K5,$H$5:$I$7,2,0)))এখানে,
- K5 = A106, Result Table-এ লুকআপ ভ্যালু হিসেবে সংরক্ষিত ID
- $B$5:$C$7 = Table 1-এ লুকআপ ভ্যালু সার্চ করার ডেটা রেঞ্জ
- $E$5:$F$7 = Table 2-এ লুকআপ ভ্যালু সার্চ করার ডেটা রেঞ্জ
- $H$5:$I$7 = Table 3-এ লুকআপ ভ্যালু সার্চ করার ডেটা রেঞ্জ
- 2 = লুকআপ ভ্যালু সার্চ করার কলাম ইনডেক্স নম্বর
- 0 = যেহেতু আমরা এক্সাক্ট ম্যাচ চাই, তাই আর্গুমেন্ট হিসেবে 0 বা FALSE দিয়েছি।
- কিবোর্ডে Ctrl + Shift + Enter চাপুন।
আপনি রিজাল্ট সেল (L5)-এ লুকআপ ID A106-এর Product নাম (Cricket Bat) পাবেন।
ফর্মুলা ব্রেকডাউন:
- VLOOKUP(K5, $H$5:$I$7, 2, 0)
- Output: #N/A
- Explanation: K5 = A106, ডেটা রেঞ্জ $H$5:$I$7-এ সার্চ করুন প্রোডাক্ট ID A106-এর এক্সাক্ট ম্যাচ (2) পाने। যেহেতু রেঞ্জ $H$5:$I$7 (Table 3)-এ ID A106-এর কোনো ম্যাচ নেই, তাই এটি #N/A এরর রিটার্ন করে।
- VLOOKUP(K5, $E$5:$F$7, 2, 0)
- Output: “Cricket Bat”
- Explanation: K5 = A106, ডেটা রেঞ্জ $E$5:$F$7-এ সার্চ করুন 2 (FALSE) সাহায্যে প্রোডাক্ট ID A106-এর এক্সাক্ট ম্যাচ পান। সেটি আমরা ওই রেঞ্জে (Table 2) পেয়েছি, তাই এটি “Cricket Bat” রিটার্ন করে।
- IFERROR(VLOOKUP(K5, $E$5:$F$7, 2, 0),VLOOKUP(K5, $H$5:$I$7, 2, 0) -> হয়ে যায়
- IFERROR(“Cricket Bat”, #N/A)
- Output: “Cricket Bat”
- Explanation: IFERROR ফাংশন ফর্মুলা চেক করে এবং যদি রেজাল্ট হিসেবে এরর পায়, তবে ইউজার দ্বারা নির্দিষ্ট অন্য একটি ভ্যালু রিটার্ন করে।
- VLOOKUP(K5, $B$5:$C$7, 2, 0)
- Output: #N/A
- Explanation: K5 = A106, ডেটা রেঞ্জ $B$5:$C$7-এ সার্চ করুন প্রোডাক্ট ID A106-এর এক্সাক্ট ম্যাচ (2) পান। যেহেতু রেঞ্জ $B$5:$C$7 (Table 1)-এ ID A106-এর কোনো ম্যাচ নেই, তাই এটি #N/A এরর রিটার্ন করে।
- IFERROR(VLOOKUP(K5,B5:C7,2,0),IFERROR(VLOOKUP(K5,E5:F7,2,0),VLOOKUP(K5,H5:I7,2,0))) -> হয়ে যায়
- IFERROR(#N/A, “Cricket Bat”)
- Output: “Cricket Bat”
- Explanation: IFERROR ফাংশন ফর্মুলা চেক করে এবং যদি রেজাল্ট হিসেবে এরর পায়, তবে ইউজার দ্বারা নির্দিষ্ট অন্য একটি ভ্যালু রিটার্ন করে।
মনে রাখবেন
- যেহেতু ভ্যালু সার্চ করার ডেটা টেবিল অ্যারে রেঞ্জ ফিক্সড, আর্ে রেফারেন্স নম্বরের সামনে ডলার ($) চিহ্ন দেয়া না ভুলবেন।
- অ্যারে ভ্যালু নিয়ে কাজ করার সময়, রিজাল্ট এক্সট্র্যাক্ট করার সময় কিবোর্ডে Ctrl + Shift + Enter চাপা না ভুলবেন। শুধু Enter চাপলে তখন কাজ করবে যখন আপনি Microsoft 365 ব্যবহার করছেন।
উপসংহার
এই আর্টিকেলে বিস্তারিতভাবে ব্যাখ্যা করা হয়েছে কীভাবে এক্সেলে ৩টি আলাদা ক্রাইটেরিয়ায় Nested VLOOKUP ব্যবহার করতে হয়। আশা করি এই আর্টিকেল আপনার জন্য খুব উপকারী হবে। বিষয়টি নিয়ে আপনার যেকোনো প্রশ্ন থাকলে বेझিঝক জিজ্ঞাসা করুন।
সম্পর্কিত আর্টিকেল
- এক্সেলে VLOOKUP এবং SUM ফাংশন একসাথে ব্যবহার করার ৬ পদ্ধতি
- এক্সেল VLOOKUP দিয়ে কLOSEST ম্যাচ খুঁজে বের করা (৫ উদাহরণ সহ)
- এক্সেলে VLOOKUP কেস সেনসিটিভ 만드는 ৪ পদ্ধতি
- এক্সেলে মাল্টিপল ক্রাইটেরিয়া সহ VLOOKUP ব্যবহার করার ৬ পদ্ধতি + বিকল্পসমূহ
- এক্সেল VBA-তে VLOOKUP ফাংশন ব্যবহার করার ৪ উদাহরণ
