تابع Lookup در اکسل چیست و ابزار لوکاپ چه کاربردی دارد؟ – به زبان ساده + مثال

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

تابع Lookup در اکسل چیست؟

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

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

انواع روش‌های جستجو با تابع Lookup در اکسل چیست؟

دو حالت زیر برای جستجو با تابع Lookup در جدول داده‌ها وجود دارد.

1. جستجوی برداری یا Vector
2. جستجوی آرایه‌ای یا Array

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

جستجوی برداری با تابع Lookup

در حالت برداری (Vector) محدوده جستجو فقط یک سطر یا یک ستون مشخص در جدول است. به این شکل، تابع Lookup مقدار متناظر با این محدوده را از سطر یا ستون مربوط به‌عنوان نتیجه نشان می‌دهد.

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

=LOOKUP(lookup_value, lookup_vector, [result_vector])

در این ساختار هر یک از آرگومان‌ها به‌ صورت زیر تعریف می‌شوند.

• lookup_value (آرگومان اجباری): مقداری که می‌خواهیم آن را با استفاده از تابع پیدا کنیم. فرمت این آرگومان می‌تواند یک عدد، متن یا حتی مقدار منطقی «False» و «True» باشد.
• lookup_vector (آرگومان اجباری): سطر یا ستونی که می‌خواهیم تابع جستجو را در آن انجام دهد. برای نتیجه صحیح، لازم است داده‌ها به صورت صعودی مرتب شده باشند.
• result_vector (آرگومان اختیاری): سطر یا ستونی که تابع مقدار متناظر جستجو را از آن باز می‌گرداند. اگر این آرگومان را در فرمول تعریف نکنیم، تابع به‌صورت خودکار نتیجه را از سطر یا ستون متناظر با lookup_vector باز می‌گرداند. تعداد سطر و ستون‌های انتخابی در این آرگومان با تعداد سلول‌های انتخاب شده در lookup_vector برابر است.

مثال

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

حال می‌خواهیم قیمت کالایی با کد «P۱۰۳» را پیدا کنیم. در این حالت با نوشتن فرمول =LOOKUP(C8,A2:A5,E2:E5) نتیجه جستجو به شکل تصویر زیر خواهد بود. طبق این فرمول، تابع Lookup ابتدا کد انبار مورد نظر در سلول C8 را در ستون مربوط به کد کالا یعنی A2:A5 جستجو می‌کند. سپس مقدار متناظر با این کد را در ستون قیمت‌ها یعنی E2:E5 به‌عنوان نتیجه می‌نویسد.

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

جستجوی آرایه‌ای با تابع Lookup

از روش آرایه‌ای (Array) تابع Lookup زمانی استفاده می‌کنیم که محدوده سلول‌های مورد نظر برای جستجو در بیش از یک سطر یا ستون قرار داشته باشند. در این حالت تابع ابتدا مقدار مشخص شده را در اولین سطر یا ستون از این محدوده جستجو می‌کند. سپس مقدار متناظر را از آخرین سطر یا ستون بر می‌گرداند. به‌عبارت دیگر بر خلاف حالت برداری در این روش سطر یا ستونی که می‌خواهیم مقدار متناظر با توجه به آن بازگردانده شود، قابل تعیین نیست.

ساختار فرمول در روش جستجوی آرایه‌ای با تابع Lookup به شکل زیر است.

=LOOKUP(lookup_value, array)

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

• lookup_value (آرگومان اجباری): مقداری که می‌خواهیم آن را با استفاده از تابع پیدا کنیم. فرمت این آرگومان می‌تواند یک عدد، متن یا حتی مقدار منطقی «False» و «True» باشد.
• array (آرگومان اجباری): محدوده‌ای شامل چند سطر یا ستون از جدول که می‌خواهیم در اولین سطر یا اولین ستون آن، مقدار lookup_value را پیدا کنیم.

مثال

