تابع LOOKUP در اکسل

تابع LOOKUP جزو توابع جست‌وجوی اکسل است. این تابع یک مقدار را از یک سطر یا ستون و یا یک جدول جست‌وجو می­‌کند و جواب مربوطه را از یک سطر یا ستون معین دیگر برمی­‌گرداند.

◄ Lookup در زبان انگلیسی به معنای جست‌وجو است.

‌

این تابع دارای دو حالت برداری (Vector) و آرایه‌ای (Array) می‌باشد.

به زبان ساده به یک سطر یا ستون بردار می‌گویند و به چند سطر یا ستون آرایه!

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

در زیر هر دو حالت به صورت جداگانه بررسی شده است.

حالت برداری تابع Lookup

آرگومان‌ها یا پارامترهای ورودی

‎=LOOKUP( lookup_value, lookup_vector, [result_vector] )‎

آرگومان اول (lookup_value): مقدار یا ارزشی که قرار است جست‌وجو شود و می‌تواند بصورت عدد، متن، تاریخ، آدرس سلول و … باشد.

آرگومان دوم (lookup_vector): این آرگومان بیانگر بردار یا محدوده‌ی محل جست‌وجو است و باید بصورت یک سطر یا ستون باشد. مقدار آرگومان اول از درون این بردار یا محدوده جست‌وجو می‌شود.

آرگومان سوم ([result_vector]): این آرگومان بردار یا محدوده‌ی است که می‌خواهید جواب جست‌وجو از آن انتخاب شود. به عبارت دیگر این آرگومان بیانگر محل نتیجه‌ی جست‌وجو است و باید همانند آرگومان دوم بصورت سطر یا ستون باشد.

نمایی از تابع Lookup به صورت شماتیک

مثال‌ها

پیدا کردن نام براساس کد پرسنلی

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

فرمول سلول I2:

‎=LOOKUP( H2 , C2:C5 , D2:D5 )‎

ترجمه‌ی فارسی تابع فوق:

اکسل برو محتویات سلول H2 را (که در آن مقدار 1201 وجود دارد) از ناحیه C2:C5 بگرد و جواب را از ناحیه D2:D5 برای ما نمایش بده!

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

اگر همین فرمول را برای بدست آوردن سمّت بنویسیم، به صورت زیر خواهد بود:

‎=LOOKUP( H2 , C2:C5 , E2:E5 )‎

اگر دیتابیس مثال فوق به صورت افقی مطرح شود، برای حل این مسئله خواهیم داشت:

فرمول سلول J2:

‎=LOOKUP( I2 , D1:G1 , D2:G2 )‎

نکات مهم

نکته 1) مقدار آرگومان دوم (lookup_vector) باید به صورت صعودی مرتب شده باشد، یعنی از بزرگ به کوچک یا از A تا Z؛ در غیر این صورت فرمول تابع شما ممکن است یک خطا یا نتیجه‌ی نادرست برگرداند. چنان‌چه می‌خواهید جست‌وجو را بر روی داده‌های نامرتب انجام دهید، از تابع‌های INDEX و MATCH یا OFFSET و MATCH استفاده کنید.

نکته 2) مقدار آرگومان دوم (lookup_vector) و سوم (result_vector) باید به صورت یک سطر یا یک ستون بوده و هم‌اندازه باشند.
‌نکته 3) بزرگ یا کوچک بودن حروف در جست‌وجوی این تابع مهم نیستند. مانند: “Reza” = “reza”

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

برای مثال اگر شما مقدار “5” را جست‌وجو کنید، تابع ابتدا این مقدار را جست‌وجو خواهد کرد. اگر مقدار “5” پیدا نشد، مقدار “4” را جست‌وجو می‌کند. اگر مقدار “4” پیدا نشد، مقدار “3” را جست‌وجو می‌کند و به همین ترتیب… .

