এক্সেল

23 টি জিনিস যা আপনার VLOOKUP সম্পর্কে জানা উচিত

23 Things You Should Know About Vlookup

যখন আপনি একটি টেবিল থেকে তথ্য টানতে চান, তখন Excel VLOOKUP ফাংশন একটি দুর্দান্ত সমাধান। একটি টেবিল থেকে গতিশীলভাবে অনুসন্ধান এবং তথ্য পুনরুদ্ধারের ক্ষমতা অনেক ব্যবহারকারীর জন্য একটি গেম-চেঞ্জার, এবং আপনি সর্বত্র VLOOKUP পাবেন।





এবং তবুও, যদিও VLOOKUP ব্যবহার করা তুলনামূলকভাবে সহজ, সেখানে প্রচুর পরিমাণে ভুল হতে পারে। একটি কারণ হল যে VLOOKUP এর একটি প্রধান নকশা ত্রুটি রয়েছে - ডিফল্টরূপে, এটি অনুমান করে যে আপনি আনুমানিক মিলের সাথে ঠিক আছেন। যা আপনি সম্ভবত নন।

এই যে ফলাফল হতে পারে সম্পূর্ণ স্বাভাবিক চেহারা , যদিও তারা সম্পূর্ণ ভুল । আমাকে বিশ্বাস করুন, এটি এমন কিছু নয় যা আপনি আপনার বসকে বোঝানোর চেষ্টা করতে চান, যখন তিনি ইতিমধ্যেই আপনার স্প্রেডশীট ব্যবস্থাপনায় পাঠিয়ে দিয়েছেন :)





এই চ্যালেঞ্জটি কীভাবে পরিচালনা করতে হয় তা নীচে পড়ুন এবং এতে দক্ষতা অর্জনের জন্য অন্যান্য টিপস আবিষ্কার করুন এক্সেল VLOOKUP ফাংশন ।

1. কিভাবে VLOOKUP কাজ করে

VLOOKUP হল একটি টেবিলে ডেটা খোঁজার এবং পুনরুদ্ধারের একটি ফাংশন। VLOOKUP- এ 'V' মানে উল্লম্ব, যার মানে টেবিলে থাকা ডেটা সারিবদ্ধভাবে ডেটা সহ উল্লম্বভাবে সাজাতে হবে। (অনুভূমিকভাবে কাঠামোগত তথ্যের জন্য, দেখুন HLOOKUP )।



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

VLOOKUP- এর জন্য টেবিলের কাঠামো তৈরি করা প্রয়োজন যাতে বাম দিকের কলামে সন্ধানের মানগুলি উপস্থিত হয়। আপনি যে ডেটা পুনরুদ্ধার করতে চান (ফলাফল মান) ডানদিকে যে কোনও কলামে উপস্থিত হতে পারে। যখন আপনি VLOOKUP ব্যবহার করেন, কল্পনা করুন যে টেবিলের প্রতিটি কলাম বাম থেকে শুরু করে সংখ্যাযুক্ত। একটি নির্দিষ্ট কলাম থেকে একটি মান পেতে, কেবল 'কলাম সূচক' হিসাবে উপযুক্ত সংখ্যা সরবরাহ করুন। নীচের উদাহরণে, আমরা ইমেল ঠিকানাটি সন্ধান করতে চাই, তাই আমরা কলাম সূচকের জন্য 4 নম্বর ব্যবহার করছি:

VLOOKUP কিভাবে কাজ করে তার ওভারভিউ

উপরের টেবিলে, কর্মচারী আইডি বাম দিকে কলাম 1 এবং ইমেল ঠিকানাগুলি ডানদিকে 4 নম্বর কলামে রয়েছে।

VLOOKUP ব্যবহার করতে, আপনি 4 টুকরো তথ্য, অথবা 'আর্গুমেন্ট' সরবরাহ করেন:

  1. আপনি যে মানটি খুঁজছেন ( দেখার মূল্য )
  2. টেবিল তৈরির কোষের পরিসীমা ( টেবিল_ অ্যারে )
  3. কলামের সংখ্যা যেখান থেকে একটি ফলাফল পুনরুদ্ধার করতে হবে ( কলাম_ ইন্ডেক্স )
  4. ম্যাচ মোড ( range_lookup , সত্য = আনুমানিক, মিথ্যা = সঠিক)

ভিডিও: কিভাবে VLOOKUP ব্যবহার করবেন (3 মিনিট)

যদি আপনি এখনও VLOOKUP- এর প্রাথমিক ধারণা না পান, তাহলে এক্সেল ক্যাম্পাসে জন আকাম্পোরা, মহান ব্যাখ্যা স্টারবাক্স কফি মেনুর উপর ভিত্তি করে।

2. VLOOKUP শুধুমাত্র সঠিক দেখায়

সম্ভবত VLOOKUP এর সবচেয়ে বড় সীমাবদ্ধতা হল যে এটি শুধুমাত্র তথ্য পুনরুদ্ধারের অধিকার দেখতে পারে।

এর মানে হল যে VLOOKUP শুধুমাত্র টেবিলের প্রথম কলামের ডানদিকে কলাম থেকে ডেটা পেতে পারে। যখন প্রথম (বামদিকের) কলামে সন্ধানের মানগুলি প্রদর্শিত হয়, তখন এই সীমাবদ্ধতার অর্থ খুব বেশি নয়, যেহেতু অন্যান্য সমস্ত কলাম ইতিমধ্যে ডানদিকে রয়েছে। যাইহোক, যদি সন্ধানের কলামটি টেবিলের ভিতরে কোথাও প্রদর্শিত হয়, আপনি কেবলমাত্র কলাম থেকে সেই কলামের ডানদিকে মানগুলি সন্ধান করতে সক্ষম হবেন। আপনাকে VLOOKUP এ একটি ছোট টেবিল সরবরাহ করতে হবে যা লুকআপ কলাম দিয়ে শুরু হয়।

