بالنسبة لأولئك الذين هم على دراية جيدة ببرنامج Excel ، فمن المرجح أن تكون على دراية كبيرة بوظيفة VLOOKUP . تُستخدم الدالة VLOOKUP للعثور على قيمة في خلية مختلفة بناءً على بعض النصوص المطابقة داخل نفس الصف.

إذا كنت لا تزال جديدًا على وظيفة VLOOKUP ، فيمكنك التحقق من مشاركتي السابقة حول كيفية استخدام VLOOKUP في Excel^{(how to use VLOOKUP in Excel)} .

على الرغم من قوتها ، فإن VLOOKUP لديها قيود على كيفية تنظيم الجدول المرجعي المطابق حتى تعمل الصيغة.

ستوضح لك هذه المقالة القيد حيث لا يمكن استخدام VLOOKUP وتقديم وظيفة أخرى في ^(VLOOKUP)Excel تسمى INDEX-MATCH يمكنها حل المشكلة.

مثال على مطابقة الفهرس في Excel

باستخدام جدول بيانات Excel التالي ، لدينا قائمة بأسماء مالكي السيارات واسم السيارة. في هذا المثال ، سنحاول الحصول على  معرف السيارة^{(Car ID)} استنادًا إلى طراز السيارة^{(Car Model)} المُدرج ضمن العديد من المالكين كما هو موضح أدناه:

في ورقة منفصلة تسمى CarType ، لدينا قاعدة بيانات بسيطة للسيارة تحتوي على  المعرف ^(ID)وطراز ^(Color)السيارة^{(Car Model)} واللون .

باستخدام إعداد الجدول هذا ،  لا يمكن أن تعمل وظيفة VLOOKUP إلا إذا كانت البيانات التي نريد استردادها موجودة في العمود الموجود على يمين ما نحاول مطابقته ( حقل طراز السيارة ).^{(Car Model )}

بمعنى آخر ، باستخدام بنية الجدول هذه ، نظرًا لأننا نحاول مطابقتها استنادًا إلى طراز السيارة^{(Car Model)} ، فإن المعلومات الوحيدة التي يمكننا الحصول عليها هي اللون^{(Color )} (ليس المعرف^(ID) حيث يوجد عمود المعرف^(ID)  على يسار  عمود طراز السيارة^{(Car Model )} .)

هذا لأنه مع VLOOKUP ، يجب أن تظهر قيمة البحث في العمود الأول ويجب أن تكون أعمدة البحث على اليمين. لم يتم استيفاء أي من هذه الشروط في مثالنا.

والخبر السار هو أن INDEX-MATCH^{(INDEX-MATCH)}  سيكون قادرًا على مساعدتنا في تحقيق ذلك. في الممارسة العملية ، هذا في الواقع يجمع بين وظيفتين من وظائف Excel التي يمكن أن تعمل بشكل فردي: دالة INDEX ودالة MATCH .

ومع ذلك ، لغرض هذه المقالة ، سنتحدث فقط عن الجمع بين الاثنين بهدف تكرار وظيفة VLOOKUP .

قد تبدو الصيغة طويلة بعض الشيء ومخيفة في البداية. ومع ذلك ، بمجرد استخدامه عدة مرات ، سوف تتعلم النحو عن ظهر قلب.

هذه هي الصيغة الكاملة في مثالنا:

=INDEX(CarType!$A$2:$A$5,MATCH(B4,CarType!$B$2:$B$5,0))

هنا هو التفصيل لكل قسم

=INDEX( - يشير “=” إلى بداية الصيغة في الخلية و INDEX هو الجزء الأول من وظيفة Excel التي نستخدمها.

CarType!$A$2:$A$5 - الأعمدة الموجودة على الورقة CarType  حيث يتم احتواء البيانات التي نريد استردادها. في هذا المثال ، معرّف^(ID)  كل طراز سيارة.^{(Car Model.)}

MATCH ( - الجزء الثاني من وظيفة Excel التي نستخدمها.

B4 - الخلية التي تحتوي على نص البحث الذي نستخدمه ( طراز السيارة^{(Car Model)} ) .

CarType!$B$2:$B$5 - الأعمدة الموجودة على ورقة CarType   مع البيانات التي سنستخدمها للمطابقة مع نص البحث.

0)) - للإشارة إلى أن نص البحث يجب أن يتطابق تمامًا مع النص الموجود في العمود المطابق ( CarType!$B$2:$B$5 ). إذا لم يتم العثور على المطابقة التامة ، فتُرجع الصيغة #N/A A.

ملاحظة: تذكر قوس الإغلاق المزدوج في نهاية هذه الوظيفة "))" والفواصل بين الوسيطات.^{(Note: remember the double closing bracket at the end of this function “))” and the commas between the arguments.)}

أنا شخصياً ابتعدت عن VLOOKUP وأستخدم^(VLOOKUP) الآن INDEX-MATCH لأنه قادر على فعل أكثر من VLOOKUP .

وظائف INDEX-MATCH لها أيضًا مزايا أخرى مقارنة بـ VLOOKUP :

1. حسابات أسرع^{(Faster Calculations)}

عندما نعمل مع مجموعات بيانات كبيرة حيث يمكن أن تستغرق العملية الحسابية نفسها وقتًا طويلاً بسبب العديد من وظائف VLOOKUP ، ستجد أنه بمجرد استبدال كل هذه الصيغ بـ INDEX-MATCH ، سيتم حساب الحساب الكلي بشكل أسرع.

1. لا حاجة لحساب الأعمدة النسبية^{(No Need to Count Relative Columns)}

إذا كان جدولنا المرجعي يحتوي على النص الأساسي الذي نريد البحث عنه في العمود C وكانت البيانات التي نحتاج إلى الحصول عليها موجودة في العمود AQ ، فسنحتاج إلى معرفة / حساب عدد الأعمدة بين العمود C والعمود AQ عند استخدام VLOOKUP .

