Lookup এবং Reference Functions গাইড ও নোট

Lookup এবং Reference Functions এক্সেলে ডেটা অনুসন্ধান ও রেফারেন্সের জন্য ব্যবহৃত হয়। এই ফাংশনগুলো আপনাকে নির্দিষ্ট মানের জন্য ডেটার মধ্যে অনুসন্ধান করতে এবং সম্পর্কিত তথ্য খুঁজে বের করতে সহায়তা করে। এগুলি বিশেষভাবে বড় ডেটাসেটের মধ্যে দ্রুত এবং কার্যকরীভাবে তথ্য বের করার জন্য অত্যন্ত কার্যকরী। এক্সেলে বিভিন্ন ধরনের Lookup এবং Reference Functions রয়েছে, যা ডেটা খোঁজা এবং তার অবস্থান নির্ধারণে সহায়তা করে।

VLOOKUP (Vertical Lookup)

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

• সিনট্যাক্স:
  =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
  • lookup_value: যে মানটি আপনি খুঁজছেন।
  • table_array: ডেটার পরিসীমা যেখানে আপনি লুকআপ করবেন।
  • col_index_num: যেই কলাম থেকে তথ্য ফেরত পেতে চান।
  • [range_lookup]: সঠিক ম্যাচের জন্য FALSE এবং আনুমানিক ম্যাচের জন্য TRUE।
• উদাহরণ:
  =VLOOKUP("ProductA", A1:B10, 2, FALSE)
  এই ফাংশনটি "ProductA" নামের পণ্যের সাথে সম্পর্কিত দ্বিতীয় কলামের মান ফিরিয়ে দেবে।

HLOOKUP (Horizontal Lookup)

HLOOKUP ফাংশনটি VLOOKUP এর মতো, তবে এটি রো-বেসড ডেটাতে কাজ করে। HLOOKUP ব্যবহার করে আপনি এক্সেলে হরাইজন্টালি (রো অনুযায়ী) ডেটা খুঁজে বের করতে পারেন।

• সিনট্যাক্স:
  =HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])
  • lookup_value: যে মানটি আপনি খুঁজছেন।
  • table_array: ডেটার পরিসীমা যেখানে আপনি লুকআপ করবেন।
  • row_index_num: যেই রো থেকে তথ্য ফিরিয়ে আনবেন।
  • [range_lookup]: সঠিক বা আনুমানিক ম্যাচের জন্য TRUE বা FALSE।
• উদাহরণ:
  =HLOOKUP("Jan", A1:D2, 2, FALSE)
  এই ফাংশনটি "Jan" এর সাথে সম্পর্কিত দ্বিতীয় রো থেকে মান ফেরত দেবে।

INDEX

INDEX ফাংশনটি নির্দিষ্ট সেল বা রেঞ্জ থেকে মান ফেরত দেয়। এটি একটি শক্তিশালী রেফারেন্স ফাংশন, যা লুকআপ কাজের জন্য ব্যবহৃত হতে পারে। এটি VLOOKUP বা HLOOKUP এর তুলনায় আরও বেশি নমনীয়, কারণ এটি ডেটার যেকোনো জায়গা থেকে মান ফেরত দিতে পারে।

• সিনট্যাক্স:
  =INDEX(array, row_num, [column_num])
  • array: যে ডেটার মধ্যে থেকে মান বের করতে চান।
  • row_num: যে রো থেকে মান বের করবেন।
  • [column_num]: যে কলাম থেকে মান বের করবেন (যদি প্রয়োজন হয়)।
• উদাহরণ:
  =INDEX(A1:B10, 3, 2)
  এই ফাংশনটি A1:B10 রেঞ্জের ৩য় রো এবং ২য় কলামের মান ফেরত দেবে।

MATCH

MATCH ফাংশনটি নির্দিষ্ট মানের অবস্থান (পজিশন) খুঁজে বের করে। এটি সাধারণত INDEX ফাংশনের সাথে ব্যবহৃত হয়, যেখানে MATCH ফাংশনটি মানের অবস্থান নির্ধারণ করে এবং INDEX সেই অবস্থানে মান ফেরত দেয়।

• সিনট্যাক্স:
  =MATCH(lookup_value, lookup_array, [match_type])
  • lookup_value: যে মানটির অবস্থান খুঁজছেন।
  • lookup_array: যে রেঞ্জে মানটি খুঁজছেন।
  • [match_type]: 1 (ছোট থেকে বড়), 0 (ঠিক মিলে), -1 (বড় থেকে ছোট)।
• উদাহরণ:
  =MATCH("ProductB", A1:A10, 0)
  এই ফাংশনটি "ProductB" এর অবস্থান খুঁজে বের করবে A1:A10 রেঞ্জের মধ্যে।