VLOOKUP শুধুমাত্র লুকআপ ভ্যালু কলামের ডানদিকে দেখতে পারে

আপনি VLOOKUP এর পরিবর্তে INDEX এবং MATCH ব্যবহার করে এই সীমাবদ্ধতা অতিক্রম করতে পারেন।

3. VLOOKUP প্রথম ম্যাচ খুঁজে পায়

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

VLOOKUP সবসময় প্রথম ম্যাচ খুঁজে পায়

দ্রষ্টব্য: আনুমানিক মিল মোডে VLOOKUP ব্যবহার করা হলে আচরণ পরিবর্তন হতে পারে। এই নিবন্ধটি বিস্তারিতভাবে বিষয় ব্যাখ্যা করে।

4. VLOOKUP কেস-সংবেদনশীল নয়

যখন একটি মান খুঁজছেন, VLOOKUP বড় এবং ছোট কেস টেক্সট ভিন্নভাবে প্রক্রিয়া করে না। VLOOKUP- এর জন্য, 'PQRF' এর মত একটি পণ্য কোড 'pqrf' এর অনুরূপ। নীচের উদাহরণে, আমরা বড় হাতের 'JANET' খুঁজছি কিন্তু VLOOKUP কেস কে আলাদা করে না তাই এটি কেবল 'জ্যানেট' এর সাথে মেলে, যেহেতু এটিই প্রথম ম্যাচটি খুঁজে পায়:

VLOOKUP কেস-সংবেদনশীল নয়

আমরাও অফার করি প্রদত্ত প্রশিক্ষণ VLOOKUP এবং INDEX/MATCH এর জন্য

5. VLOOKUP- এর দুটি ম্যাচিং মোড রয়েছে

VLOOKUP এর অপারেশনের দুটি পদ্ধতি রয়েছে: সঠিক মিল এবং আনুমানিক মিল। বেশিরভাগ ক্ষেত্রে, আপনি সম্ভবত সঠিক মিল মোডে VLOOKUP ব্যবহার করতে চান। যখন আপনি কোন ধরণের অনন্য কী -এর উপর ভিত্তি করে তথ্য সন্ধান করতে চান তখন এটি বোধগম্য হয়, উদাহরণস্বরূপ, একটি পণ্য কোডের উপর ভিত্তি করে পণ্যের তথ্য, বা একটি চলচ্চিত্রের শিরোনামের উপর ভিত্তি করে মুভি ডেটা:

VLOOKUP হুবহু মিলের উদাহরণ - মিলে যাওয়া সিনেমা

সিনেমার শিরোনামের একটি সঠিক মিলের উপর ভিত্তি করে H6- এর সন্ধানের সূত্রটি হল:

 
= VLOOKUP (H4,B5:E9,2,FALSE) // FALSE = exact match

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

VLOOKUP আনুমানিক মিল উদাহরণ - কমিশন

D5 এর সূত্রটি সঠিক কমিশন পুনরুদ্ধারের জন্য একটি আনুমানিক মিল করে:

 
= VLOOKUP (C5,$G:$H,2,TRUE) // TRUE = approximate match

6. সতর্কতা: VLOOKUP ডিফল্টরূপে আনুমানিক মিল ব্যবহার করে

VLOOKUP- এ সঠিক এবং আনুমানিক মিল 4 র্থ যুক্তি দ্বারা নিয়ন্ত্রিত হয়, যাকে বলা হয় 'রেঞ্জ লুকআপ'। এই নামটি স্বজ্ঞাত নয়, তাই এটি কীভাবে কাজ করে তা আপনাকে কেবল মুখস্থ করতে হবে।

সঠিক মিলের জন্য, মিথ্যা বা 0. ব্যবহার করুন আনুমানিক মিলের জন্য, range_lookup কে TRUE বা 1 এ সেট করুন:

 
= VLOOKUP (value,table,column,TRUE) // approximate match = VLOOKUP (value,table,column,FALSE) // exact match

দুর্ভাগ্যবশত, 4th র্থ যুক্তি, range_lookup, optionচ্ছিক এবং TRUE- এর ডিফল্ট, যার মানে VLOOKUP ডিফল্টরূপে আনুমানিক মিল করবে। একটি আনুমানিক মিল করার সময়, VLOOKUP টেবিলটি সাজানো অনুমান করে এবং একটি বাইনারি অনুসন্ধান করে। একটি বাইনারি অনুসন্ধানের সময়, যদি VLOOKUP একটি সঠিক মিলের মান খুঁজে পায়, এটি সেই সারি থেকে একটি মান প্রদান করে। যদি তবে, VLOOKUP সন্ধানের মূল্যের চেয়ে বড় মানের সম্মুখীন হয়, এটি পূর্ববর্তী সারি থেকে একটি মান ফেরত দেবে।

এটি একটি বিপজ্জনক ডিফল্ট কারণ অনেক মানুষ অনিচ্ছাকৃতভাবে VLOOKUP এর ডিফল্ট মোডে চলে যায়, যা একটি ভুল ফলাফল যখন টেবিল সাজানো হয় না।

এই সমস্যাটি এড়ানোর জন্য, যখন আপনি একটি সঠিক মিল চান তখন argument র্থ যুক্তি হিসাবে মিথ্যা বা শূন্য ব্যবহার করতে ভুলবেন না।

