برای کسانی از شما که به خوبی در اکسل مسلط^(Excel) هستند، به احتمال زیاد با عملکرد VLOOKUP بسیار آشنا هستید . تابع VLOOKUP برای یافتن یک مقدار در یک سلول متفاوت بر اساس متنی مشابه در همان ردیف استفاده می شود.

اگر هنوز با عملکرد VLOOKUP تازه کار هستید، می توانید پست قبلی من در مورد نحوه استفاده از VLOOKUP در اکسل را^{(how to use VLOOKUP in Excel)} بررسی کنید.

VLOOKUP همانطور که قدرتمند است، محدودیتی در نحوه ساختار جدول مرجع تطبیق برای کارکرد فرمول دارد.

این مقاله محدودیت هایی را که در آن VLOOKUP نمی تواند استفاده شود را به شما نشان می دهد و تابع دیگری به نام INDEX-MATCH را در ^{(INDEX-MATCH)}اکسل^(Excel) معرفی می کند که می تواند مشکل را حل کند.

INDEX MATCH مثال اکسل

با استفاده از مثال زیر صفحه گسترده اکسل ، لیستی از نام مالک خودرو و نام خودرو را داریم. در این مثال، ما سعی خواهیم کرد  شناسه خودرو^{(Car ID)} را بر اساس مدل خودرو^{(Car Model)} که در زیر چند مالک فهرست شده است ، بگیریم.

در یک برگه جداگانه به نام CarType ، ما یک پایگاه داده ماشین ساده با  شناسه^(ID) ، مدل ماشین^{(Car Model)} و رنگ^(Color) داریم .

با تنظیم این جدول،  تابع VLOOKUP^(VLOOKUP) تنها در صورتی می‌تواند کار کند که داده‌هایی که می‌خواهیم بازیابی کنیم در ستون سمت راست آنچه می‌خواهیم مطابقت دهیم ( فیلد Car Model ) قرار داشته باشد.^{(Car Model )}

به عبارت دیگر، با این ساختار جدول، از آنجایی که سعی می کنیم آن را بر اساس مدل خودرو^{(Car Model)} مطابقت دهیم ، تنها اطلاعاتی که می توانیم به دست آوریم رنگ^{(Color )} است (نه ID ، زیرا ستون ID  در سمت چپ  ستون مدل ماشین^{(Car Model )} قرار دارد.)

این به این دلیل است که با VLOOKUP ، مقدار جستجو باید در ستون اول ظاهر شود و ستون های جستجو باید در سمت راست باشند. هیچ یک از این شرایط در مثال ما برآورده نشده است.

خبر خوب این است که 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 را برمی‌گرداند .

توجه: دوبراکت بسته شدن در انتهای این تابع "))" و کاما بین آرگومان ها را به خاطر بسپارید.^{(Note: remember the double closing bracket at the end of this function “))” and the commas between the arguments.)}

من شخصاً از VLOOKUP فاصله گرفته‌ام و اکنون از INDEX-MATCH استفاده می‌کنم زیرا قادر به انجام کارهای بیشتر از VLOOKUP است.

توابع INDEX-MATCH در مقایسه با ^{(INDEX-MATCH)}VLOOKUP مزایای دیگری نیز دارند :

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

هنگامی که ما با مجموعه داده های بزرگی کار می کنیم که در آن محاسبه به دلیل بسیاری از توابع VLOOKUP ممکن است زمان زیادی را ببرد ، متوجه خواهید شد که وقتی همه آن فرمول ها را با INDEX-MATCH جایگزین کنید ، محاسبه کلی سریعتر محاسبه می شود.

1. نیازی به شمارش ستون های نسبی نیست^{(No Need to Count Relative Columns)}

اگر جدول مرجع ما دارای متن کلیدی است که می‌خواهیم در ستون C جستجو کنیم و داده‌هایی که باید دریافت کنیم در ستون AQ باشد، باید بدانیم که در هنگام استفاده از VLOOKUP چند ستون بین ستون C و ستون AQ وجود دارد/شمارش می‌کنیم. .

با توابع  INDEX-MATCH ، می‌توانیم مستقیماً ستون شاخص (یعنی ستون AQ) را که باید داده‌ها را دریافت کنیم و ستونی را که باید مطابقت داده شود (یعنی ستون C) انتخاب کنیم.

1. پیچیده تر به نظر می رسد^{(It Looks More Complicated)}

VLOOKUP امروزه بسیار رایج است، اما تعداد زیادی از استفاده از توابع INDEX-MATCH با هم نمی دانند.

رشته طولانی‌تر در تابع INDEX-MATCH به شما کمک می‌کند در مدیریت توابع پیچیده و پیشرفته ^{(INDEX-MATCH)}اکسل^(Excel) مانند یک متخصص به نظر برسید . لذت بردن!

When to Use Index-Match Instead of VLOOKUP in Excel

For those of yоu who are well-versed in Excel, you are most likely 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 را در فرمول های اکسل مانند VLOOKUP برطرف کنیم

• نحوه حذف خطوط خالی در اکسل

• نحوه استفاده از ویژگی Speak Cells اکسل

• نحوه درج کاربرگ اکسل در Word Doc

• نحوه استفاده از تجزیه و تحلیل What-If اکسل

• 4 روش برای استفاده از علامت چک در اکسل

• نحوه محاسبه امتیاز Z در اکسل

• راهنمای VBA پیشرفته برای MS Excel

• نحوه استفاده از مراجع مطلق در اکسل

• سطرها و ستون ها را در یک کاربرگ اکسل گروه بندی کنید

• نحوه جابجایی ستون ها در اکسل

• نحوه ایجاد چندین لیست کشویی لینک شده در اکسل

• 3 روش برای تقسیم سلول در اکسل

• نحوه استفاده از دستورات If و Nested If در اکسل

• 5 روش برای تبدیل متن به اعداد در اکسل

• 2 روش برای استفاده از تابع Transpose اکسل

• نحوه تعمیر یک ردیف در اکسل

• نحوه ادغام سلول ها، ستون ها و ردیف ها در اکسل

• چرا باید از محدوده های نامگذاری شده در اکسل استفاده کنید؟

• نحوه درج CSV یا TSV در کاربرگ اکسل