এক্সেলে INDEX এবং MATCH ফাংশন একসাথে ব্যবহার করার পদ্ধতি

জানা দরকার

INDEX ফাংশন একা ব্যবহার করা যায়, কিন্তু MATCH ফাংশনকে এর মধ্যে নেস্ট করলে একটি উন্নত লুকআপ তৈরি হয়।
এই নেস্টেড ফাংশন VLOOKUP-র চেয়ে বেশি ফ্লেক্সিবল এবং দ্রুত ফলাফল দিতে পারে।

এই আর্টিকেলটি ব্যাখ্যা করে কীভাবে INDEX এবং MATCH ফাংশন দুটো একসাথে ব্যবহার করতে হয় এক্সেলের সব সংস্করণে, এক্সেল ২০১৯ এবং মাইক্রোসফট ৩৬৫ সহ।

INDEX এবং MATCH ফাংশনগুলো কী?

INDEX এবং MATCH এক্সেলের লুকআপ ফাংশন। যদিও এগুলো দুটি সম্পূর্ণ আলাদা ফাংশন যেগুলো আলাদা আলাদা ব্যবহার করা যায়, তবে এগুলোকে কাম্বাইন করেও উন্নত ফর্মুলা তৈরি করা যায়।

INDEX ফাংশন একটি নির্দিষ্ট সিলেকশনের ভিতর থেকে একটি মান বা একটি মানের রেফারেন্স ফেরত দেয়। উদাহরণস্বরূপ, এটি ব্যবহার করা যায় একটি ডেটা সেটের দ্বিতীয় সারির মান খুঁজে বের করতে, অথবা পঞ্চম সারি এবং তৃতীয় কলামের মান জানতে।

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

এক্সেলে INDEX এবং MATCH ফাংশন একসাথে ব্যবহার করার পদ্ধতি

INDEX এবং MATCH সিনট্যাক্স ও আর্গুমেন্ট

এই দুটি ফাংশন কীভাবে লিখতে হয় যাতে এক্সেল সেগুলো বুঝতে পারে:

=INDEX(array, row_num, [column_num])

array হলো কোষের সেই রেঞ্জ যা ফর্মুলা ব্যবহার করবে। এটি এক বা একাধিক সারি ও কলাম হতে পারে, যেমন A1:D5। এটি বাধ্যতামূলক।
row_num হলো array-এ সেই সারি যার থেকে মান ফেরত দিতে হবে, যেমন ২ বা ১৮। এটি বাধ্যতামূলক যদি column_num উপস্থিত না থাকে।
column_num হলো array-এ সেই কলাম যার থেকে মান ফেরত দিতে হবে, যেমন ১ বা ৯। এটি ঐচ্ছিক।

=MATCH(lookup_value, lookup_array, [match_type])

lookup_value হলো আপনি lookup_array-এ যে মানটি মিলাতে চান। এটি সংখ্যা, টেক্সট, বা লজিক্যাল মান হতে পারে যা ম্যানুয়ালি টাইপ করা হয় বা কোষ রেফারেন্স দিয়ে নির্দেশ করা হয়। এটি বাধ্যতামূলক।
lookup_array হলো খুঁজে বের করার কোষের রেঞ্জ। এটি একটি সারি বা একটি কলাম হতে পারে, যেমন A2:D2 বা G1:G45। এটি বাধ্যতামূলক।
match_type হতে পারে -1, 0, বা 1। এটি নির্দিষ্ট করে lookup_value কীভাবে lookup_array-এ মানগুলোর সাথে মিলাবে (নিচে দেখুন)। যদি এই আর্গুমেন্ট বাদ দেয়া হয়, 1 ডিফল্ট মান।

কোন Match Type ব্যবহার করবেন
Match Type	কী করবে	নিয়ম	উদাহরণ
1	lookup_value-এর চেয়ে ছোট বা সমান সবচেয়ে বড় মান খুঁজে বের করে। lookup_array মানগুলো आरোহী ক্রমে থাকতে হবে (যেমন -2, -1, 0, 1, 2; বা A-Z; বা FALSE, TRUE)।	lookup_value ২৫ কিন্তু lookup_array-এ নেই, তাই পরবর্তী ছোট সংখ্যা যেমন ২২-র পজিশন ফেরত দেবে।
0	lookup_value-এর সাথে ঠিক মিলে যাওয়া প্রথম মান খুঁজে বের করে। lookup_array মানগুলো যেকোনো ক্রমে থাকতে পারে।	lookup_value ২৫, তাই ২৫-র পজিশন ফেরত দেবে।
-1	lookup_value-এর চেয়ে বড় বা সমান সবচেয়ে ছোট মান খুঁজে বের করে। lookup_array মানগুলো অবরোহী ক্রমে থাকতে হবে (যেমন 2, 1, 0, -1, -2)।	lookup_value ২৫ কিন্তু lookup_array-এ নেই, তাই পরবর্তী বড় সংখ্যা যেমন ৩৪-র পজিশন ফেরত দেবে।