OFFSET

OFFSET ফাংশনটি একটি নির্দিষ্ট রেঞ্জ থেকে ডাইনামিকভাবে রেফারেন্স তৈরি করতে ব্যবহৃত হয়। এটি ডেটার মধ্যে কোথায় গিয়ে তথ্য সংগ্রহ করতে হবে তা নির্ধারণ করে। বিশেষত ডাইনামিক রেঞ্জ এবং চার্ট তৈরির ক্ষেত্রে এটি ব্যবহার করা হয়।

• সিনট্যাক্স:
  =OFFSET(reference, rows, cols, [height], [width])
  • reference: শুরু পজিশন যেখানে থেকে অঙ্কন শুরু হবে।
  • rows: কতগুলো রো নিচে বা উপরে যেতে হবে।
  • cols: কতগুলো কলাম ডান বা বামে যেতে হবে।
  • [height]: রেঞ্জের উচ্চতা (ঐচ্ছিক)।
  • [width]: রেঞ্জের প্রস্থ (ঐচ্ছিক)।
• উদাহরণ:
  =OFFSET(A1, 2, 1)
  এই ফাংশনটি A1 সেলের থেকে ২ রো নিচে এবং ১ কলাম ডানে থাকা সেলটি রেফারেন্স করবে।

INDIRECT

INDIRECT ফাংশনটি একটি টেক্সট রেফারেন্স হিসেবে ব্যবহৃত হয়, যা একটি সেলের রেফারেন্স তৈরি করতে সহায়তা করে। এটি ডাইনামিকভাবে সেল রেফারেন্স তৈরি করার জন্য ব্যবহৃত হয়, বিশেষ করে যখন আপনি রেফারেন্সের জন্য ভ্যারিয়েবল মান ব্যবহার করতে চান।

• সিনট্যাক্স:
  =INDIRECT(ref_text, [a1])
  • ref_text: সেলের রেফারেন্স যা টেক্সট হিসেবে প্রদান করা হয়।
  • [a1]: রেফারেন্স স্টাইল (A1 বা R1C1)।
• উদাহরণ:
  =INDIRECT("B2")
  এই ফাংশনটি B2 সেলের মানকে ফিরিয়ে দেবে।

Excel Lookup এবং Reference Functions এর গুরুত্ব

• ডেটা অনুসন্ধান: আপনি দ্রুত এবং কার্যকরভাবে ডেটার মধ্যে নির্দিষ্ট মান খুঁজে বের করতে পারেন।
• ডাইনামিক রেঞ্জ: এই ফাংশনগুলোর মাধ্যমে আপনি ডাইনামিক রেঞ্জ তৈরি করতে পারেন, যা ডেটার উপর ভিত্তি করে পরিবর্তিত হয়।
• স্মার্ট ডেটা ম্যানেজমেন্ট: ডেটার মধ্যে কোনো পরিবর্তন হলে, এই ফাংশনগুলো আপনার ডেটাকে স্বয়ংক্রিয়ভাবে আপডেট করে।

সারাংশ

Excel Lookup এবং Reference Functions এক্সেলে ডেটা অনুসন্ধান, রেফারেন্স তৈরি এবং বিভিন্ন ধরনের ডেটা ম্যানিপুলেশন করার জন্য অত্যন্ত গুরুত্বপূর্ণ টুলস। এগুলি ডেটা বিশ্লেষণ এবং প্রতিবেদন তৈরির ক্ষেত্রে দ্রুত, সহজ এবং কার্যকরী সমাধান প্রদান করে।

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

VLOOKUP (Vertical Lookup)

VLOOKUP ফাংশনটি Vertical Lookup এর জন্য ব্যবহৃত হয়, যেখানে আপনি একটি কলামে উপরের থেকে নিচে গিয়ে একটি নির্দিষ্ট মানের জন্য অনুসন্ধান করতে পারেন। এটি সাধারণত একটি ডেটা টেবিল বা রেঞ্জের প্রথম কলামে মান খুঁজে বের করে এবং ওই মানের সাথে সম্পর্কিত অন্য কলামের তথ্য প্রদান করে।

VLOOKUP ফাংশনের Syntax:

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

• lookup_value: যে মানটি আপনি খুঁজতে চান (এটি সেল রেফারেন্স হতে পারে বা কোনো নির্দিষ্ট মান হতে পারে)।
• table_array: যেখানে আপনি মানটি খুঁজতে চান (ডেটার রেঞ্জ বা টেবিল)।
• col_index_num: যেখানে আপনি খুঁজে পাওয়া মানের সাথে সম্পর্কিত তথ্য পাবেন (এই সংখ্যাটি কলামের অবস্থান নির্দেশ করে)।
• [range_lookup]: ঐচ্ছিক প্যারামিটার, যেখানে যদি আপনি TRUE দেন, তাহলে এটি নিকটতম ম্যাচ খুঁজে বের করবে (ডিফল্ট মান), এবং যদি FALSE দেন, তাহলে এটি একদম সঠিক ম্যাচ খুঁজে বের করবে।

