دو نکته مهم فرمول نویسی تابع VLOOKUP در اکسل که نباید فراموش کنید

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

نکته اول؛ ستون جستجو باید سمت چپ ترین ستون باشد

VLOOKUP فقط در ستون اول محدوده ای که به آن می دهید جستجو می کند و هیچ وقت به چپ آن ستون نگاه نمی کند. یک جدول پرسنل را در نظر بگیرید که ستون اول آن «نام» است و «کد پرسنلی» ستون دوم:

با فرمول =VLOOKUP(1038,A2:C9,3,FALSE) می خواهیم حقوق پایه کد پرسنلی ۱۰۳۸ را پیدا کنیم، اما چون ستون اول جدول «نام» است نه «کد پرسنلی»، اکسل اصلا کد را در ستون اول پیدا نمی کند و نتیجه #N/A می شود؛ حتی اگر کد پرسنلی در ستون دوم واقعا وجود داشته باشد.

برای رفع این مشکل دو راه دارید:

1. جابجایی ستون ها: کد پرسنلی را به ستون اول جدول منتقل کنید تا VLOOKUP بتواند آن را جستجو کند.
2. استفاده از INDEX/MATCH به جای VLOOKUP: بدون تغییر چیدمان جدول اصلی؛ ستون جستجو و ستون نتیجه را جدا مشخص می کنید و به ترتیب ستون ها هیچ کاری ندارید.

فرمول =INDEX(C2:C9,MATCH(1038,B2:B9,0)) دقیقا همان حقوق پایه را برمی گرداند، بدون اینکه لازم باشد جدول اصلی را به هم بریزید؛ به همین دلیل در فایل های حسابداری که جدول از قبل ساختار مشخصی دارد، INDEX/MATCH گزینه امن تری از VLOOKUP است.

نکته دوم؛ آرگومان چهارم را فراموش نکنید

آرگومان چهارم VLOOKUP مشخص می کند جستجو دقیق باشد یا تقریبی. با FALSE (یا عدد ۰)، اکسل فقط دنبال تطابق دقیق می گردد و اگر پیدا نکند، خطای #N/A می دهد. اگر این آرگومان را ننویسید، به طور پیش فرض روی TRUE (تقریبی) قرار می گیرد؛ در این حالت اکسل فرض می کند ستون جستجو از کوچک به بزرگ مرتب شده و نزدیک ترین مقدار را برمی گرداند. اگر لیست شما مرتب نباشد، این «نزدیک ترین مقدار» می تواند کاملا اشتباه باشد، و از همه مهم تر، اکسل هیچ خطایی هم نشان نمی دهد.

یک جدول ضریب کارانه بر اساس کد پرسنلی را در نظر بگیرید که به ترتیب کد مرتب نشده است:

کد پرسنلی ۱۰۵۰ به دلیل اشتباه تایپی وارد شده و اصلا در لیست وجود ندارد. فرمول =VLOOKUP(1050,A2:B9,2) بدون آرگومان چهارم، به جای نمایش خطا، ضریب کارانه یک کد کاملا دیگر را برمی گرداند؛ چون لیست مرتب نیست، این عدد نه فقط تقریبی بلکه کاملا نامربوط است. اما فرمول =VLOOKUP(1050,A2:B9,2,FALSE) با تطابق دقیق، به درستی #N/A نشان می دهد و همان لحظه مشخص می شود که کد پرسنلی وارد شده اشتباه است.

چند نکته کاربردی

• در فایل های حقوق و دستمزد، تقریبا همیشه آرگومان چهارم را FALSE یا 0 بگذارید؛ تطابق تقریبی فقط برای مواردی مثل جدول های پلکانی مالیات یا تعرفه که واقعا بر اساس بازه و به ترتیب مرتب هستند مناسب است.
• همیشه بعد از نوشتن VLOOKUP، فرمول را با یک کد پرسنلی که جواب آن را از قبل می دانید تست کنید؛ اگر نتیجه با آنچه انتظار دارید یکی نبود، به سراغ چیدمان ستون ها یا آرگومان چهارم بروید.
• برای دیدن فرمول به جای نتیجه آن در همه سلول ها، از کلید ترکیبی Ctrl+` (روی حرف تیلدا، کنار عدد ۱) استفاده کنید؛ این کار خطاهای رایج مثل فراموش کردن FALSE را سریع نشان می دهد.
• اگر مرتب ستون ها در جدول های شما تغییر می کند یا مطمئن نیستید کد پرسنلی همیشه ستون اول باقی می ماند، از ابتدا سراغ INDEX/MATCH یا XLOOKUP (در نسخه های جدید اکسل) بروید؛ این دو تابع به ترتیب ستون ها وابسته نیستند.
• قبل از هر مغایرت گیری یا محاسبه گروهی حقوق، ستون کد پرسنلی را با Remove Duplicates یا Conditional Formatting چک کنید تا کد تکراری یا خالی باعث نتیجه اشتباه در VLOOKUP نشود.

📄 دانلود فایل اکسل نمونه «دو نکته مهم VLOOKUP» — شامل جدول پرسنل، فرمول اشتباه VLOOKUP به همراه هر دو راه حل نکته اول (جابجایی ستون و INDEX/MATCH)، و جدول ضریب کارانه با فرمول تطابق تقریبی و دقیق برای نکته دوم؛ همه فرمول ها زنده هستند و بعد از باز کردن فایل می توانید نتیجه واقعی هرکدام را ببینید.

منبع : سایت حسابداری بیمارستان
کانال تلگرام @hospitalaccounting