آیا تا به حال یک صفحه گسترده بزرگ با داده در اکسل^(Excel) داشته اید و به یک راه آسان برای فیلتر کردن و استخراج اطلاعات خاص از آن نیاز داشته اید؟ اگر یاد بگیرید که چگونه از VLOOKUP در اکسل^(Excel) استفاده کنید ، می توانید این جستجو را تنها با یک تابع قدرتمند اکسل انجام^{(Excel function)} دهید .

تابع VLOOKUP^{(VLOOKUP function)} در اکسل^(Excel) افراد زیادی را می ترساند زیرا پارامترهای زیادی دارد و راه های مختلفی برای استفاده از آن وجود دارد. در این مقاله همه روش‌هایی را که می‌توانید از VLOOKUP در اکسل^(Excel) استفاده کنید و اینکه چرا این تابع بسیار قدرتمند است، خواهید آموخت.

پارامترهای VLOOKUP در اکسل^{(VLOOKUP Parameters In Excel)}

هنگامی که شروع به تایپ =VLOOKUP( در هر سلولی در اکسل^(Excel) می کنید، یک پاپ آپ مشاهده می کنید که تمام پارامترهای تابع موجود را نشان می دهد.

بیایید هر یک از این پارامترها و معنای آنها را بررسی کنیم.

• lookup_value : مقداری که از صفحه گسترده به دنبال آن هستید
• table_array : محدوده سلول های موجود در برگه ای که می خواهید در آن جستجو کنید
• col_index_num : ستونی که می‌خواهید نتیجه خود را از آنجا بگیرید
• [range_lookup] : حالت مطابقت^{(Match mode)} ( TRUE = approximate ، FALSE = exact )

این چهار پارامتر به شما امکان می دهد تا جستجوهای مختلف و مفید زیادی را برای داده ها در مجموعه داده های بسیار بزرگ انجام دهید.

یک مثال VLOOKUP ساده اکسل^{(A Simple VLOOKUP Excel Example)}

VLOOKUP^{(VLOOKUP isn)} یکی از توابع اصلی اکسل^(Excel) نیست که ممکن است یاد گرفته باشید، بنابراین بیایید برای شروع به یک مثال ساده نگاه کنیم.

برای مثال زیر، از یک صفحه گسترده نمرات SAT برای مدارس در ایالات ^(United) متحده^(States) استفاده خواهیم کرد. این صفحه گسترده شامل بیش از 450 مدرسه به همراه نمرات SAT فردی برای خواندن، ریاضی و نوشتن است. با خیال راحت^(Feel) دانلود کنید تا با ما همراه باشید. یک اتصال خارجی وجود دارد که داده ها را می کشد، بنابراین هنگام باز کردن فایل یک هشدار دریافت می کنید، اما ایمن است.

جستجو در چنین مجموعه داده بزرگی برای یافتن مدرسه ای که به آن علاقه دارید بسیار زمان بر خواهد بود.

در عوض، می توانید یک فرم ساده در سلول های خالی کنار جدول ایجاد کنید. برای انجام این جستجو، فقط یک فیلد برای مدرسه^(School) و سه فیلد اضافی برای نمرات خواندن، ریاضی و نوشتن ایجاد کنید.

در مرحله بعد، باید از تابع VLOOKUP^{(VLOOKUP function)} در اکسل^(Excel) استفاده کنید تا این سه فیلد کار کنند. در قسمت Reading تابع VLOOKUP را^{(VLOOKUP function)} به صورت زیر ایجاد کنید :

1. نوع =VLOOKUP(
2. فیلد School را^{(School field)} انتخاب کنید که در این مثال I2 است . یک کاما تایپ کنید
3. کل محدوده سلول هایی را که حاوی داده هایی هستند که می خواهید جستجو کنید انتخاب کنید. یک کاما تایپ کنید

وقتی محدوده را انتخاب می‌کنید، می‌توانید از ستونی که برای جستجو استفاده می‌کنید (در این مورد ستون نام مدرسه^{(school name column)} ) شروع کنید و سپس همه ستون‌ها و ردیف‌های دیگری را که حاوی داده‌ها هستند انتخاب کنید.

توجه^(Note) : تابع VLOOKUP^{(VLOOKUP function)} در اکسل^(Excel) فقط می تواند در سلول های سمت راست ستون جستجو جستجو کند^{(search column)} . در این مثال، ستون نام مدرسه^{(school name column)} باید در سمت چپ داده‌هایی باشد که جستجو می‌کنید.

1. در مرحله بعد، برای بازیابی امتیاز Reading^{(Reading score)} ، باید ستون 3 را از سمت چپ ترین ستون انتخاب شده انتخاب کنید. بنابراین، یک 3 تایپ کنید و سپس یک کامای دیگر را تایپ کنید.
2. در نهایت، FALSE را برای مطابقت دقیق تایپ کنید و تابع را با یک ببندید⁽⁾⁾ .

تابع نهایی VLOOKUP^{(VLOOKUP function)} شما باید چیزی شبیه به این باشد:

=VLOOKUP(I2,B2:G461,3,FALSE)

وقتی برای اولین بار Enter را فشار دهید و عملکرد را تمام کنید، متوجه خواهید شد که فیلد Reading^{(Reading field)} حاوی یک #N/A است.

دلیلش این است که فیلد School^{(School field)} خالی است و چیزی برای یافتن تابع VLOOKUP^{(VLOOKUP function)} وجود ندارد . با این حال، اگر نام دبیرستانی را که می‌خواهید جستجو کنید وارد کنید، نتایج صحیح را از آن ردیف برای امتیاز Reading^{(Reading score)} خواهید دید .

چگونه با حساس بودن به حروف بزرگ VLOOKUP برخورد کنیم^{(How To Deal With VLOOKUP Being Case- Sensitive)}

ممکن است متوجه شوید که اگر نام مدرسه را با همان حالتی که در مجموعه داده فهرست شده است تایپ نکنید، هیچ نتیجه ای نخواهید دید.

این به این دلیل است که تابع VLOOKUP^{(VLOOKUP function)} به حروف کوچک و بزرگ حساس است. این می‌تواند آزاردهنده باشد، به‌ویژه برای مجموعه داده‌های بسیار بزرگ که در آن ستونی که در آن جستجو می‌کنید با نحوه بزرگ‌نویسی اشیا مطابقت ندارد.

برای دور زدن این موضوع، می‌توانید آنچه را که در جستجوی آن هستید مجبور کنید قبل از جستجوی نتایج، به حروف کوچک تبدیل شود. برای انجام این کار، یک ستون جدید در کنار ستون مورد جستجو ایجاد کنید. تابع را تایپ کنید:

=TRIM(LOWER(B2))

با این کار نام مدرسه^{(school name)} کوچک می شود و هر کاراکتر (فضا) اضافی که ممکن است در سمت چپ یا راست نام باشد حذف می شود.

کلید Shift^{(Shift key and place)} را نگه دارید و نشانگر ماوس^{(mouse cursor)} را روی گوشه سمت راست پایین سلول اول قرار دهید تا به دو خط افقی تبدیل شود. برای تکمیل خودکار کل ستون، روی ماوس دوبار کلیک کنید.^(Double)

در نهایت، از آنجایی که VLOOKUP سعی می کند از فرمول به جای متن در این سلول ها استفاده کند، باید همه آنها را فقط به مقادیر تبدیل کنید. برای انجام این کار، کل ستون را کپی کنید، در سلول اول کلیک راست کرده و فقط مقادیر را جایگذاری کنید.

اکنون که تمام داده‌های شما در این ستون جدید پاک می‌شوند، کمی تابع VLOOKUP^{(VLOOKUP function)} خود را در اکسل^(Excel) تغییر دهید تا از این ستون جدید به جای ستون قبلی استفاده کنید و محدوده جستجو را^{(the lookup range)} در C2 به جای B2 شروع کنید.

=VLOOKUP(I2,C2:G461,3,FALSE)

اکنون متوجه خواهید شد که اگر جستجوی خود را همیشه با حروف کوچک تایپ کنید، همیشه نتیجه جستجوی^{(search result)} خوبی خواهید داشت.

این یک نکته مفید اکسل^{(handy Excel tip)} برای غلبه بر این واقعیت است که VLOOKUP به حروف کوچک و بزرگ حساس است.

VLOOKUP مطابقت تقریبی

در حالی که مثال LOOKUP^{(LOOKUP example)} مطابقت دقیق شرح داده شده در بخش اول این مقاله بسیار ساده است، مطابقت تقریبی کمی پیچیده تر است.

از تطابق تقریبی بهتر است برای جستجو در محدوده اعداد استفاده شود. برای انجام صحیح این کار، محدوده جستجو^{(search range)} باید به درستی مرتب شود. بهترین مثال برای این تابع VLOOKUP^{(VLOOKUP function)} برای جستجوی درجه حروفی است که با ^{(letter grade)}درجه عددی^{(number grade)} مطابقت دارد .

اگر معلمی فهرست بلندبالایی از نمرات تکالیف دانش‌آموز^{(student homework)} در طول سال با ستون میانگین^{(averaged column)} نهایی داشته باشد، خوب است که نمره حرف مربوط به آن نمره نهایی به‌طور خودکار نمایش داده شود.

این کار با تابع VLOOKUP^{(VLOOKUP function)} امکان پذیر است . تنها چیزی که لازم است یک جدول جستجو^{(lookup table)} در سمت راست است که شامل درجه حروف مناسب برای هر ^{(letter grade)}محدوده امتیاز^{(score range)} عددی است.

اکنون، با استفاده از تابع VLOOKUP^{(VLOOKUP function)} و یک تطابق تقریبی، می توانید درجه حروف مناسب مربوط به محدوده عددی صحیح را پیدا کنید.

در این تابع VLOOKUP:

• lookup_value : F2، نمره میانگین نهایی
• table_array : I2:J8، محدوده جستجوی درجه حروف^{(letter grade lookup range)}
• index_column : 2، دومین ستون در جدول جستجو^{(lookup table)}
• [range_lookup] : درست، مطابقت تقریبی

هنگامی که عملکرد VLOOKUP را^{(VLOOKUP function)} در G2 به پایان رساندید و Enter را فشار^{(G2 and press Enter)} دادید، می‌توانید بقیه سلول‌ها را با استفاده از همان رویکردی که در بخش آخر توضیح داده شد، پر کنید. می بینید که تمام نمرات حروف به درستی پر شده اند.

توجه داشته باشید که تابع VLOOKUP^{(VLOOKUP function)} در اکسل^(Excel) از انتهای پایین محدوده نمره با ^{(grade range)}نمره حرف^{(letter score)} اختصاص داده شده تا بالای محدوده امتیاز حرف^{(letter score)} بعدی را جستجو می کند.

بنابراین، "C" باید حرف اختصاص داده شده به محدوده پایین (75) باشد، و B به پایین (حداقل) محدوده حروف^{(letter range)} خودش اختصاص داده می شود. VLOOKUP نتیجه 60 (D) را به عنوان نزدیکترین مقدار تقریبی برای هر چیزی بین 60 تا 75 "پیدا می کند".

VLOOKUP در اکسل^(Excel) یک تابع بسیار قدرتمند است که برای مدت طولانی در دسترس بوده است. همچنین برای یافتن مقادیر منطبق در هر جایی از کتاب کار اکسل^{(Excel workbook)} مفید است.

با این حال، به خاطر داشته باشید که کاربران مایکروسافت^(Microsoft) که اشتراک ماهانه Office 365 دارند، اکنون به عملکرد جدیدتر XLOOKUP دسترسی دارند. این تابع دارای پارامترهای بیشتر و انعطاف پذیری اضافی است. کاربرانی که اشتراک شش ماهه دارند باید منتظر بمانند تا در جولای 2020^{(July 2020)} عرضه شود.

How to Use VLOOKUP in Excel

Have you ever had a large spreadsheet with data in Exсel and need an easy waу to filter and extract specific information from it? If you learn how to use VLOOKUP in Excel, you can do thіs lookup with just а single, powerful Excel functiоn.

The VLOOKUP function in Excel scares a lot of people because it has a lot of parameters and there are multiple ways to use it. In this article you’ll learn all of the ways you can use VLOOKUP in Excel and why the function is so powerful.

VLOOKUP Parameters In Excel

When you start typing =VLOOKUP( into any cell in Excel, you’ll see a pop-up showing all of the available function parameters.

Let’s examine each of these parameters and what they mean.

• lookup_value: The value you’re looking for from the spreadsheet
• table_array: The range of cells in the sheet you want to search through
• col_index_num: The column where you want to pull your result from
• [range_lookup]: Match mode (TRUE = approximate, FALSE = exact)

These four parameters let you do a lot of different, useful searches for data inside of very large datasets.

A Simple VLOOKUP Excel Example

VLOOKUP isn’t one of the basic Excel functions you might have learned, so let’s look at a simple example to get started.

For the following example, we’ll use a large spreadsheet of SAT scores for schools in the United States. This spreadsheet contains over 450 schools along with individual SAT scores for reading, math, and writing. Feel free to download to follow along. There is an external connection that pulls the data, so you’ll get a warning when opening the file, but it’s safe.

It would be very time consuming to search through such a large dataset to find the school that you’re interested in.

Instead, you can create a simple form in the blank cells on the side of the table. To conduct this search, just make one field for School, and three additional fields for reading, math, and writing scores.

Next, you’ll need to use the VLOOKUP function in Excel to make these three fields work. In the Reading field, create the VLOOKUP function as follows:

1. Type =VLOOKUP(
2. Select the School field, which in this example is I2. Type a comma.
3. Select the entire range of cells that contain the data you want to look up. Type a comma.

When you select the range, you can start from the column you’re using to look up (in this case the school name column), and then select all of the other columns and rows that contain the data.

Note: The VLOOKUP function in Excel can only search through cells to the right of the search column. In this example, the school name column needs to be to the left of the data you’re looking up.

1. Next, to retrieve the Reading score, you’ll need to select the 3rd column from the leftmost selected column. So, type a 3 and then type another comma.
2. Finally, type FALSE for an exact match, and close the function with a ).

Your final VLOOKUP function should look something like this:

=VLOOKUP(I2,B2:G461,3,FALSE)

When you first press Enter and finish the function, you’ll notice the Reading field will contain an #N/A.

This is because the School field is blank and there is nothing for the VLOOKUP function to find. However, if you enter the name of any high school you want to look up, you’ll see the correct results from that row for the Reading score.

How To Deal With VLOOKUP Being Case- Sensitive

You may notice that if you don’t type the name of the school in the same case as how it’s listed in the dataset, you will not see any results.

This is because the VLOOKUP function is case sensitive. This can be annoying, especially for a very large dataset where the column you’re searching through is inconsistent with how things are capitalized.

To get around this, you can force what you’re searching for to switch to lowercase before looking up the results. To do this, create a new column next to the column you’re searching. Type the function:

=TRIM(LOWER(B2))

This will lowercase the school name and remove any extraneous characters (spaces) that might be on the left or right side of the name.

Hold down the Shift key and place the mouse cursor over the lower right corner of the first cell until it changes to two horizontal lines. Double click the mouse to autofill the entire column.

Finally, since VLOOKUP will try to use the formula rather than the text in these cells, you need to convert them all to values only.  To do this, copy the entire column, right click in the first cell, and paste values only.

Now that all of your data is cleaned up in this new column, slightly modify your VLOOKUP function in Excel to use this new column instead of the previous one by starting the lookup range at C2 instead of B2.

=VLOOKUP(I2,C2:G461,3,FALSE)

Now you’ll notice that if you always type your search in lower case, you’ll always get a good search result.

This is a handy Excel tip to overcome the fact that VLOOKUP is case sensitive.

VLOOKUP Approximate Match

While the exact match LOOKUP example described in the first section of this article is pretty straightforward, the approximate match is a little more complex.

The approximate match is best used to search through number ranges. To do this correctly the search range needs to be properly sorted. The best example of this is a VLOOKUP function to search for a letter grade that corresponds to a number grade.

If a teacher has a long list of student homework grades from throughout the year with a final averaged column, it would be nice to have the letter grade corresponding to that final grade come up automatically.

This is possible with the VLOOKUP function. All that’s required is a lookup table off to the right that contains the appropriate letter grade for each numerical score range.

Now, using the VLOOKUP function and an approximate match, you can find the proper letter grade corresponding to the correct numeric range.

In this VLOOKUP function:

• lookup_value: F2, the final averaged grade
• table_array: I2:J8, The letter grade lookup range
• index_column: 2, the second column in the lookup table
• [range_lookup]: TRUE, approximate match

Once you finish the VLOOKUP function in G2 and press Enter, you can fill in the rest of the cells using the same approach described in the last section. You’ll see all of the letter grades properly filled in.

Note that the VLOOKUP function in Excel searches from the bottom end of the grade range with the assigned letter score to the top of the range of the next letter score.

So, “C” needs to be the letter assigned to the lower range (75), and B is assigned to the bottom (minimum) of its own letter range. VLOOKUP will “find” the result for 60 (D) as the closest approximate value for anything between 60 to 75.

VLOOKUP in Excel is a very powerful function that has been available for a long time. It is also useful for finding matching values anywhere in an Excel workbook.

Keep in mind, however, that Microsoft users who have a monthly Office 365 subscription now have access to a newer XLOOKUP function. This function has more parameters and additional flexibility. Users with a semi-annual subscription will need to wait for the update to roll out in July 2020.

Related posts

• اضافه کردن Regression Trendline خطی به Excel Scatter Plot

• چگونه می توان یک Histogram در Excel

• چگونه به صورت امن Password Protect Excel File

• چگونه به ایجاد یک Flowchart در Word and Excel

• چگونه Create Labels در Word از Excel Spreadsheet

• نحوه یافتن مقادیر منطبق در اکسل

• نحوه حذف، شکستن یا شکستن رمز عبور فراموش شده اکسل XLS

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

• 40 بهترین میانبر صفحه کلید مایکروسافت اکسل

• از عملکرد جدید Excel Mobile "Insert Data From Picture" استفاده کنید

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

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

• نحوه استفاده از COUNTIFS، SUMIFS، AVERAGEIFS در اکسل

• راهنمای همه پسوندهای فایل اکسل و معنای آنها

• چگونه به Group Worksheets در Excel

• نحوه اضافه کردن و چاپ تصاویر پس زمینه اکسل

• از توابع خلاصه برای خلاصه کردن داده ها در اکسل استفاده کنید

• از اکسل به عنوان ابزاری برای کپی داده ها از وب استفاده کنید

• نحوه اضافه کردن نظرات به سلول کاربرگ اکسل

• چگونه برای ایجاد Distribution List در Outlook