باستخدام  وظائف INDEX-MATCH ، يمكننا تحديد عمود الفهرس مباشرةً (أي العمود AQ) حيث نحتاج إلى الحصول على البيانات وتحديد العمود المراد مطابقته (أي العمود C).

1. يبدو أكثر تعقيدًا^{(It Looks More Complicated)}

يعد VLOOKUP^(VLOOKUP) شائعًا جدًا في الوقت الحاضر ، ولكن لا يعرف الكثير عن استخدام وظائف INDEX-MATCH معًا.

تساعد السلسلة الأطول في وظيفة INDEX-MATCH في جعلك تبدو كخبير في التعامل مع وظائف Excel المعقدة والمتقدمة. ^(Excel)يتمتع!

When to Use Index-Match Instead of VLOOKUP in Excel

Fоr those of you who are well-versеd in Excеl, you are moѕt lіkely verу familiar with the VLOOKUP function. The VLOOKUP function is used to find a value in a different cell based on some matching text within the same row.

If you are still new to VLOOKUP function, you can check out my previous post on how to use VLOOKUP in Excel.

As powerful as it is, VLOOKUP has a limitation on how the matching reference table needs to be structured in order for the formula to work.

This article will show you the limitation where VLOOKUP cannot be used and introduce another function in Excel called INDEX-MATCH that can solve the issue.

INDEX MATCH Excel Example

Using the following example Excel spreadsheet, we have a list of car owners name and the car name. In this example, we will be trying to grab the Car ID based on the Car Model listed under multiple owners as shown below:

On a separate sheet called CarType, we have a simple car database with the ID, Car Model and Color.

With this table setup, the VLOOKUP function can only work if the data that we want to retrieve is located on the column to the right of what we are trying to match (Car Model field).

In other words, with this table structure, since we are trying to match it based on the Car Model, the only information that we can get is Color (Not ID as the ID column is located to the left of the Car Model column.)

This is because with VLOOKUP, the lookup value must appear in the first column and the lookup columns have to be to the right. None of those conditions are met in our example.

The good news is, INDEX-MATCH will be able to help us in achieving this. In practice, this is actually combining two Excel functions that can work individually: INDEX function and MATCH function.

However, for the purpose of this article, we will only talk about the combination of the two with the aim of replicating the function of VLOOKUP.

The formula can seem to be a little bit long and intimidating at first. However, once you have used it several times, you will learn the syntax by heart.

This is the full formula in our example:

=INDEX(CarType!$A$2:$A$5,MATCH(B4,CarType!$B$2:$B$5,0))

Here is the breakdown for each section

=INDEX( – The “=” indicates the beginning of formula in the cell and INDEX is the first part of the Excel function that we are using.

CarType!$A$2:$A$5 – the columns on sheet CarType where the data we would like to retrieve is contained. In this example, the ID of each Car Model.

MATCH( – The second part of the Excel function that we are using.

B4 – The cell that contain search text that we are using (Car Model).

CarType!$B$2:$B$5 – The columns on sheet CarType  with the data which we will use to match against the search text.

0)) – To indicate that the search text has to exactly match with the text in the matching column (i.e. CarType!$B$2:$B$5). If the exact match is not found, the formula returns #N/A.

Note: remember the double closing bracket at the end of this function “))” and the commas between the arguments.

Personally I have moved away from VLOOKUP and now use INDEX-MATCH as it is capable of doing more than VLOOKUP.

The INDEX-MATCH functions also have other benefits as compared to VLOOKUP:

1. Faster Calculations

When we are working with large datasets where the calculation itself can take a long time due to many VLOOKUP functions, you will find that once you replace all of those formulas with INDEX-MATCH, the overall calculation will be compute faster.

1. No Need to Count Relative Columns

If our reference table is having the key text that we want to search in column C and the data that we need to get is in column AQ, we will need to know/count how many columns are between column C and column AQ when using VLOOKUP.

With the INDEX-MATCH functions, we can directly select the index column (i.e column AQ) where we need to get the data and select the column to be matched (i.e. column C).

1. It Looks More Complicated

VLOOKUP is quite common nowadays, but not many know about using the INDEX-MATCH functions together.

The longer string in INDEX-MATCH function help to make you look like an expert in handling complex and advanced Excel functions. Enjoy!

Related posts

• كيفية إصلاح أخطاء # N / A في صيغ Excel مثل VLOOKUP

• كيفية حذف الأسطر الفارغة في Excel

• كيفية استخدام ميزة Speak Cells في Excel

• كيفية إدراج ورقة عمل Excel في مستند Word

• كيفية استخدام تحليل ماذا لو في Excel

• كيفية تحريك الأعمدة في Excel

• مركز بيانات ورقة العمل الخاصة بك في Excel للطباعة

• كيفية تتبع المعالين في Excel

• كيفية إدراج CSV أو TSV في ورقة عمل Excel

• كيفية مشاركة ملف Excel لسهولة التعاون

• كيفية البحث في Excel

• جداول بيانات Google مقابل Microsoft Excel - ما هي الاختلافات؟

• تهجئة الأرقام بسرعة في Word و Excel

• كيفية إنشاء VBA ماكرو أو برنامج نصي في Excel

• الاختلافات بين Microsoft Excel Online و Excel لسطح المكتب

• كيفية استخدام Flash Fill في Excel

• كيفية فتح Word و Excel في الوضع الآمن

• استخدم لوحة المفاتيح لتغيير ارتفاع الصف وعرض العمود في Excel

• 4 طرق لاستخدام علامة الاختيار في Excel

• استخدام أداة البحث عن هدف تحليل ماذا لو في Excel