7. আপনি VLOOKUP কে একটি সঠিক মিল করতে বাধ্য করতে পারেন

VLOOKUP কে একটি সঠিক মিল খুঁজে পেতে বাধ্য করতে, 4 টি যুক্তি (range_lookup) মিথ্যা বা শূন্যতে সেট করতে ভুলবেন না। এই দুটি সূত্র সমতুল্য:

 
= VLOOKUP (value, data, column, FALSE) = VLOOKUP (value, data, column, 0)

সঠিক মিল মোডে, যখন VLOOKUP কোনো মান খুঁজে পায় না, তখন এটি #N/A ফিরিয়ে দেবে। এটি একটি স্পষ্ট ইঙ্গিত যে মানটি টেবিলে পাওয়া যায় না।

8. আপনি VLOOKUP কে আনুমানিক মিল করতে বলতে পারেন

আনুমানিক মিল মোডে VLOOKUP ব্যবহার করতে, হয় the র্থ যুক্তি (range_lookup) বাদ দিন অথবা এটিকে TRUE বা ১ হিসাবে সরবরাহ করুন। এই formula টি সূত্র সমতুল্য:

 
= VLOOKUP (value, data, column) = VLOOKUP (value, data, column, 1) = VLOOKUP (value, data, column, TRUE)

আমরা সুপারিশ করি যে আপনি সর্বদা পরিসর_লুকআপ যুক্তি স্পষ্টভাবে সেট করুন, যদিও VLOOKUP এর প্রয়োজন নেই। এই ভাবে, আপনি সবসময় ম্যাচ মোডের একটি ভিজ্যুয়াল রিমাইন্ডার আশা করেন।

ভিডিও: আনুমানিক মিলের জন্য কিভাবে VLOOKUP ব্যবহার করবেন

9. আনুমানিক মিলের জন্য, ডেটা বাছাই করা আবশ্যক

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

ফেলিয়ান হারম্যানস এখানে এই সমস্যার একটি দুর্দান্ত উদাহরণ রয়েছে , তিনি একটি বাস্তব বিশ্লেষণ থেকে এনরন স্প্রেডশীট!

10. VLOOKUP বিভিন্ন টেবিলে ডেটা একত্রিত করতে পারে

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

VLOOKUP টেবিলে যোগদান করে ডেটা একত্রিত করে -আগে

যেহেতু গ্রাহক আইডি উভয় টেবিলে বিদ্যমান, আপনি VLOOKUP এর সাহায্যে আপনার প্রয়োজনীয় ডেটা টানতে এই মানটি ব্যবহার করতে পারেন। টেবিল এক -এ আইডি মান এবং প্রয়োজনীয় কলাম সূচী সহ টেবিল 2 -এ ডেটা ব্যবহার করতে VLOOKUP কনফিগার করুন। নীচের উদাহরণে, আমরা দুটি VLOOKUP সূত্র ব্যবহার করছি। একটি গ্রাহকের নাম টানতে, এবং অন্যটি গ্রাহক অবস্থায় টানতে।

VLOOKUP টেবিল -এ যোগদান করে ডেটা একত্রিত করে

লিঙ্ক: VLOOKUP এর সাথে একীভূত হওয়ার উদাহরণ

ভিডিও: টেবিল মার্জ করার জন্য VLOOKUP কিভাবে ব্যবহার করবেন

11. VLOOKUP ডেটা শ্রেণীবদ্ধ বা শ্রেণিবদ্ধ করতে পারে

আপনার যদি কখনও ডেটা রেকর্ডে ইচ্ছাকৃত বিভাগগুলি প্রয়োগ করার প্রয়োজন হয়, তবে আপনি VLOOKUP এর সাথে সহজেই এটি করতে পারেন, একটি টেবিল ব্যবহার করে যা বিভাগগুলি নির্ধারণের জন্য 'কী' হিসাবে কাজ করে।

একটি ক্লাসিক উদাহরণ হল গ্রেড, যেখানে আপনাকে স্কোরের উপর ভিত্তি করে একটি গ্রেড বরাদ্দ করতে হবে:

VLOOKUP শ্রেণীভুক্ত করতে ব্যবহৃত - গ্রেড বরাদ্দ

এই ক্ষেত্রে, VLOOKUP আনুমানিক মিলের জন্য কনফিগার করা হয়েছে, তাই টেবিলটি আরোহী ক্রমে সাজানো গুরুত্বপূর্ণ।

কিন্তু আপনি ইচ্ছামত বিভাগগুলি বরাদ্দ করতে VLOOKUP ব্যবহার করতে পারেন। নীচের উদাহরণে, আমরা একটি ছোট টেবিল ('কী' নামে) ব্যবহার করে প্রতিটি বিভাগের জন্য একটি গ্রুপ গণনা করতে VLOOKUP ব্যবহার করছি যা গ্রুপিংকে সংজ্ঞায়িত করে।

VLOOKUP শ্রেণীবিন্যাস করতে ব্যবহৃত - নির্বিচারে গ্রুপ বরাদ্দ করা

12. পরম রেফারেন্সগুলি VLOOKUP কে আরও বহনযোগ্য করে তোলে