VLOOKUP এর উদাহরণ:

ধরা যাক, আপনার একটি টেবিল রয়েছে যেখানে প্রথম কলামে পণ্যের কোড এবং দ্বিতীয় কলামে পণ্যের নাম রয়েছে। আপনি একটি কোডের জন্য পণ্যের নাম খুঁজতে চান।

এখন, আপনি যদি P002 কোডের জন্য পণ্যের নাম খুঁজতে চান, তাহলে আপনি নিম্নলিখিত ফাংশন ব্যবহার করবেন:

=VLOOKUP("P002", A2:B4, 2, FALSE)

এই ফাংশনটি P002 কোডের জন্য দ্বিতীয় কলামের মান (Smartphone) প্রদান করবে, কারণ P002 এর সাথে সম্পর্কিত নাম Smartphone।

HLOOKUP (Horizontal Lookup)

HLOOKUP ফাংশনটি Horizontal Lookup এর জন্য ব্যবহৃত হয়। এটি মূলত একটি সারিতে মান অনুসন্ধান করে এবং একই কলামে সংশ্লিষ্ট তথ্য ফেরত দেয়। যখন আপনার ডেটা টেবিলটি horizontal বা সারিতে থাকে, তখন HLOOKUP ব্যবহার করা হয়।

HLOOKUP ফাংশনের Syntax:

=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])

• lookup_value: যে মানটি আপনি খুঁজতে চান (এটি সেল রেফারেন্স হতে পারে বা কোনো নির্দিষ্ট মান হতে পারে)।
• table_array: যেখানে আপনি মানটি খুঁজতে চান (ডেটার রেঞ্জ বা টেবিল)।
• row_index_num: যেখানে আপনি খুঁজে পাওয়া মানের সাথে সম্পর্কিত তথ্য পাবেন (এই সংখ্যাটি সারির অবস্থান নির্দেশ করে)।
• [range_lookup]: ঐচ্ছিক প্যারামিটার, যেখানে যদি আপনি TRUE দেন, তাহলে এটি নিকটতম ম্যাচ খুঁজে বের করবে (ডিফল্ট মান), এবং যদি FALSE দেন, তাহলে এটি একদম সঠিক ম্যাচ খুঁজে বের করবে।

HLOOKUP এর উদাহরণ:

ধরা যাক, আপনার একটি টেবিল রয়েছে যেখানে প্রথম সারিতে পণ্যের কোড এবং দ্বিতীয় সারিতে পণ্যের নাম রয়েছে। আপনি একটি কোডের জন্য পণ্যের নাম খুঁজতে চান।

এখন, আপনি যদি P002 কোডের জন্য পণ্যের নাম খুঁজতে চান, তাহলে আপনি নিম্নলিখিত ফাংশন ব্যবহার করবেন:

=HLOOKUP("P002", A1:D2, 2, FALSE)

এই ফাংশনটি P002 কোডের জন্য দ্বিতীয় সারির মান (Smartphone) প্রদান করবে, কারণ P002 এর সাথে সম্পর্কিত নাম Smartphone।

VLOOKUP এবং HLOOKUP এর মধ্যে পার্থক্য

VLOOKUP এবং HLOOKUP এর গুরুত্বপূর্ণ টিপস

1. VLOOKUP এর ক্ষেত্রে:
   • col_index_num প্যারামিটারটি ১ বা তার চেয়ে বড় হতে হবে। যদি আপনি প্রথম কলামের মান চান, তবে ১ দিতে হবে।
   • range_lookup এর মান FALSE দিলে সঠিক ম্যাচ ফেরত পাবে, এবং TRUE দিলে নিকটতম ম্যাচ পেতে পারেন।
2. HLOOKUP এর ক্ষেত্রে:
   • row_index_num প্যারামিটারটি ১ বা তার চেয়ে বড় হতে হবে। যদি আপনি প্রথম সারির মান চান, তবে ১ দিতে হবে।
   • range_lookup এর মান FALSE দিলে সঠিক ম্যাচ ফেরত পাবে, এবং TRUE দিলে নিকটতম ম্যাচ পেতে পারেন।

সারাংশ

