آموزش XLOOKUP در اکسل با مثال؛ جایگزین حرفه‌ای VLOOKUP

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

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

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

XLOOKUP چیست؟

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

به زبان ساده، تابع XLOOKUP به اکسل می‌گوید:

این مقدار را در این ستون پیدا کن، بعد مقدار متناظر آن را از ستون دیگر به من بده.

مثلاً:

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

فرمول پایه XLOOKUP در اکسل به این شکل است:

=XLOOKUP(lookup_value, lookup_array, return_array)

یعنی:

=XLOOKUP(مقدار مورد جست‌وجو, محدوده جست‌وجو, محدوده نتیجه)

چرا XLOOKUP بهتر از VLOOKUP است؟

تابع VLOOKUP سال‌ها یکی از پرکاربردترین توابع اکسل بوده، اما چند محدودیت مهم دارد. در مقابل، XLOOKUP این مشکلات را تا حد زیادی حل کرده است.

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

ساختار تابع XLOOKUP در اکسل

ساختار کامل تابع XLOOKUP به شکل زیر است:

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

اجزای این تابع:

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

=XLOOKUP(مقدار, ستون جست‌وجو, ستون نتیجه)

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

فرض کنید جدولی مثل زیر دارید:

حالا می‌خواهید با وارد کردن کد پرسنلی، نام کارمند نمایش داده شود.

اگر کد پرسنلی در سلول E2 وارد شود، فرمول این است:

=XLOOKUP(E2,A2:A4,B2:B4)

معنی فرمول:

• مقدار داخل E2 را بگیر.
• آن را در محدوده A2:A4 جست‌وجو کن.
• اگر پیدا شد، مقدار متناظر از محدوده B2:B4 را نمایش بده.

اگر در E2 عدد 1002 وارد شود، خروجی می‌شود:

سارا محمدی

این یکی از ساده‌ترین کاربردهای آموزش XLOOKUP در اکسل است و برای لیست کارمندان، مشتریان، دانشجویان و محصولات بسیار کاربرد دارد.

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

فرض کنید یک فایل اکسل فروش دارید:content_copy

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

فرمول:

=XLOOKUP(E2,A2:A4,C2:C4)

اگر در سلول E2 مقدار P103 را وارد کنید، نتیجه می‌شود:

6800000

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

مثال ۳: نمایش پیام دلخواه وقتی داده پیدا نشد

یکی از مشکلات رایج در اکسل، نمایش خطای #N/A است. وقتی مقدار موردنظر پیدا نشود، اکسل این خطا را نشان می‌دهد.

با XLOOKUP می‌توانید به جای خطای نامفهوم، یک پیام خوانا نمایش دهید.

مثال:

=XLOOKUP(E2,A2:A4,B2:B4,"کد موردنظر پیدا نشد")

اگر کدی که در E2 وارد کرده‌اید در جدول وجود نداشته باشد، اکسل به جای خطای #N/A این پیام را نمایش می‌دهد:

کد موردنظر پیدا نشد

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

مثال ۴: جست‌وجو از راست به چپ در اکسل

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

اما XLOOKUP این مشکل را حل کرده است.

فرض کنید جدول شما این‌طور است:content_copy

حالا می‌خواهید با وارد کردن کد پرسنلی، نام کارمند را پیدا کنید؛ در حالی که کد پرسنلی در ستون سمت راست قرار دارد.

فرمول:

=XLOOKUP(E2,C2:C4,A2:A4)

در اینجا اکسل مقدار E2 را در ستون C جست‌وجو می‌کند و نتیجه را از ستون A برمی‌گرداند.

این قابلیت یکی از مهم‌ترین دلایلی است که کاربران حرفه‌ای اکسل، XLOOKUP را جایگزین VLOOKUP می‌کنند.

مثال ۵: پیدا کردن اطلاعات کامل یک ردیف

گاهی فقط یک مقدار نمی‌خواهید؛ بلکه می‌خواهید با وارد کردن کد محصول، چند اطلاعات مثل نام محصول، قیمت و موجودی هم‌زمان نمایش داده شود.

فرض کنید جدول شما این است:content_copy

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

=XLOOKUP(E2,A2:A4,B2:D4)

در نسخه‌های جدید اکسل، نتیجه این فرمول در چند سلول کنار هم نمایش داده می‌شود.

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

مثال ۶: جست‌وجوی تقریبی با XLOOKUP

گاهی لازم نیست مقدار دقیق پیدا شود. مثلاً می‌خواهید بر اساس امتیاز کاربر، سطح او را مشخص کنید.content_copy

اگر امتیاز در سلول E2 باشد، فرمول زیر سطح را مشخص می‌کند:

=XLOOKUP(E2,A2:A5,B2:B5,,-1)

در اینجا -1 یعنی اگر مقدار دقیق پیدا نشد، نزدیک‌ترین مقدار کوچک‌تر را انتخاب کن.

مثلاً اگر امتیاز 85 باشد، نتیجه می‌شود:

خوب

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

مثال ۷: استفاده از Wildcard در XLOOKUP

گاهی فقط بخشی از متن را می‌دانید. مثلاً نام کامل مشتری را ندارید و فقط بخشی از نام را وارد می‌کنید.

برای این حالت می‌توانید از Wildcard استفاده کنید.

مثال:

=XLOOKUP("*"&E2&"*",A2:A10,B2:B10,"پیدا نشد",2)

در این فرمول:

• علامت * یعنی هر تعداد کاراکتر قبل یا بعد از متن می‌تواند وجود داشته باشد.
• عدد 2 در بخش match_mode یعنی جست‌وجو با Wildcard فعال شود.

اگر در E2 بنویسید رضایی، اکسل می‌تواند عبارتی مثل احمد رضایی را در جدول پیدا کند.

این روش برای جست‌وجوی نام مشتری، کالا، شهر، عنوان پروژه یا توضیحات سفارش بسیار مفید است.

مثال ۸: جست‌وجو از پایین به بالا

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

مثلاً در لیست سفارش‌ها، یک مشتری چند بار خرید کرده و شما می‌خواهید آخرین وضعیت سفارش او را ببینید.

فرمول:

=XLOOKUP(E2,A2:A20,C2:C20,"پیدا نشد",0,-1)

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

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

تفاوت XLOOKUP و VLOOKUP با مثال ساده

فرض کنید می‌خواهید با کد محصول، قیمت محصول را پیدا کنید.

در VLOOKUP معمولاً باید بنویسید:

=VLOOKUP(E2,A2:C4,3,FALSE)

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

اما در XLOOKUP می‌نویسید:

=XLOOKUP(E2,A2:A4,C2:C4)

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

خطاهای رایج در استفاده از XLOOKUP

۱. یکسان نبودن اندازه محدوده‌ها

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

اشتباه:

=XLOOKUP(E2,A2:A10,B2:B20)

در اینجا محدوده اول ۹ سلول دارد اما محدوده دوم ۱۹ سلول است.

درست:

=XLOOKUP(E2,A2:A10,B2:B10)

۲. تفاوت نوع داده‌ها

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

در این حالت ممکن است XLOOKUP مقدار را پیدا نکند.

راه‌حل‌ها:

• فرمت ستون‌ها را یکسان کنید.
• فاصله‌های اضافی را حذف کنید.
• در صورت نیاز از توابعی مثل VALUE یا TEXT استفاده کنید.

۳. وجود فاصله اضافی در متن

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

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

=TRIM(A2)

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

کاربردهای XLOOKUP در کارهای اداری

تابع XLOOKUP در اکسل فقط یک فرمول آموزشی نیست؛ در کارهای روزانه اداری واقعاً زمان شما را کم می‌کند.

کاربردهای مهم آن:

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

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

چه زمانی از XLOOKUP استفاده کنیم؟

از XLOOKUP زمانی استفاده کنید که:

• می‌خواهید یک مقدار را در جدول پیدا کنید.
• می‌خواهید اطلاعات مرتبط با آن مقدار را برگردانید.
• با چند جدول متفاوت کار می‌کنید.
• می‌خواهید خطای #N/A را بهتر مدیریت کنید.
• می‌خواهید فرمولی حرفه‌ای‌تر از VLOOKUP داشته باشید.
• ترتیب ستون‌های جدول ممکن است تغییر کند.
• نیاز به جست‌وجو از راست به چپ دارید.

در بیشتر فایل‌های کاری جدید، بهتر است به جای VLOOKUP از XLOOKUP استفاده کنید؛ البته به شرطی که نسخه اکسل شما از این تابع پشتیبانی کند.

آیا XLOOKUP در همه نسخه‌های اکسل وجود دارد؟

تابع XLOOKUP در نسخه‌های جدید اکسل ارائه شده است، از جمله:

• Microsoft 365
• Excel 2021
• Excel 2024
• نسخه‌های جدید اکسل تحت وب

اگر از نسخه‌های قدیمی‌تر مثل Excel 2016 یا Excel 2019 استفاده می‌کنید، ممکن است تابع XLOOKUP در فایل شما فعال نباشد. در این حالت می‌توانید از ترکیب توابعی مثل INDEX و MATCH یا همان VLOOKUP استفاده کنید.

جمع‌بندی: چرا باید XLOOKUP را یاد بگیریم؟

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

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

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

سوالات متداول درباره XLOOKUP در اکسل

XLOOKUP چیست؟

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

تفاوت XLOOKUP و VLOOKUP چیست؟

XLOOKUP محدودیت‌های VLOOKUP را کمتر دارد. می‌تواند از راست به چپ جست‌وجو کند، نیازی به شماره ستون ندارد و امکان نمایش پیام دلخواه هنگام پیدا نشدن داده را فراهم می‌کند.

آیا XLOOKUP بهتر از VLOOKUP است؟

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

چرا XLOOKUP در اکسل من کار نمی‌کند؟

احتمالاً نسخه اکسل شما قدیمی است. تابع XLOOKUP در Microsoft 365، Excel 2021 و نسخه‌های جدیدتر وجود دارد.

آیا XLOOKUP برای کارهای حسابداری مناسب است؟

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

پیشنهاد آنی‌لرن

اگر می‌خواهید اکسل را فقط در حد فرمول‌های پراکنده یاد نگیرید و واقعاً بتوانید در محیط کار از آن استفاده کنید، بهتر است روی توابع کاربردی مثل XLOOKUP، VLOOKUP، IF، SUMIFS، COUNTIFS و ساخت گزارش‌های حرفه‌ای مسلط شوید.

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

نویسنده آنی لرن 1 دنبال کننده