نکات و قوانین حاکم بر تابع VLOOKUP اکسل
- اگر به دنبال کد کالا می گردید محدوده مورد جستجو ، ستون اول باید شامل کد کالا باشد.
- تابع VLOOKUP به اولین نتیجه منطبق که برسد ، متوقف شده و داده مورد نظر را بر می گرداند
- تابع نمی تواند داده های قبل از خود را لوکاپ کند .
- اگر محدودیت های این تابع برای شما آزار دهنده است می توانید آفیس 2021 نصب کرده و از تابع XLOOKUP استفاده کنید
مثال ساده از تابع VLOOKUP
فرض می کنیم داده هایی مشابه تصویر زیر داریم که ستون دوم آن کد پرسنلی و ستون آخر آن هم میزان پاداش هست و می خواهیم با وارد کردن کد پرسنلی ، میزان پاداش مربوطه نمایش داده شود .
نکته مهمی که وجود دارد این است که آرگومان دوم باید از جایی شروع شود که جستجو می خواهد در آن ستون انجام شود . یعنی وقتی به دنبال کد پرسنلی هستیم باید محدوده مورد جستجو در آرگومان دوم از محدوده C2 شروع شود و تا جایی که داده وجود دارد ادامه پیدا کند .
نکته مهم دیگر اینست که باید آرگومان آخر را با اینکه اختیاری هست عدد 0 یا FALSE قرار دهیم تا دقیقا به دنبال کد پرسنلی وارد شده بگردد.
حالا چند حالت به وجود می آید :
- کد پرسنلی در محدوده وجود نداشته باشد : ارور #N/A نمایش می دهد .
- کد پرسنلی چند بار تکرار شده باشد : اولین کد پرسنلی را که پیدا می کند ملاک قرار داده و بقیه را نمایش نمی دهد.
- نوع داده وارد شده با نوع جستجو همخوانی نداشته باشد : اگر در ستون کد پرسنلی عددی باشد که به صورت متنی وارد شده باشد و در زمان جستجو خاصیت سلول عددی وارد شود ، نتیجه بازهم #N/A خواهد بود .
=VLOOKUP(I2,$C$2:$F$8,4,0)
استفاده از تابع VLOOKUP با دو شرط
فرض کنیم که داده هایی مشابه تصویر زیر داریم که شامل کد و سریال و بقیه اطلاعات است و در کدها ما امکان تکرار داریم و سریالها یونیک هستند ، حال می خواهیم با انتخاب کد و سریال ، داده های متناظر این دو انتخاب را نمایش دهد .
اولین راهی که وجود دارد این است که یک ستون کمکی قبل از ستون کد ایجاد کنیم و در آن ، کد کالا و سریال را با هم تجمیع کنیم که این کار را می توانیم با تابع TEXTJON انجام دهیم .
سپس در قسمت مورد نظر از تابع VLOOKUP به شکل زیر استفاده کنیم .
=VLOOKUP(J2&J3,$B$2:$G$8,6,0)
حالا اگر محدودیت ایجاد ستون کمکی داشته باشیم چه کاری می توانیم انجام دهیم ؟ در این صورت می توانیم از تریکیب تابع INDEX و تابع MATCH استفاده کنیم ، به این صورت که در تابع MATCH دو شرط برابری کد و سریال را قرار دهیم تا شماره ردیف صحیح را به دست آوریم.
=INDEX(C2:G8, MATCH(1, (C2:C8=J2)*(D2:D8=J3), 0),5)
فرض کنیم که می خواهیم حتما با تابع VLOOKUP انجام دهیم و نمی توانیم ستون کمکی هم ایجاد کنیم . در اینصورت می توانیم از ترکیب با توابع دیگر به صورت زیر استفاده کنیم.
تریکب با تابع CHOOSE :
دراین روش به جای اینکه ستون کمکی را به خود اکسل اضافه کنیم به صورت فرمول داخل توابع استفاده می کنیم و در آرگومان دوم از تابع CHOOSE استفاده می کنیم که دو ستون مجزا شامل ترکیب کد و سریال و یک ستون پاداش ایجاد کنیم و سپس تابع VLOOKUP را به شکل زیر استفاده کنیم .
=VLOOKUP(H2&H3,CHOOSE({1,2},B3:B8&C3:C8,E3:E8),2,0)تریکب با تابع IF :
دراین روش هم ابتدا به دنبال کد کالا می گردیم و سپس در نتایج با کمک تابع IF آنهایی که سریالشان مساوی با سریال مورد نظر ما هست را محدود می کنیم و به نتیجه دلخواه می رسیم.
=IFERROR(VLOOKUP(H3,IF(H2=B3:B8,C3:E8,""),3,0),"")
ترکیب با تابع LEN :
در این روش ابتدا کد و سریال را با هم ادغام می کنیم و سپس ستون کد را با بقیه ستونها ادغام میکنیم و نتیجه ای که حاصل می شود ، کد را به همراه نتیجه نمایش می دهد که کافیست با استفاده از تابع LEN آن را حذف کنیم و فقط مقدار پاداش باقی بماند.
البته این روش به دلیل اینکه نتیجه متنی می باشد همه جا صادق نیست و باید با احتیاط استفاده شود و در نهایت با تابع NUMBER نتیجه را تبدیل به عدد کرد.
=RIGHT(VLOOKUP(K3&K4,D3:D9&E3:H9,4,0),LEN(VLOOKUP(K3&K4,D3:D9&E3:H9,4,0))-LEN(K3))
ترکیب با تابع INT :
در این روش ابتدا کد و سریال را به همراه یک نقطه با هم ادغام می کنیم و سپس ستون کد را با بقیه ستونها ادغام میکنیم و نتیجه ای که حاصل می شود ، کد را به همراه یک نقطه و نتیجه نمایش می دهد که کافیست با استفاده از تابع INT مقدار پاداش باقی بماند.
این روش هم زمانی پاسخگو است که نتیجه ما عددی باشد و مقداری که می خواهیم بازگردانده شود عددی باشد.
=INT(VLOOKUP(H2&"."&H3,B3:E8&"."&C3:C8,4,0))
بدون تابع VLOOKUP و با استفاده از SUM :
این روش هم ساده و بسیار جذاب است و بازهم زمانی کاربرد دارد که مقداری که باید برگردانده شود ، عددی باشد .
=SUM((E3:E8)*(C3:C8=H3)*(B3:B8=H2))
نمایش نتایج تکراری
فرض می کنیم که داده های قبلی به گونه ای هست که برخی از کدها چند بار تکرار شده اند و با VLOOKUP می خواهیم تمام کدهای وارد شده را نمایش دهیم .
البته که پیشنهاد ما برای این منظور استفاده از تابع FILTER می باشد ولی اگر آفیس 2021 نداشتید و مجبور بودید از تابع VLOOKUP استفاده کنید می توانید از روش زیر استفاده کنید . که بازهم ترکییبی از تابع INDEX و سایر توابع می باشد.
=IFERROR(INDEX($B$3:$F$8,SMALL(IF($I$2=$B$3:$B$8,ROW($B$3:$B$8)-2,""),ROW()-1),3),"")
استفاده از VLOOKUP برای نمایش داده ستون قبلی
یکی از محدودیتهای ویلوکاپ این است که رو به جلو حرکت میکند ، یعنی در ستون اول که داده را پیدا کرد دادهای متناظر بعدی از آن را می تواند متناسب با شماره ستون وارد شده نمایش دهد .
حال در مثال زیر اگر بخواهیم با انتخاب کد پرسنلی ، نام و نام خانوادگی نمایش داده شود چکار باید بکنیم ؟
خب خیلی بهتر است که برای حل مسادل این چنینی از تابع XLOOKUP استفاده کنیم و اگر توابع آفیس 2021 را نداشتیم از تابع INDEX استفاده کنیم . ولی اگر اصرار بر استفاده از VLOOKUP داشتید می توانید به شکل زیر از آن بهره ببرید.
=VLOOKUP(I2,CHOOSE({1,2},C3:C8,B3:B8),2,0)
اهمیت آرگومان آخر VLOOKUP و نمایش نتیجه تقریبی
فرض می کنیم داده هایی مشابه تصویر زیر داریم که در آن مقدار پاداش بر اساس فروش تعیین شده است و به این ترتیب که برای بازه ای از فروش ، میزان پاداش تعیین شده است و احتمال اینکه دقیقا مشابه میزان فروش در لیست نباشد ، بسیار زیاد است و به همین دلیل آرگومان آخر VLOOKUP را عدد 1 یا TRUE وارد میکنیم که اگر مقدار دقیق را پیدا نکرد ، مقدار متناظر با اولین داده بزرگتر نزدیک به مقدار جستجو را نمایش دهد.
=VLOOKUP(C3,$F$3:$G$7,2,1)
اگر مثال و یا شیوه دیگری از استفاده از تابع VLOOKUP می شناسید حتما در قسمت نظرات عنوان بفرمائید تا مطلب را آپدیت کنیم.