VLOOKUP এবং HLOOKUP ফাংশন এক্সেল ব্যবহারকারীদের জন্য ডেটার মধ্যে সম্পর্কিত তথ্য দ্রুত খুঁজে বের করার জন্য অত্যন্ত গুরুত্বপূর্ণ টুল। VLOOKUP উল্লম্ব রেঞ্জে অনুসন্ধান করতে ব্যবহৃত হয়, যেখানে HLOOKUP অনুভূমিক রেঞ্জে অনুসন্ধান করতে ব্যবহৃত হয়। দুটি ফাংশনই সঠিকভাবে ব্যবহার করলে ডেটা ম্যানিপুলেশন এবং বিশ্লেষণ আরো দ্রুত এবং সহজ হয়ে ওঠে।

এক্সেল ব্যবহারকারীদের জন্য VLOOKUP এবং HLOOKUP হলো পরিচিত লুকআপ ফাংশন, কিন্তু এগুলোর কিছু সীমাবদ্ধতা রয়েছে। INDEX এবং MATCH ফাংশনগুলি দিয়ে Advanced Lookup করা অনেক বেশি কার্যকর এবং লুকআপের ক্ষেত্রে বেশি নমনীয়তা প্রদান করে। এই দুটি ফাংশন ব্যবহার করে আপনি আরো জটিল এবং শক্তিশালী লুকআপ অপারেশন সম্পাদন করতে পারেন।

INDEX ফাংশন

INDEX ফাংশনটি একটি নির্দিষ্ট রেঞ্জ থেকে একটি নির্দিষ্ট সেল বা মান ফেরত দেয়। এই ফাংশনটি দুটি প্রধান প্যারামিটার ব্যবহার করে:

1. Array: রেঞ্জ বা টেবিল যেখানে আপনি মান খুঁজে বের করতে চান।
2. Row Number: ঐ রেঞ্জে কোন সারি থেকে মান বের করবেন তা নির্দিষ্ট করে।
3. Column Number (যদি প্রয়োজন হয়): ঐ রেঞ্জের কোন কলাম থেকে মান ফেরত দেবেন।

INDEX ফাংশনের গঠন:

=INDEX(array, row_num, [column_num])

উদাহরণ:

=INDEX(A1:C10, 3, 2)

এটি A1:C10 রেঞ্জের তৃতীয় সারির দ্বিতীয় কলামের মান ফেরত দেবে।

MATCH ফাংশন

MATCH ফাংশনটি একটি নির্দিষ্ট মান খুঁজে বের করতে ব্যবহৃত হয় এবং এটি ঐ মানের অবস্থান (রো বা কলাম নম্বর) ফেরত দেয়। এটি একটি নির্দিষ্ট রেঞ্জের মধ্যে খোঁজা হয়।

MATCH ফাংশনের গঠন:

=MATCH(lookup_value, lookup_array, [match_type])

• lookup_value: যে মানটি আপনি খুঁজে বের করতে চান।
• lookup_array: যেখানে মানটি খুঁজতে চান।
• match_type: এই প্যারামিটারটি ১, ০ বা -১ হতে পারে, যা অনুসন্ধানের ধরণ নির্ধারণ করে।
  • ১: নিকটতম বড় মান।
  • ০: সঠিক মান।
  • -১: নিকটতম ছোট মান।

উদাহরণ:

=MATCH("Apple", A1:A10, 0)

এটি "Apple" মানটি A1:A10 রেঞ্জে খুঁজবে এবং ঐ মানটির অবস্থান ফেরত দেবে।

INDEX এবং MATCH দিয়ে Advanced Lookup

INDEX এবং MATCH ফাংশন একত্রিত করে অনেক বেশি নমনীয় এবং শক্তিশালী লুকআপ তৈরি করা যায়। VLOOKUP এর সীমাবদ্ধতা যেমন লুকআপ কলামের বাম পাশে থাকা তথ্য পাওয়া যায় না, তা INDEX এবং MATCH দিয়ে সহজেই সমাধান করা সম্ভব।

INDEX এবং MATCH এর মাধ্যমে Vertical Lookup (VLOOKUP এর বিকল্প)

ধরা যাক, আপনার কাছে একটি টেবিল রয়েছে এবং আপনি একটি নির্দিষ্ট প্রোডাক্টের মূল্য খুঁজে বের করতে চান, যেখানে প্রোডাক্টের নাম এবং মূল্য দুইটি আলাদা কলামে আছে।

টেবিল উদাহরণ:

এখন, যদি আপনি "Banana" এর দাম খুঁজে বের করতে চান, তাহলে INDEX এবং MATCH ফাংশন দিয়ে আপনি নিম্নরূপ লুকআপ করতে পারেন:

=INDEX(B1:B3, MATCH("Banana", A1:A3, 0))

ব্যাখ্যা:

1. MATCH("Banana", A1:A3, 0): "Banana" খুঁজে পাবে এবং A1:A3 রেঞ্জের মধ্যে তার অবস্থান (2) ফেরত দেবে।
2. INDEX(B1:B3, 2): এরপর INDEX ফাংশনটি B1:B3 রেঞ্জে ২ নম্বর সারির মান (৫০) ফেরত দেবে, অর্থাৎ "Banana" এর মূল্য।

INDEX এবং MATCH দিয়ে Horizontal Lookup (HLOOKUP এর বিকল্প)

এখন যদি টেবিলটি অনুভূমিক (horizontal) আকারে থাকে এবং আপনি কিছু ডেটা লুকআপ করতে চান, তবে INDEX এবং MATCH ফাংশন ব্যবহার করা যাবে। উদাহরণস্বরূপ:

টেবিল উদাহরণ:

আপনি যদি "Profit" এর জন্য "Feb" মাসের মান খুঁজে বের করতে চান, তাহলে এই ফর্মুলাটি ব্যবহার করতে পারেন:

=INDEX(B1:D2, 2, MATCH("Feb", B1:D1, 0))

ব্যাখ্যা:

1. MATCH("Feb", B1:D1, 0): "Feb" খুঁজে পাবে এবং B1:D1 রেঞ্জের মধ্যে তার অবস্থান (২) ফেরত দেবে।
2. INDEX(B1:D2, 2, 2): এরপর INDEX ফাংশনটি ২ নম্বর সারি এবং ২ নম্বর কলামের মান (৩০) ফেরত দেবে, অর্থাৎ "Profit" এর জন্য "Feb" মাসের মান।

INDEX এবং MATCH দিয়ে 2-Dimensional Lookup

INDEX এবং MATCH ফাংশনগুলির শক্তি তখন সবচেয়ে বেশি কাজে আসে যখন আপনাকে একাধিক শর্ত বা দুটি মাত্রায় লুকআপ করতে হয়, যা VLOOKUP বা HLOOKUP দ্বারা সম্ভব নয়।

ধরা যাক, আপনার কাছে একটি বড় টেবিল রয়েছে যেখানে বিভিন্ন প্রোডাক্ট এবং মাসের বিক্রয়ের তথ্য রয়েছে, এবং আপনি একটি নির্দিষ্ট প্রোডাক্ট ও মাসের জন্য বিক্রয়ের মান খুঁজে বের করতে চান।

টেবিল উদাহরণ:

আপনি যদি "Banana" এর জন্য "Feb" মাসের বিক্রয় খুঁজে বের করতে চান, তাহলে এই ফর্মুলাটি ব্যবহার করতে পারেন:

=INDEX(B2:D4, MATCH("Banana", A2:A4, 0), MATCH("Feb", B1:D1, 0))

ব্যাখ্যা:

1. MATCH("Banana", A2:A4, 0): "Banana" খুঁজে পাবে এবং A2:A4 রেঞ্জের মধ্যে তার অবস্থান (২) ফেরত দেবে।
2. MATCH("Feb", B1:D1, 0): "Feb" খুঁজে পাবে এবং B1:D1 রেঞ্জের মধ্যে তার অবস্থান (২) ফেরত দেবে।
3. INDEX(B2:D4, 2, 2): এরপর INDEX ফাংশনটি B2:D4 রেঞ্জের ২ নম্বর সারি এবং ২ নম্বর কলামের মান (৭৫) ফেরত দেবে, অর্থাৎ "Banana" এর জন্য "Feb" মাসের বিক্রয়।

সারাংশ

INDEX এবং MATCH ফাংশনগুলি একসঙ্গে ব্যবহার করে আপনি খুব শক্তিশালী এবং নমনীয় লুকআপ অপারেশন তৈরি করতে পারেন, যা VLOOKUP বা HLOOKUP এর চেয়ে আরও বেশি নমনীয় ও কার্যকরী। এগুলি বিভিন্ন ধরণের লুকআপ পরিস্থিতি সমাধান করতে সক্ষম, যেমন:

• Vertical Lookup
• Horizontal Lookup
• 2-Dimensional Lookup

এটি বিশেষ করে তখন কার্যকরী যখন আপনার ডেটা টেবিলের মধ্যে আরও জটিল বা ডায়নামিক লুকআপ প্রয়োজন হয়।

XLOOKUP ফাংশন এক্সেলের একটি আধুনিক এবং শক্তিশালী ফাংশন, যা VLOOKUP এবং HLOOKUP এর তুলনায় অনেক বেশি নমনীয় এবং সহজে ব্যবহারযোগ্য। এই ফাংশনটি মূলত লুকআপ (lookup) এবং রেফারেন্স (reference) সংক্রান্ত কাজ সহজ এবং দ্রুত করে তোলে। XLOOKUP এর মাধ্যমে আপনি খুব সহজে ডেটা অনুসন্ধান (lookup) করতে পারবেন, এবং এটি একাধিক সীমাবদ্ধতা দূর করে।

