آموزش تابع XLOOKUP؛ جایگزین قدرتمند VLOOKUP در اکسل

تابع XLOOKUP در اکسل چیست؟ آموزش کامل

تصور کنید در یک شرکت بزرگ کار می‌کنید و مدیرتان از شما می‌خواهد که از بین لیستی با ۱۰,۰۰۰ ردیف اطلاعات پرسنلی، حقوق ۵۰ کارمند خاص را پیدا کنید. اگر بخواهید از VLOOKUP استفاده کنید، شاید با خطای #N/A مواجه شوید، یا بدتر—یکی از همکاران یک ستون جدید به فایل اضافه می‌کند و تمام فرمول‌های شما از کار می‌افتند. اینجاست که تابع XLOOKUP در اکسل به کمک شما می‌آید و این کار طاقت‌فرسا را به یک عملیات ساده، سریع و مقاوم در برابر تغییرات تبدیل می‌کند.

مایکروسافت در سال ۲۰۱۹ تابع XLOOKUP را به عنوان جانشین مدرن VLOOKUP معرفی کرد؛ تابعی که تمام محدودیت‌های ۳۵ سالهٔ VLOOKUP را برطرف کرده است. XLOOKUP نه‌تنها جایگزین VLOOKUP است، بلکه جایگزین HLOOKUP و حتی ترکیب پیچیدهٔ INDEX و MATCH نیز محسوب می‌شود. این تابع در Excel ۲۰۲۱، Excel ۲۰۲۴ و Microsoft ۳۶۵ در دسترس است و در Excel ۲۰۱۹ و نسخه‌های قدیمی‌تر وجود ندارد.

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

-50%

افزودن به علاقه مندی

افزودن به سبد خرید

دوره آموزش Excel

۹۹۰,۰۰۰ تومان قیمت اصلی ۹۹۰,۰۰۰ تومان بود.۴۹۵,۰۰۰ تومانقیمت فعلی ۴۹۵,۰۰۰ تومان است.

-50%

افزودن به علاقه مندی

افزودن به سبد خرید

دوره آموزش office

۱,۴۹۰,۰۰۰ تومان قیمت اصلی ۱,۴۹۰,۰۰۰ تومان بود.۷۴۵,۰۰۰ تومانقیمت فعلی ۷۴۵,۰۰۰ تومان است.

-50%

افزودن به علاقه مندی

افزودن به سبد خرید

دوره آموزش word

۹۹۰,۰۰۰ تومان قیمت اصلی ۹۹۰,۰۰۰ تومان بود.۴۹۵,۰۰۰ تومانقیمت فعلی ۴۹۵,۰۰۰ تومان است.

-50%

افزودن به علاقه مندی

افزودن به سبد خرید

دوره آموزش پاورپوینت

۹۹۰,۰۰۰ تومان قیمت اصلی ۹۹۰,۰۰۰ تومان بود.۴۹۵,۰۰۰ تومانقیمت فعلی ۴۹۵,۰۰۰ تومان است.

-50%

افزودن به علاقه مندی

افزودن به سبد خرید

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

۳۹۰,۰۰۰ تومان قیمت اصلی ۳۹۰,۰۰۰ تومان بود.۱۹۵,۰۰۰ تومانقیمت فعلی ۱۹۵,۰۰۰ تومان است.

-50%

افزودن به علاقه مندی

افزودن به سبد خرید

دوره آموزش مبانی کار با ویندوز

۵۹۰,۰۰۰ تومان قیمت اصلی ۵۹۰,۰۰۰ تومان بود.۲۹۵,۰۰۰ تومانقیمت فعلی ۲۹۵,۰۰۰ تومان است.

-50%

افزودن به علاقه مندی

افزودن به سبد خرید

دوره جامع کامپیوتر

۱,۹۸۰,۰۰۰ تومان قیمت اصلی ۱,۹۸۰,۰۰۰ تومان بود.۹۹۰,۰۰۰ تومانقیمت فعلی ۹۹۰,۰۰۰ تومان است.