এমন পরিস্থিতিতে যেখানে আপনি একটি টেবিলে একাধিক কলাম থেকে তথ্য পুনরুদ্ধার করার পরিকল্পনা করছেন, অথবা যদি আপনাকে VLOOKUP কপি এবং পেস্ট করার প্রয়োজন হয়, আপনি লুকআপ ভ্যালু এবং টেবিল অ্যারের জন্য পরম রেফারেন্স ব্যবহার করে সময় এবং উত্তেজনা বাঁচাতে পারেন। এটি আপনাকে সূত্রটি অনুলিপি করতে দেয় এবং তারপরে একটি ভিন্ন কলাম থেকে একটি মান পেতে একই লুকআপ ব্যবহার করতে শুধুমাত্র কলাম সূচী নম্বর পরিবর্তন করে।

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

পরম রেফারেন্সগুলি VLOOKUP সূত্রগুলিকে আরও বহনযোগ্য করে তোলে

13. নামযুক্ত রেঞ্জ VLOOKUP পড়তে সহজ করে (এবং আরো বহনযোগ্য)

পরম রেঞ্জগুলি বেশ কুৎসিত দেখায়, তাই আপনার VLOOKUP সূত্রগুলিকে অনেক পরিস্কার এবং নামযুক্ত রেঞ্জের সাথে পরম রেফারেন্সগুলি প্রতিস্থাপন করে পড়তে সহজ করে তুলতে পারে, যা স্বয়ংক্রিয়ভাবে পরম।

উদাহরণস্বরূপ, উপরের কর্মচারী ডেটা উদাহরণে, আপনি ইনপুট সেল 'আইডি' নাম দিতে পারেন এবং তারপরে টেবিল 'ডেটা' তে ডেটার নাম দিতে পারেন, আপনি আপনার সূত্রটি নিম্নরূপ লিখতে পারেন:

নামযুক্ত রেঞ্জগুলি VLOOKUP সূত্রগুলি পড়তে সহজ করে তোলে

এই সূত্রটি কেবল পড়া সহজ নয়, এটি আরও বহনযোগ্য, যেহেতু নামযুক্ত রেঞ্জগুলি স্বয়ংক্রিয়ভাবে পরম।

14. একটি কলাম tingোকাতে বিদ্যমান VLOOKUP সূত্রগুলি ভেঙে যেতে পারে

যদি আপনার একটি ওয়ার্কশীটে বিদ্যমান VLOOKUP ফর্মুলা থাকে, তাহলে আপনি টেবিলে একটি কলাম ifোকালে সূত্র ভেঙে যেতে পারে। এর কারণ হল কলাম insোকানো বা মুছে ফেলা হলে হার্ড-কোডেড কলাম ইনডেক্স মানগুলি স্বয়ংক্রিয়ভাবে পরিবর্তিত হয় না।

এই উদাহরণে, বছর এবং র্যাঙ্কের মধ্যে একটি নতুন কলাম ertedোকানোর সময় র R্যাঙ্ক এবং বিক্রয়ের সন্ধানগুলি ভেঙে যায়। বছর কাজ চালিয়ে যাচ্ছে কারণ এটি সন্নিবেশিত কলামের বাম দিকে রয়েছে:

টেবিলে একটি কলাম tingোকালে VLOOKUP ভেঙ্গে যেতে পারে

এই সমস্যা এড়ানোর জন্য, আপনি পরবর্তী দুটি টিপসে বর্ণিত একটি কলাম সূচক গণনা করতে পারেন।

15. আপনি একটি কলাম সূচক গণনা করতে ROW বা COLUMN ব্যবহার করতে পারেন

আপনি যদি এমন একজন ব্যক্তি যিনি একটি সূত্র অনুলিপি করার পরে যে কোনও পরিমাণ সম্পাদনা দ্বারা বিরক্ত হন, আপনি গতিশীল কলাম সূচী তৈরি করতে ROW বা COLUMN ব্যবহার করতে পারেন। যদি আপনি পরপর কলাম থেকে ডেটা পাচ্ছেন, এই কৌশলটি আপনাকে একটি VLOOKUP সূত্র সেট আপ করতে দেয়, তারপর কোন পরিবর্তন প্রয়োজন ছাড়া এটি জুড়ে অনুলিপি করুন।

উদাহরণস্বরূপ, নীচের কর্মচারী ডেটা সহ, আমরা একটি গতিশীল কলাম সূচক তৈরি করতে COLUMN ফাংশন ব্যবহার করতে পারি। কোষ C3- এর প্রথম সূত্রের জন্য, COLUMN নিজেই 3 ফিরে আসবে (কারণ কলামটি ওয়ার্কশীটে তৃতীয়) তাই আমাদের কেবল একটি বিয়োগ করতে হবে, এবং সূত্র জুড়ে অনুলিপি করতে হবে:

VLOOKUP এর জন্য কলাম সূচক গণনা করতে COLUMN ব্যবহার করার উদাহরণ

সমস্ত সূত্র অভিন্ন এবং কোন পোস্ট-সম্পাদনার প্রয়োজন নেই।

আমরা যে সূত্রটি ব্যবহার করছি তা হল:

 
= VLOOKUP (id,data, COLUMN ()-1,0)

16. একটি সম্পূর্ণ গতিশীল কলাম সূচকের জন্য VLOOKUP + MATCH ব্যবহার করুন

উপরের টিপটি এক ধাপ এগিয়ে নিয়ে, আপনি একটি টেবিলে একটি কলামের অবস্থান সন্ধান করতে এবং একটি সম্পূর্ণ গতিশীল কলাম সূচক ফিরিয়ে আনতে MATCH ব্যবহার করতে পারেন।

এটিকে কখনও কখনও দ্বিমুখী সন্ধান বলা হয় যেহেতু আপনি সারি এবং কলাম উভয়ই খুঁজছেন।