XLOOKUP কী?

XLOOKUP ফাংশনটি এক্সেল 365 এবং এক্সেল 2021 এ উপলব্ধ একটি নতুন ফাংশন। এটি VLOOKUP, HLOOKUP, INDEX-MATCH এর জায়গায় ব্যবহার করা যায় এবং লুকআপের কাজ আরও সহজ ও কার্যকরী করে।

এটি আপনাকে একটি রেঞ্জে নির্দিষ্ট মান অনুসন্ধান (search) করে, এবং সেই মানের সাথে সম্পর্কিত মান অন্য একটি রেঞ্জ থেকে ফিরিয়ে দেয়। এর মাধ্যমে আপনি উল্লম্ব (vertical) বা অনুভূমিক (horizontal) অনুসন্ধান করতে পারেন, এবং এটি অনেক বেশি নমনীয়তা প্রদান করে।

XLOOKUP এর গঠন

XLOOKUP ফাংশনের গঠন নিম্নরূপ:

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

ফাংশনের প্যারামিটার:

1. lookup_value: যে মানটি আপনি খুঁজতে চান। এটি হতে পারে সেল রেফারেন্স বা কোনো নির্দিষ্ট মান।
2. lookup_array: এটি হলো সেই রেঞ্জ বা অ্যারে যেখানে আপনি আপনার lookup_value খুঁজে পেতে চান।
3. return_array: এটি হলো সেই রেঞ্জ বা অ্যারে, যা থেকে আপনি ফলাফল পাবেন।
4. [if_not_found] (ঐচ্ছিক): যদি আপনার দেয়া lookup_value পাওয়া না যায়, তবে আপনি এখানে একটি কাস্টম মেসেজ বা ফলাফল নির্ধারণ করতে পারেন।
5. [match_mode] (ঐচ্ছিক): এখানে আপনি নির্ধারণ করতে পারেন, কীভাবে মানের সাথে মেলাতে হবে:
   • 0: একটি সঠিক ম্যাচ (exact match) খোঁজে।
   • 1: সর্বাধিক কাছাকাছি বড় মান (less than or equal to)।
   • -1: সর্বাধিক কাছাকাছি ছোট মান (greater than or equal to)।
6. [search_mode] (ঐচ্ছিক): এটি নির্ধারণ করে যে, আপনি কোথা থেকে অনুসন্ধান শুরু করবেন।
   • 1: অনুসন্ধান শুরু করবে উপরের থেকে নিচের দিকে (default)।
   • -1: অনুসন্ধান শুরু করবে নিচের থেকে উপরের দিকে।

XLOOKUP এর ব্যবহার

XLOOKUP ফাংশনটি ব্যবহার করার মাধ্যমে আপনি সহজে ডেটা অনুসন্ধান করতে পারবেন। নিচে এর কিছু ব্যবহারিক উদাহরণ দেওয়া হলো:

উদাহরণ ১: সোজা লুকআপ

ধরা যাক, আপনার কাছে একটি ছাত্রদের নাম এবং তাদের প্রাপ্ত নম্বরের একটি তালিকা রয়েছে। আপনি যদি একটি নির্দিষ্ট ছাত্রের নম্বর খুঁজে পেতে চান, তাহলে XLOOKUP ব্যবহার করতে পারেন।

এখন, যদি আপনি মিথিলার নম্বর খুঁজতে চান:

=XLOOKUP("মিথিলা", A2:A5, B2:B5)

এটি "92" ফেরত দেবে, কারণ মিথিলার নম্বর 92।

উদাহরণ ২: যদি মান না পাওয়া যায়

ধরা যাক, আপনি যে ছাত্রের নামটি খুঁজছেন, সে তালিকায় নেই। আপনি তখন একটি কাস্টম মেসেজ বা মান ফেরত দিতে পারেন।

=XLOOKUP("আলী", A2:A5, B2:B5, "মাঝে নেই")

এটি "মাঝে নেই" ফেরত দেবে, কারণ আলী ছাত্রের নাম তালিকায় নেই।

উদাহরণ ৩: কাছাকাছি মান খুঁজে পাওয়া

আপনি যদি একটি নির্দিষ্ট মানের সাথে সবচেয়ে কাছাকাছি মান খুঁজে পেতে চান, তবে match_mode প্যারামিটার ব্যবহার করতে পারেন। নিচের উদাহরণে, আপনি ৮০ এর কাছাকাছি সর্বোচ্চ নম্বরটি খুঁজছেন:

=XLOOKUP(80, B2:B5, A2:A5, "মাঝে নেই", 1)

এটি "রিয়াদ" ফেরত দেবে, কারণ রিয়াদের নম্বর ৮৫ এবং এটি ৮০ এর কাছাকাছি সর্বোচ্চ।

XLOOKUP এর সুবিধাসমূহ

XLOOKUP ফাংশনের মাধ্যমে আপনি কয়েকটি গুরুত্বপূর্ণ সুবিধা উপভোগ করতে পারবেন:

1. নমনীয়তা: XLOOKUP ভিভিন্নভাবে কাজ করতে পারে—এক্সাক্ট ম্যাচ (exact match), কাছাকাছি ম্যাচ (approximate match), উল্লম্ব এবং অনুভূমিক লুকআপ। এটি VLOOKUP এবং HLOOKUP এর তুলনায় অনেক বেশি নমনীয়।
2. ডেটা সাজানোর প্রয়োজন নেই: VLOOKUP এ রেফারেন্স কলামের ডেটা সাজানো প্রয়োজন হলেও, XLOOKUP এ এ ধরনের কোনো বাধ্যবাধকতা নেই। আপনি যে কোনো কলামে লুকআপ করতে পারেন, এবং এটি অর্ডার অনুসারে কাজ করবে।
3. ফাংশনটির সহজতা: XLOOKUP এর সিঙ্কট্যাক্স সোজা এবং পরিষ্কার। এতে কলাম ইনডেক্স বা রেঞ্জ লুকআপ এর মতো অতিরিক্ত কনফিউজিং অপশন নেই যা VLOOKUP বা INDEX-MATCH-এ থাকে।
4. তিনটি আউটপুট: VLOOKUP-এ আপনি শুধু সঠিক মান বা কাছাকাছি মানই খুঁজতে পারেন, তবে XLOOKUP-এ আপনি মান না পাওয়ার ক্ষেত্রে কাস্টম মেসেজও নির্ধারণ করতে পারেন, যা আপনার ডেটা পরিচালনার ক্ষেত্রে সুবিধাজনক।

সারাংশ

XLOOKUP ফাংশনটি এক্সেলের একটি অত্যন্ত শক্তিশালী এবং নমনীয় ফাংশন যা ডেটা লুকআপ কাজকে অনেক সহজ করে তোলে। এটি ভিভিন্ন প্রয়োজনে ব্যবহৃত হতে পারে এবং এক্সেল ব্যবহারকারীদের জন্য আধুনিক লুকআপের সবচেয়ে ভালো সমাধান হিসেবে প্রমাণিত হয়েছে। এর মাধ্যমে আপনি দ্রুত ও কার্যকরভাবে লুকআপ করতে পারবেন এবং আপনি বিভিন্ন শর্তে কাস্টমাইজড ফলাফলও পেতে পারেন।

INDIRECT এবং ADDRESS ফাংশন এক্সেলের শক্তিশালী রেফারেন্স ফাংশন হিসেবে পরিচিত, যা ডাইনামিক রেঞ্জ ও সেল রেফারেন্স তৈরি করতে সাহায্য করে। এই ফাংশনগুলো ব্যবহার করে আপনি এমন সেল রেফারেন্স তৈরি করতে পারেন যেগুলি অন্যান্য সেল বা শিটের মানের উপর ভিত্তি করে পরিবর্তিত হতে পারে। এগুলি বিশেষভাবে তখন কার্যকরী যখন আপনি ডাইনামিকভাবে রেঞ্জ বা সেল রেফারেন্স তৈরি করতে চান।

INDIRECT ফাংশন

INDIRECT ফাংশনটি এক্সেলে একটি রেঞ্জ বা সেলের রেফারেন্সকে টেক্সট হিসেবে ব্যবহার করতে সক্ষম। এটি মূলত টেক্সটের মাধ্যমে সেল রেফারেন্স তৈরি করে এবং সেই সেল বা রেঞ্জে থাকা মানকে ফেরত দেয়।

ব্যবহার:
=INDIRECT(ref_text, [a1])

• ref_text: এটি হলো সেই সেল রেফারেন্স বা টেক্সট স্ট্রিং যা আপনি ফাংশনে দিতে চান। উদাহরণস্বরূপ, "A1" বা "Sheet2!B5"।
• [a1]: এটি ঐচ্ছিক প্যারামিটার, যেখানে আপনি নির্ধারণ করতে পারেন ফর্মুলা A1 স্টাইল রেফারেন্স ব্যবহার করবে কিনা (যদি TRUE থাকে) বা R1C1 স্টাইল রেফারেন্স (যদি FALSE থাকে)।

