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

تابع vlookup اکسل

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

نکته:
میتونیم از ترکیب دو تابع Index و Match که آموزشش در مطالب سایت وجود داره به عنوان جایگزین تابع Vlookup استفاده کنیم. زمانی که فایل اکسل ما سنگین هست و محاسبات زیادی داره این کار میتونه باعث بهبود سرعت محاسبات بشه.

قاعده کلی تابع Vlookup اکسل به صورت زیر هست:

(جستجوی تقریبی [اختیاری]، شماره ستون، محدوده جستجو، مقدار موردنظر برای جستجو) VLOOKUP

فرض کنید ما یک Workbook داشته باشیم که دو تا شیت حاوی اطلاعات زیر داشته باشه:

Sheet1:

A B C D
1 کد محصول نام محصول تعداد برنامه تولید تعداد تولید شده
2 1411 ماوس 3000
3 2712 کیبورد 2000
4 7415 مانیتور 1000
5 1352 فلش 1000

Sheet2:

A B C
1 کد محصول نام محصول تعداد تولید شده
2 1411 ماوس 2000
3 2712 کیبورد 1500
4 7415 مانیتور 350
5 1352 فلش 700
6 1352 فلش 800

حالا اگر بخوایم تعداد تولید شده متناظر هر محصول رو از Sheet2 برداریم و در ستون D در Sheet1 قرار بدیم میتونیم از تابع Vlookup اکسل استفاده کنیم. ما میخوایم در سلول D2 در Sheet1 تعداد تولید شده مربوط به محصول ماوس از Sheet2 قرار بگیره. برای اینکار پارامترهای تابع Vlookup رو به ایتصورت مقداردهی میکنیم.

مقدار موردنظر برای جستجو:  این پارامتر مقداری رو که قراره در محدوده موردنظر ما جستجو بشه تعیین میکنه. در واقع ما با این پارامتر به تابع میگیم که از بین اعداد موجود در ستون تعداد تولید شده Sheet2، عددی رو که در سطر متناظر این پارامتر قرار داره برگردونه. ما در این مثال میخوایم تعداد تولید شده رو برای محصولات مختلف بدست بیاریم. بنابراین مقداری که ما میخوایم بر اساس اون جستجو رو انجام بدیم کد هر محصول هست که در ستون A قرار داره. پس پارامتر اول ما برابر A:A (ستون A مربوط به Sheet1) هست.

محدوده جستجو: در این پارامتر ما بازه ای رو که در اون قراره جستجو رو انجام بدیم تعریف میکنیم. این بازه باید از ستونی که ما قراره در اون جستجو رو انجام بدیم (در مثال ما ستون کد محصول) شروع بشه و ستونی که به دنبال نتایج متناطر جستجو از اون هستیم (ستون مربوط به تعداد تولید شده) رو هم شامل بشه. ما میخوایم کد هر محصول (A2) رو در ستون A در Sheet2 جستجو کنیم و بعد از پیدا کردن شماره سطری که مقدار A2 در اون قرار داره، مقدار متناظرش رو از ستون C (تعداد تولید شده) برگردونیم. پس پارامتر دوم ما برابر Sheet2!A:C (ستون های A تا C مربوط به Sheet2) هست.

شماره ستون: این پارامتر تعیین میکنه که پس از پیدا شدن مقدار مورد نظر جواب تابع از چه ستون متناظری در محدوده جستجو باید استخراج بشه و به عنوان نتیجه تابع نشون داده بشه. در این مثال ما جستجو رو در ستون یک (ستون مربوط به کد محصول) انجام میدیم اما هدف ما رسیدن به تعداد تولید شده مربوط به کد محصول مورد نظر (سومین ستون محدوده جستجو) هست پس باید شماره ستون رو برابر 3 قرار بدیم. این باعث میشه که هنگامی که کد محصول مورد نظر ما (پارامتر مقدار موردنظر برای جستجو) در ستون اول پیدا شد، نتیجه تابع از ستون سوم که نشان دهنده تعداد تولید شده مربوط به اون کد محصول هست استخراج بشه.