1 বা -1 ব্যবহার করুন যখন আপনাকে একটি স্কেলের সাথে অনুমানমূলক লুকআপ চালাতে হয়, যেমন সংখ্যার সাথে কাজ করার সময় এবং অনুমান গ্রহণযোগ্য হয়। কিন্তু মনে রাখবেন যদি আপনি match_type নির্দিষ্ট না করেন, 1 ডিফল্ট হবে, যা ফলাফল বিকৃত করতে পারে যদি আপনি আসলে এক্সাক্ট ম্যাচ চান।

INDEX এবং MATCH ফর্মুলার উদাহরণ

INDEX এবং MATCH কীভাবে একসাথে এক ফর্মুলায় কাম্বাইন করতে হয় সেটি দেখার আগে, আমাদের বুঝতে হবে এই ফাংশনগুলো আলাদা আলাদা কীভাবে কাজ করে।

INDEX উদাহরণ

=INDEX(A1:B2,2,2)
=INDEX(A1:B1,1)
=INDEX(2:2,1)
=INDEX(B1:B2,1)

এই প্রথম উদাহরণে, চারটি INDEX ফর্মুলা ব্যবহার করে আমরা ভিন্ন ভিন্ন মান পেতে পারি:

=INDEX(A1:B2,2,2) A1:B2-এ খুঁজে বের করে দ্বিতীয় কলাম এবং দ্বিতীয় সারির মান পায়, যা Stacy।
=INDEX(A1:B1,1) A1:B1-এ খুঁজে বের করে প্রথম কলামের মান পায়, যা Jon।
=INDEX(2:2,1) দ্বিতীয় সারির সব কিছু খুঁজে বের করে প্রথম কলামের মান পায়, যা Tim।
=INDEX(B1:B2,1) B1:B2-এ খুঁজে বের করে প্রথম সারির মান পায়, যা Amy।

MATCH উদাহরণ

=MATCH("Stacy",A2:D2,0)
=MATCH(14,D1:D2)
=MATCH(14,D1:D2,-1)
=MATCH(13,A1:D1,0)

MATCH ফাংশনের চারটি সহজ উদাহরণ:

=MATCH("Stacy",A2:D2,0) Stacy কে A2:D2 রেঞ্জে খুঁজে বের করে এবং ফলাফল হিসেবে 3 ফেরত দেয়।
=MATCH(14,D1:D2) 14 কে D1:D2 রেঞ্জে খুঁজে বের করে, কিন্তু টেবিলে না পাওয়া যাওয়ায় MATCH 14-এর চেয়ে ছোট বা সমান পরবর্তী সবচেয়ে বড় মান খুঁজে পায়, যা এই কেসে 13, যা lookup_array-এ পজিশন 1-এ আছে।
=MATCH(14,D1:D2,-1) উপরের ফর্মুলার মতোই, কিন্তু যেহেতু অ্যারেটি ডিসেন্ডিং অর্ডারে নয় যা -1 আশা করে, তাই এরর দেখাবে।
=MATCH(13,A1:D1,0) শিটের প্রথম সারিতে 13 খুঁজে বের করে, যা 4 ফেরত দেয় কারণ এটি এই অ্যারেতে চতুর্থ আইটেম।

INDEX-MATCH উদাহরণ

এখানে দুটি উদাহরণ দেওয়া হলো যেখানে INDEX এবং MATCH এক ফর্মুলায় কাম্বাইন করা হয়েছে:

টেবিলে কোষ রেফারেন্স খুঁজে বের করা

=INDEX(B2:B5,MATCH(F1,A2:A5))

এই উদাহরণে MATCH ফর্মুলা INDEX ফর্মুলার ভিতরে নেস্ট করা হয়েছে। লক্ষ্য হলো আইটেম নম্বর ব্যবহার করে আইটেমের রঙ শনাক্ত করা।