اگر مقدار آرگومان اول (lookup_value) از کوچک‌ترین مقدار آرگومان دوم (lookup_vector) کوچک‌تر باشد، تابع خطای ‎#N/A را برمی‌گرداند.

نکته 5) این تابع مقدار تکراری ­را پیدا نمی­‌کند و در صورت تکراری بودن، اولین مقدار را نمایش می­‌دهد.

حالت آرایه‌ای تابع Lookup

توجە:

برای جست‌وجو به صورت آرایه‌ای بهتر است از توابع جایگزین LOOKUP یعنی VLOOKUP یا HLOOKUP که رایج‌تر و به نسبت ساده‌تر هستند، استفاده شود.

• برای آشنایی بیشتر با تابع Vlookup این‌جا کلیک کنید.
• برای آشنایی بیشتر با تابع Hlookup این‌جا کلیک کنید.

آرگومان‌ها یا پارامترهای ورودی

‎=LOOKUP( lookup_value, array )‎

آرگومان اول (lookup_value): مقدار یا ارزشی که قرار است جست‌وجو شود و می‌تواند بصورت عدد، متن، تاریخ، آدرس سلول و … باشد.

آرگومان دوم (lookup_vector): این آرگومان بیانگر محل جست‌وجو است و باید بصورت یک جدول باشد که حکم دیتابیس را دارد. مقدار آرگومان اول از درون این جدول جست‌وجو می‌شود.

در شکل آرایه‌ای، یک جدول در آرگومان دوم داریم که آرگومان اول (lookup_value) از اولین سطر یا ستون آن جست‌وجو می‌شود و نتیجه همیشه از آخرین سطر یا ستون نشان داده می­‌شود.

توجه: تمامی نکاتی که در شکل برداری بیان شد شامل شکل آرایه‌­ای نیز می‌شود.

نکته: در صورتی که تعداد ستون­‌های جدول بیشتر از تعداد سطرهای آن باشد، تابع به صورت سطری عمل خواهد کرد. یعنی اولین سطر را به عنوان بردار جست‌وجو انتخاب می ‌کند؛ و در صورتی که تعداد سطرهای جدول بیشتر از تعداد ستون‌های آن باشد، تابع به صورت ستونی عمل می­ کند، یعنی اولین ستون را به عنوان بردار جست‌وجو انتخاب می‌کند.

نمایی از تابع Lookup به صورت شماتیک

مثال‌ها

پیدا کردن حقوق براساس کد پرسنلی

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

فرمول سلول I2:

‎=LOOKUP( H2 , C2:F5 )‎

ترجمه‌ی فارسی تابع فوق:

اکسل برو محتویات سلول H2 را (که در آن مقدار 1201 وجود دارد) از ستون اول ناحیه C2:F5 بگرد و جواب را از ستون آخر (یعنی F2:F5) برای ما نمایش بده!

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

خطاهای تابع

خطای !REF#: این خطا زمانی رخ می‌دهد که فرمول تابع بە سلول‌هایی ارجاع می‌کند که وجود ندارد.

این حالت می‌تواند به دلایل زیر باشد:

• سلول‌ها بعد از این که تابع Lookup وارد شده‌ است، حذف شده‌اند.

یا

• ارجاع‌های نسبی در تابع Lookup هنگام کپی از سلولی به سلولی دیگر، نامعتبر شده است.

خطای N/A#: این خطا به معنای موجود نبودن (Not Available) یا پیدا نکردن است و زمانی نمایش داده می‌شود که مقداری که در حال جست‌وجوی آن هستیم، موجود نباشد.

این حالت می‌تواند به دلایل زیر باشد:

• کوچک‌ترین مقدار موجود در lookup_vector (ردیف یا ستون اول بردار) از مقدار مورد جست‌وجوی ما بیشتر باشد.

یا

• lookup_vector (ردیف یا ستون اول بردار) به صورت صعودی مرتب نشده باشد.