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

از آنجایی که در این آموزش جهت صفحه کاری از راست به چپ است در صورت عدم آشنایی به تغییر جهت صفحه آموزش زیر را مطالعه کنید:

 (جهت آشنایی با تغییر جهت صفحه کاری اینجا کلیک کنید)

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

پیشنهاد مطالعه: 

تابع xlookup در اکسل با قابلیت هایی که دارد بسیاری از محدودیت های تابع vlookup را حل کرده است، بعد از یادگیری تابع vlookup حتما این مطلب را مطالعه کنید:

تابع xlookup در اکسل (تایع جدید در اکسل 2021)

هدف تابع 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 جستجو میکند.

برای مشاهده یک مثال کاربردی از ترکیب تابع match و vlookup در محاسبات بانکی کلیک کنید.

نحوه انجام جستجو در یک ورک بوک دیگر با استفاده از تابع 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، به ورک بوک مور نظر منتقل شده و محدوده دلخواه را انتخاب کنید.

در تصویر زیر محدوده جستجو(table array) در ورک بوک pricelist و در ورک شیت prices قرار دارد. پس از پایان کار میتوانید ورک بوک حاوی جدول جستجو را ببندید و هیچ مشکلی برای فرمول vlookup شما رخ نخواهد داد ولی آدرس کامل مربوط به ورک بوک حاوی table array در درون formula bar به روشنی نشان داده میشود.  

فیلم آموزشی یکبار برای همیشه تابع vlookup را خوب یاد بگیرید (قسمت اول)

در سلسله آموزش های #فرمولهای_اساسی در اکسل، رفتیم سراغ تابع بسیار پرکاربرد #vlookup . اما این یک آموزش معمولی نیست، تا حد امکان این تابع را با جزئیات و با مصورسازی توضیح دادیم تا به صورت کامل تابع vlookup رو متوجه بشید و دیگه هیچ وقت در کارکردن با این تابع مشکلی نداشته باشید.

دانلود فیلم آموزشی قسمت اول

تابع 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 را خوب یاد بگیرید. (قسمت دوم)

توی قسمت دوم vlookup همین طور که قول داده بودیم میریم سراغ range_lookup و approximate match. شاید در خیلی از مواقع خواستید از تابع vlookup استفاده کنید و با خطای #N/a مواجه شدید. یا هر کاری کردید مقدار مناسب رو برای شما بر نمی گردونده.

بعد از مشاهده قسمت اول vlookup و این قسمت دیگه در کار با این تابع مشکلی نخواهید داشت.

دانلود فیلم آموزشی قسمت دوم

نکات کاربردی در تابع 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 می باشد. 

فیلم آموزشی یکبار برای همیشه تابع vlookup در اکسل را خوب یاد بگیرید (قسمت سوم)

یکی از مسائلی که به راحتی میشه با استفاده از #vlookup حل کرد، بحث محاسبات پلکانی هست. مالیات حقوق و دستمزد و پورسانت ها و تخفیف های پلکانی بر اساس حجم خرید سه مثال خیلی خوب برای این موضوع است. شما به راحتی می تونید با استفاده از یک ستون کمکی پورسانت مراحل قبل رو به صورت #تجمعی محاسبه کنید. در انتها با سه #vlookup و جمع و ضرب ساده این محاسبات رو انجام بدید.

دانلود فیلم آموزشی قسمت سوم

چرا تابع vlookup مقدار درست را نمایش نمی دهد یا خطای #N/A را باز می گرداند

اگر در هنگام کار با تابع vlookup با خطای #N/A مواجه می شوید، در اینجا سه دلیل عمده ای که این خطا ایجاد می شود و روش های حل آن را بررسی کرده ایم: خطای #N/A در توابع اکسل و روش ها حل آن

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

6 مشکل رایج در تابع vlookup

یکی از پرکاربردترین توابع جستجوگر در اکسل تابع Vlookup است اما بسیاری از کاربران اکسل با این تابع دچار مشکل هستند و حتی بعضی از کاربران ترجیح میدهند از جایگزین های اکسل برای این تابع استفاده کنند. واقعیت این است که در مورد تابع Vlookup اکسل نکاتی وجود دارد که با در نظر گرفتن این نکات تا حد زیادی میتوان از بروز خطا و مشکلات در کار با این تابع جلوگیری کرد. در این مطلب 6 مورد از مهمترین نکات در زمینه تابع Vlookup در اکسل را بررسی میکنیم.

پیشنهاد مطالعه: 

تابع xlookup در اکسل قابلیت هایی را ایجاد کرده است که بسیاری از محدودیت های تابع vlookup را حل می کند حتما مطلب زیر را مطالعه کیند:

تابع xlookup در اکسل (تایع جدید در اکسل 2021)

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

تابع VLOOKUP در اکسل | مثال ها، کاربردها و فیلم آموزشی