ছবিটি দেখলে "Separated" সারিগুলোতে ফর্মুলাগুলো আলাদা আলাদা কীভাবে লেখা হয়তো তা দেখা যাচ্ছে, কিন্তু যেহেতু আমরা সেগুলো নেস্ট করছি, এই ঘটনা ঘটছে:

MATCH(F1,A2:A5) F1 মান (৮৭৯৫) কে ডেটা সেট A2:A5-এ খুঁজে বের করছে। কলামে নিচে গণনা করলে দেখা যাচ্ছে এটি 2, তাই MATCH ফাংশনটি 2 ফেরত দিয়েছে।
INDEX array হলো B2:B5 কারণ আমরা সেটি কলাম থেকে মান খুঁজছি।
MATCH 2 খুঁজে পেলে INDEX ফর্মুলা এভাবে লিখা যেতো: INDEX(B2:B5, 2, [column_num])।
যেহেতু column_num ঐচ্ছিক, আমরা সেটি বাদ দিয়ে পাই: INDEX(B2:B5,2)।
এখন এটি একটি সাধারণ INDEX ফর্মুলার মতো যেখানে B2:B5-এ দ্বিতীয় আইটেমের মান খুঁজছি, যা red।

সারি এবং কলাম হেডিং দিয়ে লুকআপ করা

=INDEX(B2:E13,MATCH(G1,A2:A13,0),MATCH(G2,B1:E1,0))

এই MATCH এবং INDEX উদাহরণে, আমরা একটি টু-ওয়ে লুকআপ করছি। উদ্দেশ্য হলো Green আইটেমগুলোর May মहीने কত টাকা আয় হয়েছে তা জানা। এটি উপরের উদাহরণের মতোই, কিন্তু INDEX-এ একটি অতিরিক্ত MATCH ফর্মুলা নেস্ট করা হয়েছে।

MATCH(G1,A2:A13,0) এই ফর্মুলার প্রথম সমাধান। এটি G1 (শব্দ "May") কে A2:A13-এ খুঁজে বের করে একটি নির্দিষ্ট মান পাওয়ার জন্য। আমরা এখানে দেখতে পাচ্ছি না, কিন্তু এটি 5।
MATCH(G2,B1:E1,0) দ্বিতীয় MATCH ফর্মুলা, যা খুব অনুরূপ প্রথমটির মতো কিন্তু বদলে G2 (শব্দ "Green") কে কলাম হেডিং B1:E1-এ খুঁজে বের করছে। এটি 3 রেজল্ভ করে।
এখন আমরা INDEX ফর্মুলা এভাবে রিরাইট করতে পারি যা ঘটছে তা ভিজুয়ালাইজ করতে: =INDEX(B2:E13,5,3)। এটি পুরো টেবিল, B2:E13-এ, পঞ্চম সারি এবং তৃতীয় কলাম খুঁজে বের করে, যা ফেরত দেয় $180।

MATCH এবং INDEX নিয়মাবলী

এই ফাংশনগুলোর সাথে ফর্মুলা লিখার সময় কিছু বিষয় মনে রাখতে হবে:

MATCH কেস-সেনসিটিভ নয়, তাই টেক্সট মান মিলানোর সময় বড় এবং ছোট হাতের অক্ষর একইভাবে বিবেচনা করা হয়।
MATCH #N/A ফেরত দেয় একাধিক কারণদ্বারা: যদি match_type 0 হয় এবং lookup_value না পাওয়া যায়, যদি match_type -1 হয় এবং lookup_array ডিসেন্ডিং অর্ডারে নয়, যদি match_type 1 হয় এবং lookup_array অ্যাসেন্ডিং অর্ডারে নয়, এবং যদি lookup_array একক সারি বা কলাম নয়।
match_type 0 হলে এবং lookup_value টেক্সট স্ট্রিং হলে, আপনি lookup_value আর্গুমেন্টে 와াইল্ডকার্ড ক্যারেক্টার ব্যবহার করতে পারেন। প্রশ্নবোধক চিহ্ন যেকোনো একক ক্যারেক্টার মিলায় এবং তারকাচিহ্ন যেকোনো ক্যারেক্টার সিকোয়েন্স মিলায় (যেমন, =MATCH("Jo*",1:1,0))। MATCH দিয়ে আসল প্রশ্নবোধক বা তারকাচিহ্ন খুঁজতে হলে, প্রথমে ~ টাইপ করুন।
INDEX #REF! ফেরত দেয় যদি row_num এবং column_num array-এ কোনো কোষে নির্দেশ না করে।