একটি উদাহরণ একটি নির্দিষ্ট মাসে একজন বিক্রয়কর্মীর জন্য বিক্রয় খুঁজতে হবে, অথবা একটি নির্দিষ্ট সরবরাহকারীর কাছ থেকে একটি নির্দিষ্ট পণ্যের দাম খুঁজতে হবে।

উদাহরণস্বরূপ, ধরুন আপনার প্রতি মাসে বিক্রয় আছে, বিক্রেতা দ্বারা বিভক্ত:

VLOOKUP দুই উপায় সন্ধান - কিভাবে মাস সন্ধান করবেন?

VLOOKUP সহজেই বিক্রয় ব্যক্তি খুঁজে পেতে পারে, কিন্তু মাসের নাম স্বয়ংক্রিয়ভাবে পরিচালনা করার কোন উপায় নেই। কৌশলটি একটি স্ট্যাটিক কলাম সূচকের জায়গায় MATCH ফাংশন ব্যবহার করা।

VLOOKUP কলাম ইনডেক্স পেতে MATCH ব্যবহার করে দুই উপায় সন্ধান করুন

লক্ষ্য করুন যে আমরা VLOOKUP দ্বারা ব্যবহৃত কলাম সংখ্যাগুলিকে 'সিঙ্ক আপ' করার জন্য টেবিলের সমস্ত কলাম অন্তর্ভুক্ত করে এমন একটি পরিসর প্রদান করি।

 
= VLOOKUP (H2,data, MATCH (H3,months,0),0)

দ্রষ্টব্য: আপনি প্রায়শই INDEX এবং MATCH- এর মাধ্যমে দুটি উপায় অনুসন্ধান দেখতে পাবেন, এমন একটি পদ্ধতি যা বড় ডেটা সেটে আরও নমনীয়তা এবং ভাল পারফরম্যান্স প্রদান করে। এই দ্রুত ভিডিওতে দেখুন: INDEX এবং MATCH এর সাথে কিভাবে দ্বিমুখী সন্ধান করা যায় ।

17. VLOOKUP আংশিক মিলের জন্য ওয়াইল্ডকার্ডের অনুমতি দেয়

যে কোন সময় আপনি VLOOKUP সঠিক মিল মোডে ব্যবহার করছেন, আপনার কাছে লুকআপ ভ্যালুতে ওয়াইল্ডকার্ড ব্যবহারের বিকল্প আছে। এটি বিপরীত মনে হতে পারে, কিন্তু ওয়াইল্ডকার্ডগুলি আপনাকে একটি আংশিক মিলের উপর ভিত্তি করে একটি সঠিক ম্যাচ করতে দেয় :)

এক্সেল দুটি ওয়াইল্ডকার্ড অক্ষর প্রদান করে: একটি তারকাচিহ্ন (*) এক বা একাধিক অক্ষরের সাথে মিলে যায়, এবং একটি প্রশ্ন চিহ্ন (?) একটি অক্ষরের সাথে মেলে।

উদাহরণস্বরূপ, আপনি সরাসরি একটি সেলে একটি তারকাচিহ্ন টাইপ করতে পারেন এবং এটিকে VLOOKUP এর সাথে একটি সন্ধানের মান হিসাবে উল্লেখ করতে পারেন। নীচের স্ক্রিনে, আমরা 'সোম*' কে H3 তে প্রবেশ করেছি, যা 'val' নামক একটি পরিসীমা। এর ফলে VLOOKUP 'Monet' নামের সাথে মিলে যায়।

ওয়াইল্ডকার্ড সহ VLOOKUP - সরাসরি একটি তারকা চিহ্ন ব্যবহার করে

এই ক্ষেত্রে সূত্রটি সহজ:

 
= VLOOKUP (val,data,1,0)

যদি আপনি চান, আপনি একটি অন্তর্নির্মিত ওয়াইল্ডকার্ড ব্যবহার করার জন্য VLOOKUP সূত্রটি সামঞ্জস্য করতে পারেন, যেমন নীচের উদাহরণ, যেখানে আমরা কেবল একটি তারকাচিহ্নের সাথে H3 এর মান সংযোজিত করি।

ওয়াইল্ডকার্ড সহ VLOOKUP - তারকাচিহ্নটি সন্ধানমূল্যের সাথে সংযুক্ত

এই ক্ষেত্রে, আমরা তারকাচিহ্নকে VLOOKUP ফাংশনের ভিতরে লুকআপ ভ্যালুতে সংযুক্ত করছি:

 
= VLOOKUP (val&'*',data,1,0)

দ্রষ্টব্য: ওয়াইল্ডকার্ড এবং VLOOKUP এর সাথে সতর্ক থাকুন। তারা আপনাকে একটি 'অলস ম্যাচ' তৈরি করার একটি সহজ উপায় দেয়, কিন্তু তারা ভুল মিল খুঁজে পাওয়াও সহজ করে তোলে।

18. আপনি #N/A ত্রুটি আটকাতে পারেন এবং একটি বন্ধুত্বপূর্ণ বার্তা প্রদর্শন করতে পারেন

সঠিক ম্যাচ মোডে, VLOOKUP #N/A ত্রুটি প্রদর্শন করবে যখন কোন মিল খুঁজে পাওয়া যাবে না। একভাবে, এটি দরকারী কারণ এটি আপনাকে নিশ্চিতভাবে বলে যে লুকআপ টেবিলে কোনও মিল নেই। যাইহোক, #N/A ত্রুটিগুলি দেখতে খুব মজাদার নয়, তাই এই ত্রুটিটিকে ফাঁদে ফেলার এবং এর পরিবর্তে অন্য কিছু প্রদর্শন করার বিভিন্ন উপায় রয়েছে।

