آموزش کامل و جامع تابع XLOOKUP 2021

تابع XLOOKUP

تابع XLOOKUP

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

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

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

توجه: تابع XLOOKUP در اکسل ورژن 2016 و 2019 وجود ندارد. با توجه به اینگه از مدت ها پیش بحث در مورد تابع XLOOKUP مطرح بود و برخی نویسندگان در کتاب ها یا سایت ها از این تابع و نحوه عملکرد آن صحبت کرده بودند و گهگاه آموزش هایی را ارائه کرده بودند.

Syntax یا شیوه:

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

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

آرگومان اول lookup_value: (اجباری)

مقداری که باید جستجو شود. معادل همین آرگومان در vlookup هم هست.

آرگومان دوم lookup_array: (اجباری)

آرایه یا رنجی که باید در آن جستجو شود و مقدار آرگومان اول در این محدوده جستجو می شود.

آرگومان سوم return_array: (اجباری)

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

آرگومان چهارم [if_not_found]: (اختیاری)

اگر مقدار آرگومان اول در رنج داده شده یافت نشد، چه مقداری برگردانده شود. که می توان یک متن مانند “موجود نیست” یا “یافت نشد” باشد یا یک عملیات دیگر مانند: یک تابه XLOOKUP دیگر که در ویدئو به صورت کامل توضیح داده شده است.

آرگومان پنجم [match_mode]: (اختیاری)

مشخص کردن نوع تشخیص.

این آرگومان چهار مقدار می گیرد:

0: همان مطابقت دقیق یا Exact match در تابع VLOOKUP است که مقدار را دقیق و با مطابقت کامل پیدا می کند.

1: مطابقت دقیق با این تفاوت که اگر یافت نشد مقدار کوچکتر بعدی را بر می گرداند.

-1: مطابقت دقیق با این تفاوت که اگر یافت نشد مقدار بزرگتر بعدی را بر می گرداند.

2: مطابقت با وایلد کارت ها: ؟ *

توجه: برای درک بهتر ویدئو را کامل ببینید

آرگومان ششم [search_mode]: (اختیاری)

مشخص کردن شیوه جستجو

این آرگومان چهار مقدار می گیرد:

1: مقدار پیش فرض، لیست را از ابتدا تا انتها می گردد

-1: جستجوی معکوس، لیست را از انتها تا ابتدا می گردد.

2: نوع جستجو متکی به ترتیب صعودی می شود. در صورت مرتب سازی نکردن، نتایج نامعتبر بازگردانده می شود.

-2: نوع جستجو متکی به ترتیب نزولی می شود. در صورت مرتب سازی  نکردن، نتایج نامعتبر بازگردانده می شود.

مثال یک:

در جدول بالا؛ می خواهیم با توجه به “شماره پرسنلی” ابتدا سمت کارمند موردنظر را برگردانیم.

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

=XLOOKUP(C14,D2:D11,E2:E11)

ستون نامو نام خانوادگی قبل از ستون شماره پرسنلی قرار دارد، اما برای تابع XLOOKUP تفاوتی ندارد و با فرمول های زیر به ترتیب نام و نام خانوادگی بر می گردد:

=XLOOKUP(C14,D2:D11,B2:B11)

=XLOOKUP(C14,D2:D11,C2:C11)

مثال دو:

در جدول بالا با کمک تابع XLOOKUP یک آرایه را برمی گردانیم.

با توجه به نام و نام خانوادگی و با یک تابع XLOOKUP تمامی نمره ها را بر می گردانیم.

نکته: توجه داشته باشد که در این نمونه، تابع XLOOKUP را در D16 نوشته ایم، یعنی در خانه نمره مبانی و بقیه نمره ها به ترتیب در خانه های خود قرار گرفته اند، این بدان معنا است که در سلول های کناری مقداری وجود ندارد و آنها وابسته به خانه D16 هستند. اگر محتوای خانه D16 پاک شوند همه نمره های دیگر پام خواهد شد.

مثال سه: اگر پیدا نشد!

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

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

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