زمانی که به یک مطابقت دقیق احتیاج دارید

آخرین پارامتر از تابع Vlookup میزان مطابقت را در جستجویی که انجام میدهید، مشخص میکند. این پارامتر فقط دو مقدار 0 و 1 یا true و false را میگیرد. اکثر کاربران با استفاده از تابع Vlookup یک مقدار شبیه به نام یک محصول، یکی از کارکنان، مشتریان و … را جستجو میکنند. در این شرایط شما می بایست دقیقا همان مقداری را که مشخص میکنید جستجو کنید، بنابراین می بایست از یک مطابقت دقیق استفاده کنید و در نتیجه از مقدار false در پارامتر آخر تابع vlookup استفاده میکنید(مطابقت دقیق). در صورتی که مایلید مقدار مشخص شده را به صورت دقیق جستجو نکنید و مقدارهای مشابه به صورت تقریبی نیز در صورت وجود برگردانده شوند، از مقدار true در پارامتر آخر تابع Vlookup اکسل استفاده کنید. البته این پارامتر اختیاری است و در صورتی که آن را خالی قرار دهید، به صورت اتوماتیک مقدار true انتخاب میشود(مطابقت جزیی). در تصویر زیر پارامتر آخر تابع Vlookup وارد نشده است ولی مقدار اشتباهی برگردانده شده است. پس بهتر است در مورد این پارامتر تابع VLOOKUP دقت بیشتری داشته باشید. راه حل: برای حل مشکل بالا بهتر است مقدار False را در پارامتر آخر تابع وارد کنید تا نتیجه مورد نظر حاصل شود.

=VLOOKUP(H3,B3:F11,2,FALSE)

آدرس های جدول را قفل کنید.

ممکن است بخواهید از چندین vlookup برای برگرداندن اطلاعات مختلف در زمینه یک رکورد استفاده کنید. اما اگر میخواهید vlookup خود را به سلول های مختلف کپی کنید حتما میبایست آدرس های جدول خود را قفل کنید. در تصویر زیر یک فرمول vlookup را مشاهده میکنید که به صورت نادرستی وارد شده است. در این فرمول محدوده اشتباهی بعنوان lookup_value و table_array وارد شده است. راه حل: جدولی که تابع vlookup برای جستجوی اطلاعات در آن و برگرداندن اطلاعات از آن استفاده میکند را table_array میگویند. زمانی که میخواهید تابع Vlookup خود را در سلول های دیگر کپی کنید می بایست آدرس مربوط به این پارامتر را ثابت (مطلق) کنید. برای ثابت کردن آدرس جدول، آدرس آن را در درون فرمول انتخاب کنید و کلید F4 را فشار دهید تا از حالت نسبی به حالت مطلق تغییر یابد. این فرمول می بایست به شکل زیر  تبدیل شود.

=VLOOKUP($H$3,$B$3:$F$11,4,FALSE)

در این مثال هر دو پارامتر lookup_value و table_array ثابت شدند. هرچند معمولا نیاز به انجام این کار نیست و معمولا فقط با ثابت کردن پارامتر table_array مشکل حل خواهد شد.

در صورتی که با نحوه ثابت کردن آدرس ها در فرمول اکسل آشنا نیستید، مطلب زیر را مطالعه فرمایید.

ثابت کردن آدرس در فرمول های اکسل

یک ستون اضافه شده است

پارامترcol_index_num در تابع vlookup برای مشخص کردن ستون مورد نظر برای برگرداندن اطلاعات استفاده میشود. از آنجایی که این پارامتر به صورت شاخص عددی وارد میشود، از پایداری چندانی برخوردار نیست. به این معنی که اگر یک ستون جدید به جدول اضافه شود، ممکن است باعث متوقف شدن تابع Vlookup شما و عدم اجرای صحیح آن شود. در تصویر زیر شما چنین حالتی را مشاهده میکنید. مقدار quantity در ستون 3 وارد شده است. ولی زمانی که یک ستون جدید در جدول اضافه میشود، به دلیل به روز رسانی نشدن اتوماتیک تابع vlookup ، نتیجه صحیح برگردانده نمیشود. راه حل 1: یک راه حل این است که با protect کردن ورک شیت اجازه اضافه کردن ستون جدید را به کاربران ندهیم. ولی اگر ماهیت کار به نحوی است که کاربران می بایست حتما این کار را انجام دهند، این روش قابل استفاده نیست. راه حل 2: در این روش، شما میتوانید از ترکیب تابع vlookup و match استفاده کنید، به این صورت که به جای پارامتر co;_index_num از یک تابع match استفاده کنید. تابع match میتواند شماره ستون مورد نیاز را جستجو کرده و برگرداند. در این صورت پارامتر col-index-num داینامیک میشود و اضافه کردن یک ستون جدید تغییری در نتیجه تابع vlookup ایجاد نمیکند. در مثال بالا میتواند از فرمول زیر برای رفع مشکل استفاده کنید.