একবার আপনি VLOOKUP ব্যবহার করা শুরু করলে, আপনি #N/A ত্রুটির মধ্যে পড়তে বাধ্য হবেন, যা VLOOKUP কোনো মিল খুঁজে পেতে সক্ষম না হলে ঘটে।

আজকের আগে যদি এক্সেল হয়

এটি একটি দরকারী ত্রুটি, কারণ VLOOKUP আপনাকে স্পষ্টভাবে বলছে যে এটি সন্ধানের মান খুঁজে পাচ্ছে না। এই উদাহরণে, 'ল্যাটে' টেবিলে পানীয় হিসেবে বিদ্যমান নেই, তাই VLOOKUP একটি #N/A ত্রুটি ফেলে দেয়

VLOOKUP একটি #N/A ত্রুটি খেলা

এই ক্ষেত্রে সূত্রটি একটি সম্পূর্ণ মানসম্মত সঠিক মিল:

 
= VLOOKUP (E6,data,2,0)

যাইহোক, #N/A ত্রুটিগুলি দেখতে খুব মজাদার নয়, তাই আপনি এই ত্রুটিটি ধরতে এবং আরও বন্ধুত্বপূর্ণ বার্তা প্রদর্শন করতে চাইতে পারেন।

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

এই ত্রুটিটি ফাঁদে ফেলার জন্য এবং ত্রুটির পরিবর্তে একটি 'খুঁজে পাওয়া যায়নি' বার্তা প্রদর্শন করতে, আপনি কেবল IFERROR এর ভিতরে মূল সূত্রটি মোড়ানো এবং আপনার পছন্দসই ফলাফল সেট করতে পারেন:

VLOOKUP #N/A IFERROR এর সাথে একটি ত্রুটি আটকা পড়েছে

যদি সন্ধানের মান পাওয়া যায়, কোন ত্রুটি ঘটে না এবং VLOOKUP ফাংশন একটি স্বাভাবিক ফলাফল প্রদান করে। এখানে সূত্র:

 
= IFERROR ( VLOOKUP (E6,data,2,0),'Not found')

19. পাঠ্য হিসাবে সংখ্যা একটি মিল ত্রুটি হতে পারে

কখনও কখনও, VLOOKUP এ আপনি যে টেবিলে কাজ করছেন তাতে পাঠ্য হিসাবে প্রবেশ করা সংখ্যা থাকতে পারে। যদি আপনি কেবল একটি টেবিলে একটি কলাম থেকে পাঠ্য হিসাবে সংখ্যাগুলি পুনরুদ্ধার করছেন, তাতে কিছু আসে যায় না। কিন্তু যদি টেবিলের প্রথম কলামে পাঠ্য হিসাবে প্রবেশ করা সংখ্যা থাকে, তাহলে আপনি একটি #N/A ত্রুটি পাবেন যদি সন্ধানের মানও পাঠ্য না হয়।

নিম্নলিখিত উদাহরণে, গ্রহ টেবিলের জন্য আইডি সংখ্যা পাঠ্য হিসাবে প্রবেশ করানো হয়েছে , যার কারণে VLOOKUP একটি ত্রুটি ফেরত দেয় কারণ সন্ধানের মান হল সংখ্যা 3:

পাঠ্য VLOOKUP ত্রুটির উদাহরণ হিসেবে প্রবেশ করা সংখ্যা

এই সমস্যা সমাধানের জন্য, আপনাকে নিশ্চিত করতে হবে যে সন্ধানের মান এবং টেবিলের প্রথম কলাম উভয়ই একই ডাটা টাইপ (উভয় সংখ্যা বা উভয় পাঠ্য)।

এটি করার একটি উপায় হল সন্ধান কলামের মানগুলিকে সংখ্যায় রূপান্তর করা। এটি করার একটি সহজ উপায় হল পেস্ট বিশেষ ব্যবহার করে শূন্য যোগ করা।

যদি সোর্স টেবিলের উপর আপনার সহজ নিয়ন্ত্রণ না থাকে, তাহলে আপনি VLOOKUP সূত্রটি সমন্বয় করতে পারেন যাতে সন্ধানের মানকে পাঠ্য রূপে রূপান্তর করতে পারে ''

 
= VLOOKUP (id&'',planets,2,0)

পাঠ্য VLOOKUP ত্রুটি সমাধান হিসাবে প্রবেশ করা নম্বরগুলি

যদি আপনি নিশ্চিত হতে না পারেন যে আপনার কখন সংখ্যা থাকবে এবং কখন আপনার কাছে পাঠ্য থাকবে, আপনি IFERROR- এ VLOOKUP মোড়ানো এবং উভয় ক্ষেত্রে পরিচালনার জন্য একটি সূত্র লিখে উভয় বিকল্প পূরণ করতে পারেন:

 
= IFERROR ( VLOOKUP (id,planets,3,0), VLOOKUP (id&'',planets,3,0))

20. আপনি নেস্টেড IF স্টেটমেন্ট প্রতিস্থাপন করতে VLOOKUP ব্যবহার করতে পারেন

VLOOKUP এর আরও আকর্ষণীয় ব্যবহারগুলির মধ্যে একটি হল নেস্টেড IF স্টেটমেন্ট প্রতিস্থাপন করা। যদি আপনি কখনও নেস্টেড আইএফগুলির একটি সিরিজ তৈরি করেন, আপনি জানেন যে তারা জরিমানা কাজ করে, কিন্তু তাদের জন্য কিছুটা বন্ধনী প্রয়োজন। আপনি যে অর্ডারে কাজ করেন সে সম্পর্কেও আপনাকে সতর্ক থাকতে হবে, যাতে লজিক্যাল ত্রুটি না হয়।