در فروشگاه فرضی قبلی، این‌بار با استفاده از حالت آرایه‌ای می‌خواهیم قیمت کد کالا را پیدا کنیم. در این حالت فرمول به شکل ساده‌تر =LOOKUP(C8,A1:E5) نوشته می‌شود که در آن آرگومان A1:E5 محدوده جستجو از اولین ستون تا ستونی است که قیمت در آن قرار دارد.

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

یادگیری سریع فرمول‌نویسی با توابع جستجوی اکسل در فرادرس

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

با توجه به تنوع بالای آموزش‌ها، موارد منتخب زیر راهنمای خوبی برای شروع یادگیری است.

• فیلم آموزش استفاده از توابع و فرمول‌نویسی در اکسل همراه گواهینامه در فرادرس
• فیلم آموزش ابزارهای کاربردی اکسل در فرادرس
• فیلم آموزش ترفندهای کاربردی اکسل در فرادرس

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

• مجموعه فیلم آموزش توابع و فرمول در اکسل در فرادرس
• مجموعه فیلم آموزش اکسل کاربردی در فرادرس

توابع جایگزین تابع Lookup در اکسل

به‌جز فرم کلاسیک تابع Lookup که تا این بخش آن‌ را بررسی کردیم، توابع جستجوی دیگری نیز در اکسل تعریف شده‌اند. هر یک از این توابع با توجه به مزیت‌ها و محدودیت‌های خود، کاربردها و ترفندهای خاصی نیز برای رفع مشکلات احتمالی دارند.

فیلم آموزش ترفندهای کاربردی در اکسل Excel + گواهینامه در فرادرس

کلیک کنید

مهم‌ترین این توابع به شرح زیر هستند.

1. تابع VLookup
2. تابع HLookup
3. تابع XLookup

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

تابع VLookup در اکسل

از تابع ویلوکاپ برای جستجوی عمودی (Vertical Lookup) در یک ستون از جدول داده‌ها استفاده می‌شود. در ساختار فرمول این تابع می‌توانیم با نوشتن عبارات «True» یا «False» در آرگومان آخر، نوع جستجو را به حالت تقریبی یا دقیق مشخص کنیم.

مثال

در یک جدول فرضی از اطلاعات نمایندگان فروش یک شرکت می‌خواهیم اسم فردی با نام خانوادگی «سرخوش» را که در سلول B3 قرار دارد، در محدوده B2:E5 را پیدا کنیم. از آنجا که نام خانوادگی افراد در ستون دوم قرار دارد، عدد «۲» را در فرمول می‌نویسم و با نوشتن عبارت «False» روش جستجو را به شکل جستجوی «دقیق» اسم تعیین می‌کنیم.

در مطلب زیر از مجله فرادرس، روش کار با تابع VLookup را به‌طور کامل توضیح داده‌ایم.

تابع HLookup در اکسل

تابع HLookup برای جستجوی افقی (Horizontal Lookup) در اکسل استفاده می‌شود. به‌عبارتی زمانی‌که داده‌های جدول به شکل افقی در یک یا چند ردیف قرار داشته باشند، این تابع برای جستجوی تقریبی یا دقیق به‌کار می‌رود.

مثال

مدیر یک مدرسه فرضی در نظر دارد برای مهمانان ویژه خود در جشن پایان سال تحصیلی تعدادی بسته هدیه تهیه کند. فروشنده این بسته‌ها مطابق جدول زیر با توجه به تعداد خریداری شده تخفیف‌هایی را روی هر بسته ارائه می‌کند.

مدیر می‌خواهد بداند اگر تعداد مهمانان او ۱۴۹ نفر باشد، میزان تخفیف بازای هر نفر و قیمت کل قابل پرداخت چقدر است. طبق جدول، داده‌های مربوط به قیمت در ردیف دوم و محدوده B1:E2 قرار دارند. از آنجا که عدد ۱۴۹ در جدول به شکل دقیق وجود ندارد، روش جستجو تقریبی خواهد بود و به همین دلیل در فرمول تابع HLookup عبارت «True» را به نشانه روش جستجوی تقریبی می‌نویسیم.