افزودن به علاقه مندی

افزودن به سبد خرید

مینی دوره آموزشی رایگان کامپیوتر

۰ تومان

تابع XLOOKUP چیست و چه تفاوتی با VLOOKUP دارد؟

تابع XLOOKUP مخفف عبارت Extended Lookup به معنای «جستجوی گسترده» است. این تابع در یک محدوده (آرایه) به دنبال یک مقدار مشخص می‌گردد و مقدار متناظر با آن را از محدودهٔ دیگری برمی‌گرداند. برخلاف VLOOKUP که فقط می‌تواند در جهت عمودی و از چپ به راست جستجو کند، XLOOKUP در هر جهتی—چپ، راست، بالا و پایین—قابلیت جستجو دارد.

به زبان ساده‌تر، فرض کنید یک دفترچه تلفن دارید که در ستون اول آن نام افراد و در ستون دوم شماره تلفن آن‌ها نوشته شده است. با VLOOKUP فقط می‌توانید نام را جستجو کنید و شماره تلفن را پیدا کنید. اما با XLOOKUP می‌توانید شماره تلفن را جستجو کنید و نام فرد را نیز پیدا کنید—چیزی که با VLOOKUP غیرممکن بود مگر با بازآرایی کل جدول.

جدول مقایسهٔ سریع XLOOKUP و VLOOKUP

ساختار تابع XLOOKUP به زبان ساده

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

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

بیایید هر کدام را به زبان ساده بررسی کنیم:

۱. lookup_value (مقدار جستجو) – اجباری

این همان مقداری است که می‌خواهید آن را جستجو کنید. مثلاً کد محصول، شماره پرسنلی یا نام مشتری. این مقدار می‌تواند یک عدد، متن، تاریخ یا آدرس یک سلول باشد.

۲. lookup_array (آرایه جستجو) – اجباری

این محدوده‌ای است که اکسل باید در آن جستجو کند. نکته مهم: برخلاف VLOOKUP، اینجا فقط ستون یا ردیفی که مقدار جستجو در آن قرار دارد را وارد می‌کنید، نه کل جدول. مثلاً اگر کد محصول در ستون A قرار دارد، آرایه جستجو A2:A100 خواهد بود.

۳. return_array (آرایه بازگشتی) – اجباری

این محدوده‌ای است که مقدار نتیجه از آن استخراج می‌شود. مثلاً اگر قیمت محصول در ستون C قرار دارد، آرایه بازگشتی C2:C100 خواهد بود. مزیت بزرگ: این ستون می‌تواند در سمت چپ ستون جستجو نیز باشد—چیزی که در VLOOKUP غیرممکن بود.

۴. if_not_found (مقدار در صورت عدم یافتن) – اختیاری

اگر هیچ مقدار منطبقی پیدا نشود، این مقدار نمایش داده می‌شود. دیگر نیازی به تابع IFERROR ندارید. مثلاً "پیدا نشد".

۵. match_mode (حالت تطابق) – اختیاری

این آرگومان نوع تطابق را مشخص می‌کند:

۶. search_mode (حالت جستجو) – اختیاری

این آرگومان جهت جستجو را مشخص می‌کند:

آموزش گام‌به‌گام XLOOKUP با مثال عملی

حالا که با ساختار تابع آشنا شدیم، بیایید یک مثال واقعی را قدم‌به‌قدم بررسی کنیم.

سناریو: پیدا کردن قیمت محصول با کد کالا

فرض کنید یک جدول محصولات دارید:

مرحله ۱: روی سلول E2 کلیک کنید (سلولی که می‌خواهید قیمت در آن نمایش داده شود).

مرحله ۲: فرمول زیر را تایپ کنید:

=XLOOKUP(D2, A2:A5, C2:C5, "پیدا نشد")

مرحله ۳: تجزیه و تحلیل فرمول:

مرحله ۴: با فشردن کلید Enter، اگر کد کالا در سلول D2 موجود باشد، قیمت آن محصول در سلول E2 نمایش داده می‌شود.

مثال تکمیلی: بازگرداندن چند ستون با یک فرمول

یکی از مزایای بزرگ XLOOKUP نسبت به VLOOKUP، قابلیت بازگرداندن چند ستون به‌طور همزمان است. کافی است آرایه بازگشتی را به‌صورت یک محدوده چندستونی وارد کنید:

=XLOOKUP(D2, A2:A5, B2:C5, "پیدا نشد")

این فرمول هم نام محصول (ستون B) و هم قیمت (ستون C) را به‌طور همزمان برمی‌گرداند. خروجی به‌صورت خودکار در سلول‌های مجاور پخش می‌شود (Spill).

تفاوت جستجوی دقیق و تقریبی در XLOOKUP

یکی از مهم‌ترین مفاهیم در تابع xlookup در اکسل، درک تفاوت بین حالت‌های مختلف تطابق است.

جستجوی دقیق (پیش‌فرض – match_mode = ۰)

در این حالت، اکسل فقط مقدارهایی را پیدا می‌کند که دقیقاً با مقدار جستجو مطابقت داشته باشند. اگر هیچ تطابقی پیدا نشود، مقدار if_not_found (یا #N/A در صورت عدم تعیین) نمایش داده می‌شود.

جستجوی تقریبی (match_mode = -۱ یا ۱)

در این حالت، اگر مقدار دقیق پیدا نشود، اکسل نزدیک‌ترین مقدار کوچک‌تر (match_mode = -۱) یا نزدیک‌ترین مقدار بزرگ‌تر (match_mode = ۱) را برمی‌گرداند.

جستجو با الگو (match_mode = ۲)

با این حالت می‌توانید از کاراکترهای عام (Wildcard) مانند * و ? استفاده کنید. مثلاً برای پیدا کردن همه محصولاتی که با «دفتر» شروع می‌شوند:

=XLOOKUP("دفتر*", A2:A100, B2:B100, "پیدا نشد", 2)

جدول مقایسه حالت‌های تطابق

جستجوی چندشرطی با XLOOKUP (روش‌های پیشرفته)

یکی از قدرتمندترین قابلیت‌های تابع xlookup در اکسل، امکان جستجو بر اساس چندین شرط به‌طور همزمان است. برخلاف VLOOKUP که برای جستجوی چندشرطی به ستون کمکی نیاز دارد، XLOOKUP این کار را به‌صورت مستقیم انجام می‌دهد.

روش اول: استفاده از منطق بولی (AND)

فرض کنید می‌خواهید فروش یک محصول خاص در یک تاریخ خاص را پیدا کنید. کافی است شرط‌ها را با عملگر * (ضرب) ترکیب کنید:

=XLOOKUP(1, (A2:A100="محصول A")*(B2:B100="1404/01/15"), C2:C100, "پیدا نشد")

در این فرمول، دو آرایه بولی (True/False) در هم ضرب می‌شوند و آرایه‌ای تولید می‌کنند که فقط در ردیفی که هر دو شرط برقرار است، مقدار ۱ دارد. سپس XLOOKUP به دنبال مقدار ۱ می‌گردد و مقدار متناظر از ستون C را برمی‌گرداند.

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

روش دیگر ترکیب چند ستون با استفاده از عملگر & و جستجوی مقدار ترکیبی است:

=XLOOKUP(F2&G2, A2:A100&B2:B100, C2:C100, "پیدا نشد")

جدول مقایسه روش‌های جستجوی چندشرطی

مدیریت خطاها در XLOOKUP

یکی از مزایای بزرگ تابع xlookup در اکسل، مدیریت خطای داخلی است. برخلاف VLOOKUP که برای مدیریت خطای #N/A نیاز به تابع IFERROR دارد، XLOOKUP این کار را به‌صورت داخلی انجام می‌دهد.

=XLOOKUP(A2, B2:B100, C2:C100, "یافت نشد")

در این فرمول، اگر مقدار A2 در ستون B پیدا نشود، به‌جای خطای #N/A، عبارت «یافت نشد» نمایش داده می‌شود.

نمونه‌های مختلف استفاده از if_not_found

خطاهای رایج در XLOOKUP و راه‌حل آن‌ها

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

خطای #NAME? (در نسخه‌های قدیمی)

دلیل: نسخهٔ اکسل شما از XLOOKUP پشتیبانی نمی‌کند (Excel ۲۰۱۹ یا قدیمی‌تر).

راه‌حل: از VLOOKUP یا ترکیب INDEX و MATCH استفاده کنید، یا نسخهٔ اکسل خود را ارتقا دهید.

خطای #VALUE!

دلیل: اندازهٔ آرایهٔ جستجو و آرایهٔ بازگشتی با هم برابر نیست.

راه‌حل: مطمئن شوید که lookup_array و return_array دقیقاً هم‌اندازه باشند.

خطای #SPILL!

دلیل: فضای کافی برای پخش (Spill) نتایج وجود ندارد.

راه‌حل: سلول‌های اطراف را خالی کنید تا نتایج بتوانند پخش شوند.

خطای #N/A با وجود مقدار موجود

دلیل: عدم تطابق نوع داده (متن/عدد) یا وجود فاصله‌های اضافی.

راه‌حل: نوع داده‌ها را یکسان کنید و از تابع TRIM برای حذف فاصله‌های اضافی استفاده کنید.

جدول خلاصه خطاهای رایج XLOOKUP

مقایسهٔ سرعت و عملکرد XLOOKUP با VLOOKUP

یکی از سؤالات رایج کاربران این است: «آیا XLOOKUP از VLOOKUP سریع‌تر است؟» پاسخ صادقانه این است: بستگی دارد!

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

تحلیل نتایج:

۱. در حالت Exact Match ساده، VLOOKUP می‌تواند تا ۴۰٪ سریع‌تر از XLOOKUP باشد.

۲. اما XLOOKUP با Binary Search (search_mode=۲) می‌تواند بیش از ۲ برابر سریع‌تر از XLOOKUP معمولی باشد. برای استفاده از این حالت، داده‌های ستون جستجو باید مرتب‌شده باشند.

۳. در دیتاست‌های کوچک (کمتر از ۱۰۰۰ ردیف)، تفاوت سرعت اصلاً محسوس نیست.

کاربردهای عملی تابع XLOOKUP در مشاغل مختلف

تابع xlookup در اکسل فقط یک ابزار تئوری نیست؛ بلکه کاربردهای عملی بسیار متنوعی در محیط‌های کاری واقعی دارد.

مثال ۱: کاربرد در حسابداری

=XLOOKUP(A2, فاکتورها!$A$2:$A$1000, فاکتورها!$C$2:$C$1000, "فاکتور یافت نشد")

مثال ۲: کاربرد در انبارداری

=XLOOKUP(B2, انبار!$A$2:$A$500, انبار!$D$2:$D$500, "کالا یافت نشد")

مثال ۳: کاربرد در منابع انسانی

=XLOOKUP(C2, کارکنان!$A$2:$A$200, کارکنان!$B$2:$B$200, "کارمند یافت نشد")

مثال ۴: جستجوی دوطرفه (Two-Way Lookup)

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

=XLOOKUP(H2, B1:E1, XLOOKUP(G2, A2:A9, B2:E9))

این فرمول ابتدا ردیف مورد نظر (G2) را در ستون A پیدا می‌کند و سپس ستون مورد نظر (H2) را در ردیف اول جستجو می‌کند.

مهاجرت از VLOOKUP به XLOOKUP: راهنمای عملی

اگر تصمیم گرفته‌اید به XLOOKUP مهاجرت کنید، این جدول تبدیل می‌تواند به شما کمک کند:

نکته مهم: اگر فایل خود را با کاربرانی که از Excel ۲۰۱۹ یا قدیمی‌تر استفاده می‌کنند به اشتراک می‌گذارید، آن‌ها خطای #NAME? خواهند دید. در این صورت، استفاده از VLOOKUP همچنان گزینهٔ مطمئن‌تری است.

جمع‌بندی

تابع xlookup در اکسل یکی از قدرتمندترین و انعطاف‌پذیرترین توابع جستجو است که تمام محدودیت‌های VLOOKUP را برطرف کرده است. این تابع در هر جهتی جستجو می‌کند، به‌طور پیش‌فرض تطابق دقیق دارد، خطاها را به‌صورت داخلی مدیریت می‌کند و در برابر تغییرات ساختار جدول مقاوم است.

نکات کلیدی این مقاله:

• XLOOKUP جایگزین مدرن VLOOKUP، HLOOKUP و INDEX/MATCH است

• ساختار شش آرگومانی آن انعطاف‌پذیری بالایی ارائه می‌دهد

• جستجوی چندشرطی بدون نیاز به ستون کمکی امکان‌پذیر است

• مدیریت خطای داخلی (if_not_found) جایگزین IFERROR شده است

• در Excel ۲۰۲۱ و Microsoft ۳۶۵ در دسترس است

• در دیتاست‌های بزرگ با Binary Search می‌تواند بسیار سریع‌تر از VLOOKUP عمل کند

توصیه نهایی: اگر از نسخه‌های جدید اکسل استفاده می‌کنید، یادگیری تابع xlookup در اکسل را در اولویت قرار دهید. اگر با نسخه‌های قدیمی‌تر کار می‌کنید، VLOOKUP همچنان ابزار ارزشمندی است. بهترین رویکرد این است که هر دو تابع را بلد باشید تا در هر شرایطی بتوانید بهترین انتخاب را داشته باشید.

سوالات متداول

۱. تابع XLOOKUP در چه نسخه‌هایی از اکسل وجود دارد؟

XLOOKUP فقط در Microsoft ۳۶۵، Excel ۲۰۲۱ و Excel ۲۰۲۴ در دسترس است. در Excel ۲۰۱۹ و نسخه‌های قدیمی‌تر وجود ندارد.

۲. تفاوت اصلی XLOOKUP و VLOOKUP چیست؟

XLOOKUP در هر جهتی جستجو می‌کند (چپ، راست، بالا، پایین)، به‌طور پیش‌فرض تطابق دقیق دارد، خطاها را به‌صورت داخلی مدیریت می‌کند و در برابر اضافه/حذف ستون مقاوم است. VLOOKUP فقط چپ به راست جستجو می‌کند و با تغییر ساختار جدول می‌شکند.

۳. آیا XLOOKUP از VLOOKUP سریع‌تر است؟

در حالت Exact Match ساده، VLOOKUP می‌تواند سریع‌تر باشد. اما XLOOKUP با Binary Search (search_mode=۲) می‌تواند بسیار سریع‌تر عمل کند. در دیتاست‌های کوچک، تفاوت محسوس نیست.

۴. چگونه از XLOOKUP برای جستجوی چندشرطی استفاده کنم؟

با استفاده از منطق بولی: =XLOOKUP(1, (A:A="شرط۱")*(B:B="شرط۲"), C:C, "پیدا نشد")

۵. آیا XLOOKUP با WPS Office هم کار می‌کند؟

بله، نسخه‌های جدید WPS Office از XLOOKUP پشتیبانی می‌کنند. اگر در WPS تابع XLOOKUP را در دسترس ندارید، ممکن است نیاز به ارتقا به آخرین نسخه داشته باشید.

۶. چگونه خطای #NAME? را در XLOOKUP برطرف کنم؟

این خطا نشان می‌دهد که نسخهٔ اکسل شما از XLOOKUP پشتیبانی نمی‌کند. نسخهٔ خود را به Microsoft ۳۶۵ یا Excel ۲۰۲۱ ارتقا دهید، یا از VLOOKUP استفاده کنید.