آموزش اکسل - نحوه استفاده از تابع VLOOKUP

این تابع (VERTICAL LOOKUP) از پرکاربردترین توابع اکسل بوده که یک جستجوی عمودی انجام داده و مقدار مبتنی بر یک انطباق و یا مقدار دقیق را بر می گرداند.
در ادامه با مثالی به توضیح این تابع می پردازیم:
یکی از کاربرد های وی لوک آپ انطباق دو لیست می باشد، فرض کنید دو لیست داریم (لیست1 و لیست2) و می خواهیم مقدار متناظر یک مقدار را از لیست2 پیدا کرده و به لیست1 اضافه کنیم، یعنی نام کاربری و کلمه عبور هر کاربر را از لیست 2 پیدا نموده و در ستون مربوطه در لیست1 وارد نماییم،
پس بصورت زیر عمل می کنیم:
1- ابتدا به لیست2 رفته و با انتخاب کل سلولها کلیدهای ترکیبی ctrl+t را زده تا ناحیه انتخابی به جدول تبدیل شود.
2-در لیست 1 سلول E2 را انتخاب کرده و در Insert Function به بخش Lookup & Refrence رفته و VLOOKUP را انتخاب می کنیم.(تصویر2)

3- توضیحات مربوط به این تابع:
Lookup_Value: سلول A2 که می خواهیم متناظر آن را در لیست2 پیدا کنیم را انتخاب می کنیم.
Table_array: کل محدوده ای که می بایست به دنبال مقدار متناظر جستجو شود، یعنی جدول موجود در لیست2
Col_index_num: شماره ستونی که می بایست مقدار پیداشده در لیست2 برگردانده شود. برای user این شماره2 و برای pass شماره 3 می باشد، البته برای لی اوت راست به چپ می بایست متناسب آن وارد گردد.
Range_lookup: برای این گزینه دو انتخاب داریم True & False. برای پیدا کردن مقدار دقیق از False و برای پیدا کردن مقدار نزدیک از True استفاده می کنیم. در اینجا ما نیاز به مقدار دقیق داریم پس False و یا عدد صفر را وارد می کنیم.(تصویر3)

4- فرمول این سلول را به دیگر سلولهای این ستون بسط می دهیم.
5- برای ستون Pass نیز همین مراحل را تکرار می کنیم با تفاوت اینکه در قسمت  Col_index_num عدد 3 را وارد می کنیم.(تصویر4)

توضیح بیشتر :

به طور کلی توابع Lookup جزو توابع بسیار مفید و پرکاربرد در تمام نگارشهای برنامه اکسل هستند. در این آموزش کار تابع vlookup از این خانواده را شرح خواهم داد که با یادگیری آن استفاده از سایر توابع lookup نیز بسیار آسان خواهد بود.

ابتدا برای درک کار این تابع، به شکل زیر دقت کنید. در این شکل یک لیست فرضی از اطلاعات دانشجویان چند دوره مختلف را داریم. تعداد زیاد دانشجویان و نمرات ثبت شده در این لیست در آینده می تواند جستجو برای یافتن نمرات یک دانشجوی خاص را با مشکل همراه کند.

حال به شکل زیر دقت کنید. در این شکل یک فرم طراحی شده که در آن با وارد کردن کد دانشجویی می توان سایر اطلاعات مورد نظر از یک دانشجوی خاص را به دست آورد. در این فرم از تابع vlookup استفاده شده است.

کار تابع vlookup چیست؟
از این تابع برای جستجوی ستون اول از یک گروه ستون برای یافتن یک مقدار مشخص و سپس یافتن مقادیر هر کدام از سلولهای مرتبط با آن استفاده می شود.

حرف V در ابتدای این تابع، معنای عمودی یا vertical دارد. با این معنا که برای استفاده از این تابع مادیر مورد جستجو باید در اولین ستون محدوده قرار گرفته باشند بر خلاف دیگر هم خانواده این تابع یعنی تابع HLOOKUP که در آن مقادیر مورد جستجو در اولین سطر محدوده قرار می گیرند.

نحوه استفاده از تابع:

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

آرگومانهای تابع Vlookup عبارتند از:

lookup_value:

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

در صورتی که این مقدار در جدول اصلی یافت نشود تابع #N/A را به عنوان پیغام خطا بازگشت می دهد.

table_array:

محدوده سلولهایی است که شامل اطلاعات مورد جستجوی ما هستند. در این مثال، table_array شامل سلولهای A1:F5 است. البته به جای استفاده از نام سلولها می توان از نام محدوده هم استفاده کرد.  در این صورت قبل از استفاده باید محدوده را نامگذاری کرد.

col_index_num:

شماره ستون مورد نظر در table_array که می خواهیم آن را برگردانیم. مثلا برای بازگردانی نام خانوادگی یک دانشجو در تابع نوشته شده برای آن عدد 3 را باید برای col_index_num مشخص کرد.