جستجوی تقریبی: این پارامتر در تابع VLOOKUP اختیاری هست. اگر این مقدار رو برابر TRUE قرار بدید به این معنی هست که اگر مقدار موردنظر برای جستجو در محدوده جستجو وجود نداشت بزرگترین مقدار موجود در محدوده جستجو که کوچکتر از مقدار موردنظر برای جستجو هست به عنوان نتیجه جستجو انتخاب بشه. به عنوان مثال در جدول بالا بزرگترین کد محصول موجود در Sheet2 برابر 7415 هست. حالا اگر جستجوی تقریبی رو برابر TRUE قرار بدیم و پارامتر مقدار موردنظر برای جستجو رو 8000 تعیین کنیم، تابع VLOOKUP نتیجه رو برای بزرگترین مقدار موجود در محدوده جستجو که که کوچکتر از 8000 هست (7415) نشون میده. اگر در این حالت پارامتر جستجوی تقریبی رو برابر  FALSE قرار بدیم تابع خطای !REF# رو برمیگردنه. ما میخوایم فقط تعداد تولید شده هر محصول در مقابلش قرار بگیره بنابراین جستجوی تقریبی رو False قرار میدیم.

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

(VLOOKUP(A:A;Sheet2!A:C;3;False

حالا فرمول رو در سلول های D3 تا D5 کپی میکنیم تا تعداد تولید شده هر محصول در سلول متناظرش قرار بگیره. در نهایت Sheet1 ما به شکل زیر در میاد.

A B C D
1 کد محصول نام محصول تعداد برنامه تولید تعداد تولید شده
2 1411 ماوس 3000 2000
3 2712 کیبورد 2000 1500
4 7415 مانیتور 1000 350
5 1352 فلش 1000 700

نکته ای که در مورد تابع Vlookup اکسل وجود داره اینه که این تابع هنگام جستجو اولین مقدار پیدا شده رو بر میگردونه. مثلاً در مورد محصول فلش که در Sheet2 دو سطر با مقادیر 700 و 800 وجود داره فقط اولین مقدار یعنی  عدد 700 به عنوان نتیجه تابع برگردونده میشه. اگر بخوایم در این حالت تمام اعداد موجود مربوط به اون کد محصو با هم جمع بشه و عدد 1500 به عنوان تعداد تولید شده فلش برگردونده بشه باید از تابع SUMIF استفاده کنیم.

امیدوارم این آموزش براتون مفید بوده باشه.

443 Comments on “تابع VLOOKUP اکسل و نحوه استفاده از آن”

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

  2. سلام من دو تا فایل دارم که حاوی اطلاعات بورس هستن یکیشون 300 تا شرکت هست با اطلاعات کامل و اون یکی از فایل هام فقط اسم 100 تا شرکت هست که قبلا آنالیز شده و هیچگونه اطلاعات دیگه ای جلوش نیست فقط اسم شرکت هست میخوام این 100 تا اسم رو از ین اون 300 تا با اطلاعاتشون در بیارم از طریق این فرمول میتونم در بیارم ؟؟

  3. سلام.
    من یه سری کد(بیش از 1000 تا) رو می خواهم در یک ستون برام پیدا کنه و ستون دیگری که مبلغ متناظر با این کد ها هستش رو بهم نمایش بده و در انتها جمع کل مبلغ ها رو هم بهم نشون بده.
    با vlookup انجام بدهم؟؟

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

  5. سلام
    یه سوال مدتی هست ذهن من رو درگیر خودش کرده و اون اینکه اگر به عنوان مثال ما ی ستون به اسم ستون A داشته باشیم که حاوی اطلاعات (عدد) هستش
    ستون B اطلاعاتی هستش که مشتری بهمون میده که اون هم شامل عدده . حالا با وارد کردن اطلاعات مشتری در ستون B میخاهیم در ستون C نشون بده چه تعداد از این اطلاعات (ستون B) در ستون مرجع ما که همون ستون A هستش قرار داره یا بهتره بگم در ستون C اشتراکات AوB رو نشون بده

  6. سلام.
    ممنون از سایت خوبتون. من یک فایل اکسل دارم حاوی اطلاعات فروش به شرکتهاست.
    می خواهم اطلاعات مربوط به فروش هر شرکت رو در شیت دیگه ای داشته باشم.
    این اطلاعات شامل تاریخ فروش و مبلغ فروش میشه.
    ممنون میشم اگه بتونید راهنمایی کنید از چه توابعی استفاده کنم.

  7. سلام
    من حالت عکس vlookup را نیاز دارم . یعنی کلمه را بدهم و از لیست اظلاعات شماره مورد نظر ظاهر شود .قبلا متشکرم

  8. سلام میشه راهتماییم کنین. میخوام بدونم توی اکسل چطور میشه من کلمه ای رو توی یک سلول بنویسم و بعد توی یه سلول دیگه اطلاعات مربوط به اون کلمه نمایش داده بشه. ایا امکان پذیره؟ بهتر بگم یه سری محصول دارم (برای هر مشتری حدود 5 کالا)که میخوام توی سلولی که کد نویسی کردم قیمت نهایی اون محصول ها برای مشتری نمایش داده بشه.
    =SUM(IF(B2=”مقاومت”؛100؛IF(B2=”ای سی”؛500؛))+IF(C2=”مقاومت”؛100؛IF(C2=”ای سی”؛500؛0)))+SUM(IF(D2=”t”؛900؛0))

  9. با سلام
    خواستم ببینم با چه فرمول یا تابع یا گزینه ای میشه این کار رو انجام داد:
    میخوایم جابجایی های مبلغ بین حسابهای یک شخص رو کنترل کنیم و پیدا کنیم.
    حالا کاری که باید بکنیم برای کنترل دقیق تر اینه که ما میخوایم فرمولی باشه مبالغ همسان رو در 2 ستون (برداشت و واریز) با ستون تاریخ مقایسه کنه و مشابه ها رو برامون به هر شکلی که میتونه مشخص کنه.
    تاریخ برداشت واریز
    1396/05/13 50.000.000 7.000.000
    1396/05/13 2.000.000 50.000.000
    مثال : تابعی که برامون مشخص کنه برداشت و واریز 50 میلیون در تاریخ 13 مرداد رو و همچنین بقیه سطر هارو
    خواهشمند است هرکس اطلاعی داره بهم بگه.تشکر

  10. با سلام و عرض احترام
    من میخواستم ببینم آیا میتوانم از اکسل به عنوان برنامه حسابداری استفاده کنم . یعنی با وارد کردن کد کالا کلیه اطلاعات مربوط به کالا در فاکتور ظاهر شود .اگر میشه لطفا راهنمایی بفرمایید./ البته قید کنم بنده کاملا مبتدی هستم در اکسل

  11. سلام و خسته نباشید جناب آقای اسماعیل پور از مطالب مفید شما سپاسگزارم ما در یک شهرک حدود 20 تا ساختمان برج داریم که مشخصات مالکین اینا با شماره ساختمان و واخد و مساحت .وکارت عضویت بصورت اکسل موجود میباشد حال میخواهیمدر بین این دو هزار مالک دنبال اسمی از مالکین بگردیم این عمل چگونه امکان پذیر میباشد ممنون خواهم شد اگر راهنمایی بفرمائید.

  12. با سلام و احترام
    همونطور که میدونید دستور VLOOKUP از راست به چپ دنبال میگرده
    لطفاً بفرمائید راهی وجود داره که این جستجو از چپ به راست انجام بشه
    با تشکر

  13. با سلام و احترام
    من یک ستون اعداد رو میخوام در یک فایلی پیدا کنم از vlookup استفاده کردم بعضی اعداد رو پیدا میکنه بعضیاشو N/A میزنه در صورتی که با find پیداش میکنه و واقعا در هر دو فایل موجوده. اگر فرمولم اشتباه باشه همه رو باید N/A بزنه ولی الان فقط بعضیاشو تشخیص نمیده. ممکنه فرمت تمام اعداد توی دو تافایل یکی نباشه؟ اگر مشکل فرمتشون باشه چطوری باید درستش کنم؟ میشه منو راهنمایی کنید؟

  14. با عرض سلام و خسته نباشید زمانی که از vlookup استفاده می کنم و برای جستجو هنوز چیزی را جستجو نکرده ام سلول های جستجو شده پیغام #N/A می دهند. آیا راهی هست که قبل از جستجو خالی نماش داده شوند ؟

  15. اگر سلول A1 را lookup value فرض کنیم می توان از if برای حل مشکل استفاده کرد.
    … )if($A$1=””;””;VLOOKUP=

  16. با سلام
    من در فایلی از این تابع استفاده کردم
    ولی داده ها به طور اشتباه برگردونده شده
    به جای داده مرتبط داده، یک سطر بالاتر به عنوان جواب تابع اومده میشه راهنماییم کنید

  17. سلام
    لطفا توضیح دهید که آیا امکان دارد مثلا دو ستون داریم یکی زمان را مشخص می کنه و دیگری اعداد داخلش هستند می خواهیم در یک سلول دیگر max عدد را پیدا کرده و در سلول دیگر با یک فرمول مقدار عدد max پیدا شده در چه ساعتی بوده
    ممنون
    مثلا
    14 2:42
    14 4:00
    18 5:30

  18. سلام و خسته نباشید
    در شیت 1 یک لیستی دارم شامل شماره عضویت (ستون a ) و تاریخ واریز (ستون b )و شماره پیگیری (ستون c) ، که در ستون a و B در ردیف های مختلف و البته نا معلوم احتمال تکرار وجود دارد (چرا که شماره عضویت یک نفر یکی است ولی چندین مبلغ را در تاریخ های گوناگون واریز میکند .
    در شیت های 2 تا 35 که مخصوص عضو با شماره عضویت 1 تا 34 هست ، در ستون a تاریخ واریز ، در ستون b شماره پیگیری وجود دارد .
    سوال : با استفاده از چه تابعی میتوانیم با وارد کردن شماره عضویت ، تاریخ واریز ، شماره پیگیری در شیت 1 ، این اطلاعات را خودمان دوباره در شیت مخصوص عضو وارد نکنیم و اطلاعات وارده در شیت 1 مستقیم وارد شیت مخصوص عضو شود ؟

  19. با سلام
    سایتتون بسیار آموزنده هست و امیدوارم موفق باشین
    در قسمت آخر توضیحاتتون تابع sumif را باید کاملا به جای تابع vlookup استفاده کرد یا باهم ادغام کرد دو تابع را ؟

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

  21. سلام
    چطوری میشه همزمان دوتا شرط رو برای پیدا کردن جواب اعمال کرد؟
    مثلا جدولی شامل ردیف اول نام و ردیف دوم نام خانوادگی و ردیف سوم سن
    با چه فرمولی می شه وقتی اسم رو توی یک سلول و فامیل رو توی سلول دیگر نوشت در سلول سوم سن رو از جدول پیدا کنه و بنویسه؟
    امین محمدی 25
    امین رحمانی 30
    محمد رحمانی 22
    دستور vlookup فقط یک فیلتر ایجاد می کنه ولی من نیاز به دوتا فیلتر دارم

  22. سلام
    من فایلی را در اکسل دارم که میخواهم با تشکیل یک کمبو باکس از مجموع 900 قلم کالا قیمت کل را به من بدهد با دستور lookup نتوانستم انجام بدهم چون دیتا ظاهرا باید صعودی باشد که در فایل من امکان پذیر نیست وقتی گزارش میگیرم و یک قلم را انتخاب میکنم کد و قیمت مربوط به چند سطر بالاتر را نشان میدهد چطور میتوانم اینکار را انجام بدهم؟

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

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

  25. سلام
    من میخوام تابع شرطی برای یک بازه بدم، یعنی IF(F5=”a1:a5″;2;0)= ولی جواب نمیده.
    میخوام با لیست کشویی در یک خانه اگر از خانه a1 یا a2 و یا … تا a5 انتخاب شد 2 بده در غیر این صورت بده 0.
    ممنون میشم اگر کمک کنید.

  26. باسلام و تشکر از اموزش و انرژی تون
    اگر در مثال بالا کدهای محصول تکراری باشند چطور میتونیم اخرین تعداد تولید رو از طریق فرمول بدست ؟

  27. چگونه میتوان در VLOOKUP برای کالایی که دو مقدار در SHEET2 دارد از تابع SUMIF استفاده کرد؟؟؟؟

  28. با سلام و احترام
    من یک فایل در اکسل تشکیل دادم که ستون افقی مربوط به برگه های موجود در یک پرونده و ستون عمودی مربوط به بخش هایی است که پرونده از آنجا ارسال می شود نقص مربط به هر کس (پزشک ،پرستار،منشی و …) را با کد عددی 1 و 2 و 3 تا 9 مشخص کردم حالا میخوام درصد نقص مربوط به هر شخص را در هر بخش مشخص کنم چگونه باید این کار را انجام دهم؟

پاسخ دهید

نشانی ایمیل شما منتشر نخواهد شد. بخش‌های موردنیاز علامت‌گذاری شده‌اند *