در این حالت از آنجا که عدد ۱۴۹ در جدول وجود ندارد، تابع HLookup مقدار متناظر قیمت را از نزدیک‌ترین عدد کوچک‌تر تعداد مهمانان بین دو عدد ۱۰۰ و ۱۵۰ انتخاب می‌کند. بنابراین قیمت هر بسته متناظر با تعداد مهمان «۱۰۰» (عدد ۹۶۰۰۰) را به‌عنوان خروجی نشان می‌دهد.

تابع XLookup در اکسل

تابع XLookup پیشرفته‌ترین ابزار جستجوی اکسل است که در نسخه ۲۰۱۹ این نرم‌افزار و Microsoft 365 معرفی شده است.با این تابع می‌توانیم جستجوی دقیق یا تقریبی به دو شکل افقی یا عمودی را هم‌زمان انجام دهیم.

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

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

فیلم آموزش کاربرد اکسل در حسابداری و مدیریت + گواهینامه در فرادرس

کلیک کنید

مثال

فرض می‌کنیم جدولی شامل اسامی کارکنان شرکت و کد پرسنلی آن‌ها را داریم. با تابع Xlookup به‌راحتی می‌توانیم فرمولی بنویسیم که هر دو ستون مربوط به اسامی و شماره کد پرسنلی را برای نام مورد نظر پیدا کند. همچنین در صورت پیدا نکردن اسم، عبارت «کد پرسنلی مربوط با این نام وجود ندارد» را به‌عنوان خروجی نشان دهد. همان‌طور که در تصویر زیر مشخص است، نام «پریسا» در جدول وجود ندارد و تابع XLookup عبارت مورد نظر را نمایش می‌دهد.

در مطلب زیر ساختار فرمول این تابع را همراه مثال‌های بیشتر توضیح داده‌ایم.

تفاوت توابع V، H و X با تابع Lookup در اکسل چیست؟

انتخاب بهترین تابع جستجو به روش پیدا کردن داده‌ها و دقت انجام کار بستگی دارد. به همین دلیل بررسی تفاوت‌های آن‌ها کمک بسیاری به این موضوع می‌کند. در جدول زیر خلاصه‌ای از ویژگی‌های هر یک از این توابع را برای مقایسه بهتر آورده‌ایم.

در این جدول منظور از مدیریت خطا، خروجی تابع در زمان پیدا نکردن عدد در جدول است. در تابع Lookup احتمال خطای محاسباتی و نتیجه اشتباه بالاتر است. اما در فرمول تابع XLookup به‌صورت پیش‌فرض یک آرگومان مخصوص خطاهای احتمالی در حین محاسبه وجود دارد. به شکلی که می‌توانیم حتی عبارت دلخواه خود برای نمایش در صورت پیدا نکردن عدد در جدول را در فرمول تابع مشخص کنیم.

جمع‌بندی پایانی

تابع Lookup یکی از توابع پایه جستجو در اکسل است که به دلیل محدودیت خود در روش جستجو و امکان‌ناپذیر بودن پیدا کردن دقیق عبارت یا عدد مورد نظر ممکن است در برخی موارد مشکل ایجاد کند. هر چند این تابع برای فعالیت‌های ساده جستجو در اکسل بسیار مناسب است، با این حال توابع جایگزین دیگری نیز وجود دارند که می‌توانیم با استفاده از آن‌ها جستجوی دقیق‌تری را انجام دهیم. در این مطلب از مجله فرادرس یاد گرفتیم که ساختار و انواع روش‌های جستجو با تابع Lookup در اکسل چیست. همچنین مروری بر توابع جایگزین مانند VLookup، HLookup و XLookup داشتیم که برای یادگیری کامل‌تر آن‌ها علاقه‌مندان می‌توانند مجموعه فیلم آموزش توابع اکسل در فرادرس را نیز مشاهده کنند.