نکته: اگر col_index_num را کمتر از 1 در نظر بگیریم پیغام خطای #VALUE! و در صورتی که عددی بزرگتر از تعداد ستونهای اطلاعات جدول اصلی در نظر بگیریم، (برای این مثال عددی مانند 10)  پیغام خطای #REF! ظاهر خواهد شد.

range_lookup:

در صورت وارد کردن عبارت TRUE، اگر lookup_value مورد نظر در جدول داده ها یافت نشود، نزدیکترین مقدار به آن به عنوان جواب برگردانده می شود. ولی در صورتیکه عبارت FALSE را وارد کنیم فقط خود مقدار مورد نظر باید یافته شود و در صورت پیدا نکردن، تابع #N/A را بر خواهد گرداند.

توضیح انگلیسی :

Using VLOOKUP in Excel

VLOOKUP is one of Excel’s most useful functions, and it’s also one of the least understood.  In this article, we demystify VLOOKUP by way of a real-life example.  We’ll create a usable Invoice Template for a fictitious company.

So what is VLOOKUP?  Well, of course it’s an Excel function.  This article will assume that the reader already has a passing understanding of Excel functions, and can use basic functions such as SUM, AVERAGE, and TODAY.  In its most common usage, VLOOKUP is a database function, meaning that it works with database tables – or more simply, lists of things in an Excel worksheet.  What sort of things?   Well, any sort of thing.  You may have a worksheet that contains a list of employees, or products, or customers, or CDs in your CD collection, or stars in the night sky.  It doesn’t really matter.

Here’s an example of a list, or database.  In this case it’s a list of products that our fictitious company sells:

Usually lists like this have some sort of unique identifier for each item in the list.  In this case, the unique identifier is in the “Item Code” column.  Note:  For the VLOOKUP function to work with a database/list, that list must have a column containing the unique identifier (or “key”, or “ID”), and that column must be the first column in the table.  Our sample database above satisfies this criterion.

The hardest part of using VLOOKUP is understanding exactly what it’s for.  So let’s see if we can get that clear first:

VLOOKUP retrieves information from a database/list based on a supplied instance of the unique identifier.

Put another way, if you put the VLOOKUP function into a cell and pass it one of the unique identifiers from your database, it will return you one of the pieces of information associated with that unique identifier.  In the example above, you would pass VLOOKUP an item code, and it would return to you either the corresponding item’s description, its price, or its availability (its “In stock” quantity).  Which of these pieces of information will it pass you back?  Well, you get to decide this when you’re creating the formula.

If all you need is one piece of information from the database, it would be a lot of trouble to go to to construct a formula with a VLOOKUP function in it.  Typically you would use this sort of functionality in a reusable spreadsheet, such as a template.  Each time someone enters a valid item code, the system would retrieve all the necessary information about the corresponding item.

Let’s create an example of this:  An Invoice Template that we can reuse over and over in our fictitious company.

First we start Excel…

…and we create ourselves a blank invoice:

This is how it’s going to work:  The person using the invoice template will fill in a series of item codes in column “A”, and the system will retrieve each item’s description and price, which will be used to calculate the line total for each item (assuming we enter a valid quantity).

For the purposes of keeping this example simple, we will locate the product database on a separate sheet in the same workbook:

In reality, it’s more likely that the product database would be located in a separate workbook.  It makes little difference to the VLOOKUP function, which doesn’t really care if the database is located on the same sheet, a different sheet, or a completely different workbook.

In order to test the VLOOKUP formula we’re about to write, we first enter a valid item code into cell A11:

Next, we move the active cell to the cell in which we want information retrieved from the database by VLOOKUP to be stored.  Interestingly, this is the step that most people get wrong.  To explain further:  We are about to create a VLOOKUP formula that will retrieve the description that corresponds to the item code in cell A11.  Where do we want this description put when we get it?  In cell B11, of course.  So that’s where we write the VLOOKUP formula – in cell B11.

Select cell B11:

We need to locate the list of all available functions that Excel has to offer, so that we can choose VLOOKUP and get some assistance in completing the formula.  This is found by first clicking the Formulas tab, and then clicking Insert Function:

A box appears that allows us to select any of the functions available in Excel.  To find the one we’re looking for, we could type a search term like “lookup” (because the function we’re interested in is a lookup function).  The system would return us a list of all lookup-related functions in Excel.  VLOOKUP is the second one in the list.  Select it an click OK…

The Function Arguments box appears, prompting us for all the arguments (or parameters) needed in order to complete the VLOOKUP function.  You can think of this box as the function is asking us the following questions:

1. What unique identifier are you looking up in the database?
2. Where is the database?
3. Which piece of information from the database, associated with the unique identifier, do you wish to have retrieved for you?

The first three arguments are shown in bold, indicating that they are mandatory arguments (the VLOOKUP function is incomplete without them and will not return a valid value).  The fourth argument is not bold, meaning that it’s optional:

We will complete the arguments in order, top to bottom.