উদাহরণস্বরূপ, নেস্টেড আইএফগুলির একটি সাধারণ ব্যবহার হল কোন ধরণের স্কোরের উপর ভিত্তি করে গ্রেড বরাদ্দ করা। নীচের উদাহরণে, আপনি দেখতে পারেন যে নেস্টেড আইএফগুলির সাথে একটি সূত্র তৈরি করা হয়েছে যা ঠিক করার জন্য, গাইড হিসাবে ডানদিকে গ্রেড কী ব্যবহার করে।

একটি দীর্ঘ নেস্টেড আইএফ সূত্র দিয়ে গ্রেড বরাদ্দ করা

সম্পূর্ণ নেস্টেড আইএফ সূত্রটি এইরকম দেখাচ্ছে:

 
= IF (C5<64,'F', IF (C5<73,'D', IF (C5<85,'C', IF (C5<95,'B','A'))))

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

বিপরীতে VLOOKUP একটি সাধারণ সূত্র দিয়ে একই গ্রেড বরাদ্দ করতে পারে। আপনাকে যা করতে হবে তা নিশ্চিত করতে হবে যে VLOOKUP এর জন্য গ্রেড কী টেবিলটি সেট আপ করা হয়েছে (যেমন এটি সর্বাধিক স্কোর দ্বারা সাজানো হবে, এবং সমস্ত স্কোর পরিচালনা করার জন্য বন্ধনী ধারণ করবে)।

গ্রেড কী টেবিলের জন্য একটি নামযুক্ত পরিসীমা 'কী' সংজ্ঞায়িত করার পরে, VLOOKUP সূত্রটি খুব সহজ এবং মূল নেস্টেড IFs সূত্রের মতো একই গ্রেড তৈরি করে:

একটি সহজ VLOOKUP সূত্র দিয়ে গ্রেড বরাদ্দ করা

'কী' নামে গ্রেড কী টেবিলের সাথে আমাদের একটি খুব সহজ VLOOKUP সূত্র আছে:

 
= VLOOKUP (C5,key,2,TRUE)

এই পদ্ধতির একটি চমৎকার বোনাস হল যে যুক্তি এবং স্কোর উভয়ই গ্রেড কী টেবিলে তৈরি করা হয়েছে। যদি কিছু পরিবর্তন হয়, আপনি কেবল টেবিলটি সরাসরি আপডেট করতে পারেন এবং VLOOKUP সূত্রগুলি স্বয়ংক্রিয়ভাবে আপডেট হবে - কোন সম্পাদনার প্রয়োজন নেই।

ভিডিও: কিভাবে VLOOKUP দিয়ে নেস্টেড আইএফ প্রতিস্থাপন করবেন

21. VLOOKUP শুধুমাত্র একটি মানদণ্ড পরিচালনা করতে পারে

নকশা দ্বারা, VLOOKUP শুধুমাত্র একটি মানদণ্ডের উপর ভিত্তি করে মানগুলি খুঁজে পেতে পারে, যা টেবিলের প্রথম কলামে (লুকআপ কলাম) খুঁজে পেতে একটি সন্ধান মান হিসাবে সরবরাহ করা হয়।

এর অর্থ হল আপনি 'অ্যাকাউন্টিং' এ 'স্মিথ' এর শেষ নাম সহ কোনও কর্মচারীর সন্ধান করার মতো কাজগুলি সহজে করতে পারবেন না, বা পৃথক কলামে প্রথম এবং শেষ নামের ভিত্তিতে কোনও কর্মচারীর সন্ধান করতে পারবেন না।

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

VLOOKUP একাধিক মানদণ্ড সমস্যা - কিভাবে প্রথম এবং শেষ নাম উভয় সন্ধান করবেন?

প্রথমে, একটি সহায়ক কলাম যুক্ত করুন যা কেবল প্রথম এবং শেষ নামগুলি একসাথে সংযুক্ত করে:

VLOOKUP একাধিক মানদণ্ড ধাপ 2 - একটি সহায়ক কলাম যোগ করুন যা একাধিক মানদণ্ডে যোগ দেয়

তারপরে VLOOKUP কনফিগার করুন একটি টেবিল ব্যবহার করতে যা এই নতুন কলামটি অন্তর্ভুক্ত করে এবং সন্ধানের মূল্যের জন্য প্রথম এবং শেষ নামগুলিতে যোগ দিন:

VLOOKUP একাধিক মানদণ্ড ধাপ 3 - অনুসন্ধান মান তৈরি করতে মানদণ্ডে যোগদান করুন

চূড়ান্ত VLOOKUP ফর্মুলা প্রথম এবং শেষ নামগুলি একসাথে কী হিসাবে সাহায্যকারী কলাম ব্যবহার করে দেখায়:

 
= VLOOKUP (C3&D3,data,4,0)

22. দুটি VLOOKUPS একটি VLOOKUP এর চেয়ে দ্রুত

এটি পুরোপুরি পাগল মনে হতে পারে, কিন্তু যখন আপনার কাছে একটি বড় ডেটা থাকে এবং একটি সঠিক মিল করার প্রয়োজন হয়, তখন আপনি সূত্রটিতে আরেকটি VLOOKUP যোগ করে VLOOKUP অনেক গতিতে করতে পারেন!

