تابع VLOOKUP در اکسل

مثال ها، کاربردها و فیلم آموزشی

تابع vlookup از کاربردی ترین توابع اکسل است. تابع vlookup یکی از توابع اکسل جهت جستجو و بازیابی اطلاعات از یک ستون مشخص از یک جدول می­باشد. تابع vlookup دارای حالت تقریبی و حالت دقیق و wild cards برای مقادیر مشخص می­باشد. V که اولین حرف vlookup است، مخفف کلمه vertial به معنی«عمودی» می­باشد یعنی این تابع به صورت عمودی در ستون (یا آرایه) کار جستجو را انجام می دهد.

 

قبل از شروع آموزش تابع vlookup، بحث را با طرح یک مثال ساده شروع می کنیم. در عکس بالا فرض کنید هر فرد یک سری اطلاعات مانند شماره شناسایی، نام، نام خانوادگی، ایمیل و موقعیت شغلی دارد. شما میخواهید بر اساس شماره شناسه، ایمیل یا سایر اطلاعات فرد را فراخوانی کنید. ساده ترین راه استفاده از تابع vlookup است. در ادامه با هدف تابع vlookup، نحوه استفاده و نکات و مثال های ریز و درشت این تابع آشنا خواهید شد.

هدف تابع vlookup

جستجو یک مقدار مشخص در جدول بر اساس تطابق آن با ستون اول جدول مورد نظر

نتیجه دریافتی از vlookup اکسل

یافتن مقدار مورد نظر از جدول

ترکیب بندی یا اجزای تابع vlookup

=VLOOKUP (lookup_value , table_array , col_index , [range-lookup])

lookup_value :  مقداری که از اولین ستون جدول جستجو می­شود. این پارامتر میتواند یک آدرس سلول، یک مقدار( متنی، عددی، تاریخ) یا مقدار برگردانده شده توسط یک تابع دیگر باشد. برای مثال:

• فرمول (2,vlookup(40,A2:B15 مقدار 40 را جستجو میکند.
• به همین صورت فرمول (2,vlookup(“Apple”,A2:B15 رشته متنی apple را در محدوده تعریف شده جستجو میکند و مقدار متناظر را برمیگرداند. حتما توجه داشته باشید که برای تعریف رشته های متنی می بایست این مقادیر را در درون “” قرار دهید.
• اما همانطور که گفتیم شما میتوانید از مقادیر سلول های دیگر نیز بعنوان lookup value در فرمول های vlookup خود استفاده کنید. برای مثال فرمول (2,vlookup(c2,A2:B15  مقدار موجود در سلول c2 را در بازه مشخص شده جستجو میکند و مقدار متناظر را برمیگرداند.

 table Array: جدولی که مقادیر مورد نظر در آن جستجو می­شود و متشکل از دو یا چند ستون است.

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

col-index-num : شماره ستونی که قرار است مقادیر مورد نظر از آن بازیابی شود. همانطور که گفته شد تابع vlookup همیشه مقدار مورد نظر را در اولین ستون جستجو میکند و مقدار متناظر با آن را در ستون دلخواه شما که توسط این پارامتر مشخص میکنید برمیگرداند. همیشه سمت چپ ترین ستون در جدول مشخص شده شماره 1، ستون بعد شماره 2 و … می باشد.

rang-lookup : به صورت پیش فرض TRUE می­باشد و نشان دهندة مقدار تقریبی است و کلمه FALSE که نشان دهندة مقدار دقیق است.این پارامتر اختیاری است ولی از اهمیت بسیار فوق العاده ای برخوردار است و در بسیاری از موارد مشکلاتی که برای کاربران در رابطه با تابع vlookup پیش می آید ناشی از بی دقتی در استفاده از این پارامتر است. در ادامه این مطلب توضیحات بیشتری در این زمینه ارائه خواهیم کرد.

نکات کاربردی تابع vlookup

تابع vlookup برای بازیابی داده ها در جدولی است که با اطلاعات آن در ردیف های عمودی قرار گرفته اند که هر ردیف نشان دهنده یک ثبت جدید می­باشد. کلمه V در vlookup مخفف vertial به معنی «عمودی» می­باشد.

اگر اطلاعات شما به صورت افقی طبقه بندی شده­ اند باید از تابع HLOOKUP استفاده کنید.

تابع vlookup فقط مقدارهای سمت چپ را می‌آورد

تابع vlookup جستجوی خود را از داده ­های ستون

سمت راست شروع کرده و مقادیر موردنظر و جواب­های درخواستی را در ستون سمت چپ (در صورتی که صفحه کاری راست چین باشد) متناظر به نمایش می­گذارد.

 

تابع vlookup داده­ ها را بر اساس شماره ستون بازیابی می­نماید

وقتی که شما از تابع vlookup اکسل استفاده می­کنید تصور نمایید که هر ستون جدول شماره­گذاری شده است و از سمت راست هر جدول کار شروع می­شود. برای دریافت یک مقدار از ستون خاص، شماره مورد نظر را به عنوان ستون شاخص وارد نمایید.

 

 

 

 

 

 

 

=VLOOKUP(H3,B4:E13,2,FALSE) // نام

=VLOOKUP(H3,B4:E13,3,FALSE) // نام خانوادگی

=VLOOKUP(H3,B4:E13,4,FALSE) // ایمیل

چند مثال از تابع vlookup در اکسل

نحوه جستجو در یک ورک شیت دیگر با استفاده از vlookup

در عمل به ندرت اتفاق می افتد که شما با استفاده از vlookup یک مقدار را در همان ورک شیت جستجو کنید و اغلب مقادیر خواسته شده در ورک شیت های دیگر قرار دارند. برای جستجو در یک ورک شیت دیگر می بایست نام ورک شیت و یک علامت تعجب(!) را قبل از محدوده مورد نظر وارد کنید. برای مثال (vlookup(40,sheet2!A2:B6,2 . با استفاده از این فرمول table array شما محدوده A2:B6 از شیت 2 قرار می گیرد.

اما شما مجبور نیستید نام شیت را به صورت دستی وارد کنید. کافیست شروع به نوشتن فرمول کنید و به جای نوشتن table array به شیت مورد نظر منتقل شده و محدوده دلخواه را انتخاب کنید. برای مثال در تصویر زیر فرمول نوشته شده مقدار “Product 1” را در ستون A ورک شیت prices جستجو میکند.

نحوه انجام جستجو در یک ورک بوک دیگر با استفاده از تابع Vlookup اکسل

برای انجام جستجو در یک ورک بوک دیگر نیز شبیه به مثال قبل عمل میکنیم با این تفاوت که نام ورک بوک دلخواه را در درون براکت قرار داده و سپس نام ورک شیت( به اضافه علامت تعجب) و آدرس محدوده دلخواه را وارد میکنیم. برای مثال به منظور جستجوی مقدار 40 در شیت 2 ورک بوک numbers از فرمول زیر استفاده میکنیم.

=VLOOKUP(40,[Numbers.xlsx]Sheet2!A2:B15,2)

در صورتی که نام ورک بوک یا ورک شیت شامل فاصله یا کاراکترهای غیر الفبایی باشد می بایست حتما نام آنها را در درون علامت ‘ ‘ قرار دهید . برای مثال:

=VLOOKUP(40,'[Numbers.xlsx]Sheet2′!A2:B15,2)

اما در اینجا نیز راهی ساده تر از وارد کردن نام های معمولا پیچیده ورک بوک در درون فرمول های vlookup وجود دارد. شبیه به مثال قبل شما میتوانید:

1. هر دو ورک بوک را باز کنید. البته این مرحله اجباری نیست ولی پیشنهاد میشود برای ساده تر شدن کار قبل از شروع به نوشتن فرمول vlookup هر دو ورک بوک را باز کنید.
2. شروع به نوشتن فرمول خود کنید و به جای نوشتن دستی پارامتر table-array، به ورک بوک مور نظر منتقل شده و محدوده دلخواه را انتخاب کنید.

تابع vlookup شامل حالت تقریبی و حالت دقیق می­باشد

این وضعیت را می­توان توسط بخش چهارم فورمول که «range-VLOOKUP» نامیده می­شود تنظیم نمود. اگر عبارت «FALS» در این قسمت نوشته شود تابع vlookup دقیقاً کلمه مورد نظر را جستجو خواهد کرد و اگر عبارت «TURE» در این قسمت نوشته شود نتیجه با یک عبارت تقریبی منطبق خواهد شد.

=VLOOKUP(value, table, column) // حالت پیش فرض: تقریبی

=VLOOKUP(value, table, column, TRUE) // تطابق تقریبی

=VLOOKUP(value, table, column, FALSE) // تطابق دقیق

توجه : قسمت «range-lookup» به صورت پیش فرض روی کلمه «TURE» می­باشد بنابراین به صورت خودکار در جستجوی خود بر یک مقدار تقریبی منطبق می­شود.

مثال 1 : تطبیق دقیق یا مطابقت کامل در تابع vlookup

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

فرمول خانه H6 برای جستجوی نام فیلم بر اساس سال به صورت زیر خواهد بود.

=VLOOKUP(H4,B5:E9,2,FALSE) // FALSE = تطابق دقیق

مثال 2 : مطابقت تقریبی در تابع vlookup اکسل

در این حالت شما در جستجوی بهترین و نزدیک­ترین مقدار با هدف مورد نظر خود هستید و مقدار دقیق مد نظر شما نیست. به عنوان مثال یافتن نرخ حق کمیسیون بر اساس مقدار فروش ماهانه است. در این حالت شما می­خواهید تابع vlookup بهترین نتیجه منطبق با داده­های ورودی را برای شما پیدا نماید. در جدول زیر فرمول خانه D5 نشان دهندة مقدار تقریبی به سمت کمترین مقدار نرخ حق کمیسیون در جدول مورد نظر می­باشد.

=VLOOKUP(C5,$G$5:$H$10,2,TRUE) // TRUE = تطابق تقریبی

نکته : زمانی که از حالت تقریبی vlookup استفاده می­کنید داده­های شما باید به صورت صعودی در lookup value ثبت شود.

نکات کاربردی در تابع vlookup اکسل

Range-lookup : بررسی می­کند که آیا value ها دقیقاً مطابقت دارند یا خیر. به صورت پیش فرض TURE = با یک مقدار تقریبی

برای دریافت یک مقدار دقیق «rang-vlookup» را روی «FALSE» تنظیم کنید.

وقتی range-lookup که به صورت پیش فرض روی TRUE قرار دارد VLOOKUP نزدیکترین مقدار با ارزش خواسته شده به سمت کمترین مقدار را پیدا می­کند و به نمایش می­گذارد.

وقتی range-vlookup حذف می­شود VLOOKUP یک مقدار نه چندان دقیق را جستجو می­­کند ولی در صورت وجود مقدار دقیق همان را به نمایش می­گذارد.

وقتی range-lookup در حالت پیش فرض TRUE قرار دارد اطمینان حاصل کنید که مقادیر موجود در ردیف اول جدول به صورت صعودی مرتب شده­اند. در غیر اینصورت ممکن است تابع vlookup یک مقدار نادرست یا غیرمنتظره را نمایش دهد. وقتی rang-lookup روی FALSE قرار می­گیرد (نیازمند یک تطابق دقیق)، مقادیر در ستون اول جدول نیازی به مرتب بودن ندارند.

استفاده از کاراکترهای wildcard در تابع vlookup

در فرمول های vlookup نیز میتوانید شبیه به هر تابع دیگری در اکسل از کاراکترهای wildcard استفاده کنید. قبلا در مطالب متعددی در مورد کاراکترهای Wildcard اکسل صحبت کرده ایم. در صورتی که مطالب قبل در این زمینه را مطالعه نکرده اید یا با کاراکترهای وایلدکارد در اکسل آشنایی ندارید پیشنهاد میکنیم مطلب زیر را در این باره مطالعه کنید:

 

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

شما در موارد متعددی میتوانید از این کاراکترها در فرمول های خود بهره مند شوید. از جمله:

• زمانی که متن دقیق مورد نظر را که میخواهید جستجو کنید نمیدانید.
• زمانی که میخواهیم عبارتی را جستجو کنیم که قسمتی از محتوای یک سلول است. عموما فرمول های Vlookup کل محتوای یک سلول را جستجو میکنند. در این شرایط یکی از بهترین گزینه ها استفاده از کاراکترهای wildcard در ترکیب با تابع vlookup است.
• زمانی که مقدار مورد جستجو دارای فاصله های اضافی در ابتدا یا انتها می باشد .
• …

مثال 1: جستجوی عبارت متنی که با مقدار خاصی شروع میشود یا به پایان میرسد.

فرض کنید میخواهید نام یکی از مشتریان را از لیست زیر پیدا کنید ولی شما نام او را به یاد نمی آورید و صرفا میدانید که نام او به مقدار “ack” شروع میشود.  برای انجام این کار میتوانید از کاراکترهای وایلدکارت در ترکیب با تابع vlookup به شکل زیر استفاده کنید:

=VLOOKUP(“ack*”,$A$2:$C$11,1,FALSE)

زمانی که اطمینان حاصل کردید که نام صحیح را پیدا کرده اید میتوانید از یک فرمول مشابه برای یافتن مقدار پرداخت شده توسط این مشتری استفاده کنید. برای انجا این کار فقط کافیست پارامتر سوم فرمول را به 3 تغییر دهید تا مقدار متناشر با آن مشتری در ستون Sum برگردانده شود.

=VLOOKUP(“ack*”,$A$2:$C$11,3,FALSE)

در زیر چند نمونه دیگر در این زمینه ارائه شده است:

=VLOOKUP(“*man”,$A$2:$C$11,1,FALSE)

نامی که با مقدار man به پایان میرسند را پیدا میکند.

=VLOOKUP(“ad*son”,$A$2:$C$11,1,FALSE)

نامی که با ad شروع میشود . با son به پایان میرسد را پیدا میکند.

=VLOOKUP(“?????”,$A$2:$C$11,1,FALSE)

یک نام پنج کاراکتری را پیدا میکند.

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

مثال 2: نوشتن فرمول vlookup بر اساس مقدار یک سلول

در این میخواهیم یک مقدار را که فقز قسمتی از آن را میدانیم، در یک سلول جستجو کنیم . فرض کنید یک کد در ستون A و یک نام مربوط به آن کد در ستون B در اختیار دارید. حال فرض کنید میخواهیم نام مربوط به یک کد را که فقز قسمتی از آن را در اختیار داریم را پیدا کنیم. برای انجام این کار کاراکترهایی را که در اختیار داریم را در سلول C1 (مقدار مورد جستجو) وارد میکنیم و از فرمول زیر برای استخراج نام مربوط به آن کد استفاده میکنیم:

=VLOOKUP(“*”&C1&”*”,$A$2:$B$12,2,FALSE)

توجه داشته باشید که در پارامتر اول، قبل و بعد از آدرس سلول از علامت (&) استفاده کردیم تا رشته های متنی را به هم متصل کند. همانطور که در تصویر زیر مشاهده میکنید، مقدار jeremy hill برگردانده شد. زیرا تنها کد مربوط به این نام است که شامل محتوای سلول C1 می باشد.