سم اغلب از تابع VLOOKUP استفاده می کند. بسیار مفید است، اما یک محدودیت جدی وجود دارد - جستجو می تواند تنها به ستون های سمت راست اشاره کند. این بدان معناست که Sam نمی تواند به عنوان پارامتر سوم برای VLOOKUP از یک مقدار منفی برای ارجاع به یک ستون در سمت چپ استفاده کند. او متعجب است که آیا راهی برای دور زدن این محدودیت وجود دارد؟
در واقع سه راه برای دور زدن این محدودیت وجود دارد: تجدید ساختار، استفاده از INDEX و استفاده از CHOOSE. به نوبه خود به هر یک از این روش ها نگاه می کنم.
تجدید ساختار محتوای شما
این ممکن است حداقل مطلوب ترین رویکرد باشد، اما در ابتدا آن را از سر راه برداشته ام. اگر متوجه شدید که اغلب باید مقادیر را به سمت چپ جستجوی خود برگردانید، ممکن است کاربرگ خود را بازسازی کنید تا مقادیر در سمت راست فرمول شما قرار گیرند.
یک روش جایگزین برای بازسازی این است که از یک ستون کمکی در سمت راست فرمول خود استفاده کنید. این ستون کمکی به سادگی باید به مقادیر بازگشتی واقعی اشاره کند. به عنوان مثال، اگر مقادیر بازگشتی شما در ستون A و فرمول شما در ستون E باشد، ممکن است یک ستون کمکی J اضافه کنید. فرمول در J1 به سادگی =A1 خواهد بود. آن را کپی کنید و سپس از ستون J به عنوان مقادیر بازگشتی خود در فرمول های ستون E استفاده کنید.
با استفاده از INDEX و MATCH
شاید رایج ترین رویکرد برای مشکلی که سام با آن مواجه است استفاده از ترکیبی از توابع INDEX و MATCH به جای VLOOKUP باشد. به عنوان مثال، فرض کنید فرمول VLOOKUP زیر را دارید:
=VLOOKUP(G1,$C$1:$E$100,3,TRUE)
این مقدار را در محدوده C1:C100 در G1 (به عنوان یک تطابق تقریبی) جستجو می کند و مقدار مربوطه را در E1:E100 دریافت می کند. این فرمول معادل فرمول زیر است:
=INDEX($E$1:$E$100,MATCH(G1,$C$1:$C$100,1))
بنابراین، اگر می‌خواهید ستونی را در سمت چپ ستون جستجوی خود قرار دهید (به عنوان مثال A1:A100)، می‌توانید از چیزی مانند زیر استفاده کنید:
=INDEX($A$1:$A$100,MATCH(G1,$C$1:$C$100,1))
اگر می‌خواهید یک مطابقت دقیق توسط جستجو برگردانده شود، تنها کاری که باید انجام دهید این است که 1 نهایی در تابع MATCH را به 0 تغییر دهید، مانند این:
=INDEX($A$1:$A$100,MATCH(G1,$C$1:$C$100,0))
با استفاده از VLOOKUP و CHOOSE
اگر واقعاً می‌خواهید به استفاده از تابع VLOOKUP در فرمول خود ادامه دهید، می‌توانید با گنجاندن تابع CHOOSE، آن را برای بازیابی مقادیر سمت چپ فریب دهید.
برای نشان دادن این موضوع، اجازه دهید فرض کنیم که مقادیر جستجوی شما در ستون D و مقادیر بازگشتی در ستون A هستند. در سلول G1 مقدار جستجوی شما است. فرمول زیر مقادیر مناسب را برمی گرداند:
=VLOOKUP(G1,CHOOSE({1,2},$D$1:$D$100,$A$1:$A$100),2,FALSE)
تابع CHOOSE آرایه ای متشکل از سلول های نشان داده شده را برمی گرداند. سپس تابع VLOOKUP مقداری را از ستون دوم آن آرایه، که اتفاقاً ستون A است، در سمت چپ ستون D برمی‌گرداند.