The first argument we need to complete is the Lookup_value argument.  The function needs us to tell it where to find the unique identifier (the item code in this case) that it should be retuning the description of.  We must select the item code we entered earlier (in A11).

Click on the selector icon to the right of the first argument:

Then click once on the cell containing the item code (A11), and press Enter:

The value of “A11” is inserted into the first argument.

Now we need to enter a value for the Table_array argument.  In other words, we need to tell VLOOKUP where to find the database/list.  Click on the selector icon next to the second argument:

Now locate the database/list and select the entire list – not including the header line.  The database is located on a separate worksheet, so we first click on that worksheet tab:

Next we select the entire database, not including the header line:

…and press Enter.  The range of cells that represents the database (in this case “’Product Database’!A2:D7”) is entered automatically for us into the second argument.

Now we need to enter the third argument, Col_index_num.  We use this argument to specify to VLOOKUP which piece of information from the database, associate with our item code in A11, we wish to have returned to us.  In this particular example, we wish to have the item’s description returned to us.  If you look on the database worksheet, you’ll notice that the “Description” column is the second column in the database.  This means that we must enter a value of “2” into the Col_index_num box:

It is important to note that that we are not entering a “2” here because the “Description” column is in the B column on that worksheet.  If the database happened to start in column K of the worksheet, we would still enter a “2” in this field.

Finally, we need to decide whether to enter a value into the final VLOOKUP argument, Range_lookup.  This argument requires either a true or false value, or it should be left blank.  When using VLOOKUP with databases (as is true 90% of the time), then the way to decide what to put in this argument can be thought of as follows:

If the first column of the database (the column that contains the unique identifiers) is sorted alphabetically/numerically in ascending order, then it’s possible to enter a value of true into this argument, or leave it blank.

If the first column of the database is not sorted, or it’s sorted in descending order, then you must enter a value of false into this argument

As the first column of our database is not sorted, we enter false into this argument:

That’s it!  We’ve entered all the information required for VLOOKUP to return the value we need.  Click the OK button and notice that the description corresponding to item code “R99245” has been correctly entered into cell B11:

The formula that was created for us looks like this:

If we enter a different item code into cell A11, we will begin to see the power of the VLOOKUP function:  The description cell changes to match the new item code:

We can perform a similar set of steps to get the item’s price returned into cell E11.  Note that the new formula must be created in cell E11.  The result will look like this:

…and the formula will look like this:

Note that the only difference between the two formulae is the third argument (Col_index_num) has changed from a “2” to a “3” (because we want data retrieved from the 3rd column in the database).

If we decided to buy 2 of these items, we would enter a “2” into cell D11.  We would then enter a simple formula into cell F11 to get the line total:

=D11*E11

…which looks like this…

Completing the Invoice Template

We’ve learned a lot about VLOOKUP so far.  In fact, we’ve learned all we’re going to learn in this article.  It’s important to note that VLOOKUP can be used in other circumstances besides databases.  This is less common, and may be covered in future How-To Geek articles.

Our invoice template is not yet complete.  In order to complete it, we would do the following:

1. We would remove the sample item code from cell A11 and the “2” from cell D11.  This will cause our newly created VLOOKUP formulae to display error messages:
   
   We can remedy this by judicious use of Excel’s IF() and ISBLANK() functions.  We change our formula from this…      =VLOOKUP(A11,’Product Database’!A2:D7,2,FALSE)…to this…=IF(ISBLANK(A11),””,VLOOKUP(A11,’Product Database’!A2:D7,2,FALSE))
2. We would copy the formulas in cells B11, E11 and F11 down to the remainder of the item rows of the invoice.  Note that if we do this, the resulting formulas will no longer correctly refer to the database table.  We could fix this by changing the cell references for the database to absolute cell references.  Alternatively – and even better – we could create a range name for the entire product database (such as “Products”), and use this range name instead of the cell references.  The formula would change from this…      =IF(ISBLANK(A11),””,VLOOKUP(A11,’Product Database’!A2:D7,2,FALSE))…to this…      =IF(ISBLANK(A11),””,VLOOKUP(A11,Products,2,FALSE))

   …and then copy the formulas down to the rest of the invoice item rows.

3. We would probably “lock” the cells that contain our formulae (or rather unlock the other cells), and then protect the worksheet, in order to ensure that our carefully constructed formulae are not accidentally overwritten when someone comes to fill in the invoice.
4. We would save the file as a template, so that it could be reused by everyone in our company

If we were feeling really clever, we would create a database of all our customers in another worksheet, and then use the customer ID entered in cell F5 to automatically fill in the customer’s name and address in cells B6, B7 and B8.

If you would like to practice with VLOOKUP, or simply see our resulting Invoice Template, it can be downloaded from here.

JOIN THE DISCUSSION

Lowell Heddings, better known online as the How-To Geek, spends all his free time bringing you fresh geekery on a daily basis. You can follow him on Google+ if you'd like.

• Published 03/30/10