مطالعه بیشتر:

معرفی تابع match در اکسل، ترکیب vlookup و match در محاسبات بانکی-توابع اساسی اکسل

جدول بزرگتر شده است

هرچه تعداد ردیف های بیشتری به جدول شما اضافه میشود، شما می بایست تابع vlookup خود را به روز رسانی کنید تا از قرار گرفتن این ردیف ها در درون فرمول vlookup اطمینان حاصل کنید. تصویر زیر یک تابع Vlookup را نشان میدهد که تمام آیتم های جدول را مورد بررسی و جستجو قرار نمیدهد. راه حل: برای حل این مشکل میتوانید فرمت داده های خود را به حالت جدولی تبدیل کنید(ctrl+T) و یا اینکه محدوده مورد نظر را به صورت محدوده های دارای نام تبدیل کنید. با استفاده از این تکنیک های، مطمئن خواهید بود که همیشه کل جدول شما توسط فرمول vlookup مورد بررسی قرار خواهد گرفت. در تصویر زیر مشاهده میکنید که محدوده table_array تبدیل به جدولی به نام fruitlist شده است.

Vlookup قادر به جستجو به سمت چپ نیست.

یکی از مهمترین محدودیت های تابع vlookup این است که این تابع قادر به انجام عملیات جستجو به سمت چپ نیست. برای حل این مشکل میتوانید از را حل زیر استفاده کنید. راه حل 1: یکی از بهترین راه حل های برای این موضوع این است که اصلا از تابع vlookup استفاده نکنیم. یک روش ابداعی فوق العاده برای جایگزین تابع vlookup ، ترکیب تابع match و index است. استفاده از این ترکیب مزیت های بسیار فوق العاده ای نسبت به تابع vlookup دارد. در تصویر زیر نمونه ای را مشاهده میکنید که فرمول index-match به خوبی جستجو را در سمت چپ ستون مورد نظرانجام میدهد.

بحث ترکیب تابع index و match بسیارجالت و کاربردی است و پیشنهاد میکنیم حتما مطالب زیر را در این زمینه مراجعه کنید.

• معرفی ترکیب توابع index و match در اکسل

• استفاده ترکیبی از توابع index و match جهت محاسبات داینامیک و ساخت داشبورد اکسل

• چرا ترکیب توابع index و match بهتر از تابع vlookup است؟

در جدول شما مقادیر تکراری وجود دارد

تابع vlookup فقط یک مقدار را برمیگرداند. به این معنی که همیشه اولین رکوردی که مطابق با جستجوی شما باشد، برگردانده میشود. اما در صورت وجود مقادیر تکراری در جدول شما ممکن است مشکلاتی در عملیات شما رخ دهد. راه حل1: آیا واقعا لازم است که در لیست شما مقادیر تکراری وجود داشته باشد؟ در صورتی که پاسخ منفی است، با استفاده از تب data و گزینه remove duplicates مقادیر تکراری را حذف کنید. راه حل 2:

در صورتی که لیست شما می بایست دارای مقادیر تکراری باشد، vlookup به کار شما نمی آید و بهتر است از پیوت تیبل استفاده کنید.

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

معرفی تابع vlookup و کاربرد عملی در محاسبه دستمزد (آموزش اکسل در حسابداری و مالی)

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

اگر توی مشاهده این کلیپ با ابهام رو به رو شدید، حتما قسمت های قبل آموزش ها را مشاهده کنید، و در صورت داشتن هر گونه سوال با ادمین های کانال در ارتباط باشید یا در بلاگ ما در قسمت نظرات، سوالات خود را مطرح کنید

نگاه آرایه ای به تابع vlookup

همه ما با تابع Vlookup و ویژگی های این تابع مهم در اکسل آشنا هستیم . شما میتوانید با استفاده از تابع Vlookup در اکسل، مقادیر مورد نظر خود را در یک ناحیه از سلول های جستجو کنید و مقدار متناظر با آن را در ستون دلخواه بعنوان نتیجه برگردانید. اما در ادامه دوره فرمول نویسی پیشرفته در اکسل و مبحث آرایه ها، میخواهیم با نگاه آرایه ای به تابع Vlookup در اکسل نگاه کنیم و ببینیم خاصیت آرایه ها در اکسل چگونه میتواند باعث بهبود کارایی و قابلیت های تابع Vlookup در اکسل شود.

در صورتی که با تابع Vlookup در اکسل آشنا نیستید میتوانید مطالب “تابع VLOOKUP در اکسل | مثال ها و کاربردها” را مطالعه کنید.

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

برای مشاهده قسمت قبل دوره فرمول نویسی پیشرفته در اکسل میتوانید مطلب ” محاسبه مبلغ قابل پرداخت در اکسل ” را مشاهده کنید. 

مشاهده با کیفیت 720