উদাহরণ

ধরা যাক, আপনি সেল A1-এ "B2" লিখেছেন এবং B2 সেলে একটি মান রয়েছে। এখন, আপনি INDIRECT ফাংশন ব্যবহার করে A1 সেলের মান অনুযায়ী B2 সেলের মানকে রিটার্ন করতে চান। তাহলে ফর্মুলাটি হবে:

=INDIRECT(A1)

এটি A1 সেলের মধ্যে থাকা "B2" রেফারেন্সকে ব্যবহার করে, এবং B2 সেলে যেটি মান রয়েছে সেটি রিটার্ন করবে।

ADDRESS ফাংশন

ADDRESS ফাংশনটি নির্দিষ্ট সারি (row) ও কলাম (column) নম্বরের ভিত্তিতে একটি সেলের রেফারেন্স তৈরি করে। এটি মূলত একটি টেক্সট স্ট্রিং হিসেবে সেল রেফারেন্স দেয়।

ব্যবহার:
=ADDRESS(row_num, column_num, [abs_num], [a1], [sheet])

• row_num: সেলের সারির সংখ্যা (যেমন, 1, 2, 3)।
• column_num: সেলের কলামের সংখ্যা (যেমন, 1 = A, 2 = B, 3 = C)।
• [abs_num]: এটি ঐচ্ছিক প্যারামিটার, যা সেল রেফারেন্সের আপেক্ষিকতা নির্ধারণ করে (1 = আবশ্যক, 2 = কলাম ফিক্স, 3 = সারি ফিক্স, 4 = আপেক্ষিক)।
• [a1]: এটি ঐচ্ছিক প্যারামিটার, যেখানে আপনি A1 রেফারেন্স স্টাইল (TRUE) বা R1C1 স্টাইল (FALSE) নির্বাচন করতে পারেন।
• [sheet]: এটি ঐচ্ছিক প্যারামিটার, যেখানে আপনি শিট নাম প্রদান করতে পারেন, উদাহরণস্বরূপ, "Sheet1"।

উদাহরণ

ধরা যাক, আপনি একটি সেলের ঠিকানা তৈরি করতে চান, যেখানে সারি নম্বর হবে 3 এবং কলাম নম্বর হবে 2 (যা হবে সেল B3)। তাহলে ফর্মুলাটি হবে:

=ADDRESS(3, 2)

এটি "$B$3" রিটার্ন করবে। আপনি যদি আংশিক আপেক্ষিক রেফারেন্স চান, যেমন শুধুমাত্র কলাম ফিক্স করে সারি আপেক্ষিক রেখে, তাহলে:

=ADDRESS(3, 2, 2)

এটি B$3 রিটার্ন করবে।

INDIRECT এবং ADDRESS ফাংশন একসাথে ব্যবহার

এই দুটি ফাংশন একসাথে ব্যবহার করলে আপনি ডাইনামিক সেল রেফারেন্স তৈরি করতে পারবেন, যা বিভিন্ন কাজের জন্য অত্যন্ত উপকারী।

উদাহরণ

ধরা যাক, আপনি একটি সেল থেকে একটি রেঞ্জের মান নিতে চান, তবে রেঞ্জের অবস্থান ডাইনামিকভাবে পরিবর্তন হবে। তাহলে আপনি ADDRESS এবং INDIRECT ফাংশন একসাথে ব্যবহার করতে পারেন।

যদি আপনি সেল A1 থেকে সারি এবং কলাম নম্বর পেতে চান, এবং সেই অনুযায়ী রেফারেন্স তৈরি করতে চান, তাহলে ফর্মুলাটি হবে:

=INDIRECT(ADDRESS(A1, 2))

এটি A1 সেলে থাকা সারি নম্বর অনুযায়ী কলাম 2 থেকে রেফারেন্স তৈরি করে এবং মান রিটার্ন করবে।

সারাংশ

INDIRECT এবং ADDRESS ফাংশন এক্সেলে ডাইনামিক সেল রেফারেন্স তৈরি করতে ব্যবহৃত হয়। INDIRECT ফাংশন একটি টেক্সট রেফারেন্স থেকে সেল বা রেঞ্জের মান পেতে সাহায্য করে, আর ADDRESS ফাংশনটি সারি ও কলাম নম্বর থেকে সেল রেফারেন্স তৈরি করে। একত্রে ব্যবহার করে আপনি ডাইনামিকভাবে সেল বা রেঞ্জের মান অনুসন্ধান করতে এবং এমন সেল রেফারেন্স তৈরি করতে পারবেন যা পরিবর্তিত হতে পারে, যা বিশেষত বড় ডেটা সেটের সাথে কাজ করার সময় কার্যকরী।