فرمول hlookup در اکسل

همانطور که احتمالاً می دانیداکسل برای جستجوی یک مقدار، سه فرمول LOOKUP ،VLOOKUP و HLOOKUP را در اختیار کاربران قرار می دهد. قبلا با فرمول VLOOKUP در اکسل آشنا شدید. در این آموزش قصد داریم به بررسی فرمول HLOOKUP بپردازیم.

فرمول HLOOKUP مخفف “Horizontal lookup” است و همانطور که از نامش پیداست جستجو را به صورت افقی انجام می دهد.

در ابتدا بیایید به ساختار و آرگومان های این تابع نگاهی بیندازیم:

HLOOKUP(lookup_value, table_array, row_index_num, Range_Lookup)

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

داده های جدول زیر را در نظر بگیرید. می خواهیم با توجه به تعرفه بالای جدول، رتبه هر شخص را از A تا F مشخص کنیم. تابع HLOOKUP به راحتی این کار را انجام می دهد.

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

۱- درج فرمول HLOOKUP در اکسل

برای دسترسی به تابع HLOOKUP در سلول G6 علامت = و سپس چند حرف از این تابع را تایپ می کنیم. سپس با نشان کردن تابع مورد نظر و فشردن کلید Tab فرمول را آماده دریافت آرگومان ها می کنیم.
همچنین می توانیم در تب Formulas، گروه Lookup & Reference را انتخاب و روی HLOOKUP کلیک کنیم. با این کار پنجره‌ای برای وارد کردن آرگومان ها ظاهر می شود.

۲- آرگومان اول (lookup_value)

این پارامتر مقداری را که قرار است در محدوده موردنظر ما جستجو شود تعیین می کند. ما در این مثال می خواهیم رتبه را برای امتیاز ۹۲ بدست آوریم که یک عدد بین ۸۰ تا ۱۰۰ است. بنابراین مقداری که ما می خواهیم بر اساس آن جستجو رو انجام بدهیم سلول F6 است. پس پارامتر اول ما برابر F6 هست.

۳- آرگومان دوم (table_array)

در این پارامتر باید بازه ای را که قرار است جستجو را در آن انجام دهیم تعریف کنیم. این بازه باید هم شامل سطری باشد که می خواهیم جستجو را در آن انجام بدهیم (در مثال ما سطر “رتبه”) و هم سطری که به دنبال نتایج متناظر جستجو از آن هستیم (سطر مربوط “امتیاز”). پس پارامتر دوم ما برابر B2:G3 است.

۴- آرگومان سوم (row_index_num)

بعد از مشخص کردن مقدار مورد جستجو و جدول، فرمول می پرسد که مجهول ما کجاست؟ کافی است که جای سطر مجهول یعنی “رتبه” را به آن بگوییم. یعنی بگوییم در این جدول یعنی (B2:G3) دومین سطر حاوی مقداری است که ما آن را لازم داریم. پس در این مثال عدد ۲ (یعنی دومین ردیف داده) آرگومان سوم ما است.

توجه داشته باشید که Hlookup از اولین سطر جدول داده (نه اولین سطر اکسل) کار جست‌وجو را انجام می دهد.

۵- آرگومان چهارم (Range_Lookup)

چهارمین پارامتر تعیین می کند که آیا شما به دنبال تطابق دقیق هستید یا تطابق بازه ای. برای پیدا کردن تطابق دقیق (Exact Match)، صفر یا FALSE را وارد کنید. برای پیدا کردن تطابق بازه ای (Approximate Match) هر چیزی غیر از صفر یا TRUE را وارد کنید.

نکته: این پارامتر اختیاری است و اگر وارد نشود، به طور پیش فرض TRUE در نظر گرفته می شود.

در این مثال ما تطابق بازه ای را انتخاب می کنیم. منطق تطابق بازه ای به این صورت است که بزرگترین عدد قبل از عدد مورد جستجو را معیار جستجو قرار می دهد.

بنابراین شکل نهایی فرمول HLOOKUP به صورت زیر خواهد شد:

=HLOOKUP(F6;$C$2:$G$3;2;TRUE)

در نهایت با فشردن کلید Enter مشخص می شود که رتبه نفر اول B است.

اکنون با دوبار کلیک کردن روی نقطه مشخص شده در تصویر بالا یا Drag کردن آن و استفاده از قابلیت Fill Sequence، می توانیم به سادگی رتبه همه افراد را مشاهده کنیم.

خطاهای تابع HLOOKUP

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

خطای !REF#: این خطا به معنای اشتباه وارد شدن رفرنس (Reference) می باشد و زمانی نمایش داده می شود که آرگومان سوم تابع Hlookup عددی بزرگ تر از تعداد سطر‌های جدول داده باشد.

خطای !VALUE#: این خطا برای زمانی است که در آرگومان سوم عددی منفی وارد کنیم.

اکنون باید درک کرده باشید که دو فرمول HLOOKUP و VLOOKUP در اکسل یک کار را انجام می دهند با این تفاوت که فرمول HLOOKUP به صورت ردیفی جستجو می کند و جواب را در ستون متناظر پیدا می کند، در صورتی که فرمول VLOOKUP به صورت ستونی جستجو می کند و جواب را در سطر متناظر آن پیدا می کند.