পটভূমি: কল্পনা করুন যে আপনার কাছে প্রচুর অর্ডার ডেটা রয়েছে, বলুন, 10,000 টিরও বেশি রেকর্ড এবং আপনি অর্ডার আইডির উপর ভিত্তি করে অর্ডার মোট সন্ধান করতে VLOOKUP ব্যবহার করছেন। সুতরাং, আপনি এই মত কিছু ব্যবহার করছেন:

 
= VLOOKUP (order_id,order_data, 5, FALSE)

শেষ পর্যন্ত মিথ্যা VLOOKUP কে একটি সঠিক মিল করতে বাধ্য করে। আপনি একটি সঠিক মিল চান কারণ একটি সুযোগ যে একটি অর্ডার নম্বর পাওয়া যাবে না। এই ক্ষেত্রে, সঠিক ম্যাচ সেটিং VLOOKUP কে #N/A ত্রুটি ফিরিয়ে দেবে।

সমস্যা হল যে সঠিক মিলগুলি সত্যিই ধীর, কারণ এক্সেলকে অবশ্যই সমস্ত মান দিয়ে একটি রৈখিক পদ্ধতিতে এগিয়ে যেতে হবে যতক্ষণ না এটি একটি মিল খুঁজে পায় বা না পায়।

বিপরীতভাবে, আনুমানিক ম্যাচগুলি বিদ্যুৎ দ্রুত হয় কারণ এক্সেল যা করতে বলে তা করতে সক্ষম বাইনারি অনুসন্ধান ।

তবে বাইনারি অনুসন্ধানের সমস্যা (যেমন আনুমানিক মিল মোডে VLOOKUP) হল যে VLOOKUP ভুল ফলাফল ফেরত দিতে পারে যখন কোন মান পাওয়া যায় না। আরও খারাপ, ফলাফলটি সম্পূর্ণ স্বাভাবিক দেখতে পারে, তাই এটি চিহ্নিত করা খুব কঠিন হতে পারে।

সমাধান হল দুইবার VLOOKUP ব্যবহার করা, উভয় সময় আনুমানিক ম্যাচ মোডে। প্রথম উদাহরণটি কেবল পরীক্ষা করে যে মানটি আসলেই বিদ্যমান। যদি তাই হয়, অন্য VLOOKUP চালানো হয় (আবার, আনুমানিক ম্যাচ মোডে) আপনার প্রয়োজনীয় ডেটা আনতে। যদি তা না হয়, তাহলে আপনি যে মান দিতে চান তা ফেরত দিতে পারেন যে ফলাফল পাওয়া যায়নি।

চূড়ান্ত সূত্র এই মত দেখাচ্ছে:

 
= IF ( VLOOKUP (order_id,order_data,1,TRUE)=order_id,  VLOOKUP (order_id,order_data,5,TRUE), 'Missing')

আমি ফাস্টএক্সেলের চার্লস উইলিয়ামসের কাছ থেকে এই পদ্ধতিটি শিখেছি, যার এখানে একটি দুর্দান্ত, বিস্তারিত নিবন্ধ রয়েছে: কেন 2 VLOOKUPS 1 VLOOKUP এর চেয়ে ভালো ।

দ্রষ্টব্য: এই কৌশলটি ব্যবহার করার জন্য আপনার ডেটা সাজানো আবশ্যক। একটি দ্রুত অনুসন্ধান বজায় রাখার সময় এটি একটি অনুপস্থিত সন্ধানের মান থেকে রক্ষা করার একটি সহজ উপায়।

23. INDEX এবং MATCH একসাথে VLOOKUP যা করতে পারে এবং আরও অনেক কিছু করতে পারে

আপনি যদি অনলাইনে এক্সেল অনুসরণ করেন, তাহলে আপনি সম্ভবত এর মধ্যে চলে যাবেন VLOOKUP বনাম INDEX/MATCH বিতর্ক। যুক্তি আশ্চর্যজনকভাবে উত্তপ্ত হতে পারে :)

মূল কথাটি হল: INDEX + MATCH VLOOKUP (এবং HLOOKUP) যা করতে পারে তা করতে পারে, অনেক বেশি নমনীয়তার সাথে, একটু বেশি জটিলতার খরচে। সুতরাং, যারা INDEX + MATCH এর পক্ষে তারা যুক্তি দেখাবে (খুব বুদ্ধিমান) যে আপনি INDEX এবং MATCH শেখা শুরু করতে পারেন, কারণ এটি আপনাকে শেষ পর্যন্ত একটি ভাল টুলসেট দেয়।

INDEX + MATCH এর বিরুদ্ধে যুক্তি হল যে এটির পরিবর্তে দুটি ফাংশন প্রয়োজন, তাই ব্যবহারকারীদের (বিশেষ করে নতুন ব্যবহারকারীদের) শেখা এবং আয়ত্ত করা সহজাতভাবে আরও জটিল।

আমার দুটি সেন্ট হল যে আপনি যদি ঘন ঘন এক্সেল ব্যবহার করেন, তাহলে আপনি কিভাবে ইন্ডেক্স এবং ম্যাচ ব্যবহার করবেন তা শিখতে চান। এটি একটি খুব শক্তিশালী সমন্বয়।

কিন্তু আমি মনে করি আপনার VLOOKUP শেখা উচিত, যা আপনি সর্বত্র চালাবেন, প্রায়শই অন্যদের কাছ থেকে উত্তরাধিকারসূত্রে প্রাপ্ত ওয়ার্কশীটে। সহজবোধ্য পরিস্থিতিতে, VLOOKUP কোন ঝামেলা ছাড়াই কাজটি ঠিকভাবে সম্পন্ন করবে।

INDEX এবং MATCH সম্পর্কে আরো জানতে, এই নিবন্ধটি দেখুন

লেখক ডেভ ব্রুনস


^