Загальна формула
=VLOOKUP(A1,CHOOSE((1,2),range2,range1),2,0)
Резюме
Щоб використовувати VLOOKUP для виконання пошуку ліворуч, ви можете скористатися функцією ВИБІР, щоб змінити порядок таблиці пошуку. У наведеному прикладі формула в F5 має вигляд:
=VLOOKUP(E5,CHOOSE((1,2),score,rating),2,0)
де оцінка (C5: C9) та рейтинг (B5: B9) називаються діапазонами.
Пояснення
Одним з ключових обмежень функції VLOOKUP є те, що вона може шукати лише значення праворуч. Іншими словами, стовпець, що містить значення пошуку, повинен знаходитися ліворуч від значень, які потрібно отримати за допомогою VLOOKUP. Немає можливості замінити цю поведінку, оскільки вона підключена до функції. Як результат, при нормальній конфігурації неможливо використовувати VLOOKUP для пошуку рейтингу в стовпці B на основі оцінки в стовпці C.
Одним з обхідних шляхів є реструктуризація самої таблиці пошуку та переміщення стовпця пошуку ліворуч від значень пошуку. Ось підхід, використаний у цьому прикладі, який використовує функцію ВИБІР зворотного рейтингу та оцінку наступним чином:
CHOOSE((1,2),score,rating)
Зазвичай CHOOSE використовується з одним номером індексу як першим аргументом, а решта аргументів - це значення, з яких можна вибрати. Однак тут ми наводимо константу масиву для номера індексу, що містить два числа: (1,2). По суті, ми просимо вибрати як перше, так і друге значення.
Значення подані як два названі діапазони в прикладі: оцінка та рейтинг. Однак зверніть увагу, що ми надаємо ці діапазони в зворотному порядку. Функція ВИБІР обирає обидва діапазони у вказаному порядку і повертає результат у вигляді єдиного масиву, як це:
(5,"Excellent";4,"Good";3,"Average";2,"Poor";1,"Terrible")
CHOOSE повертає цей масив безпосередньо до VLOOKUP як аргумент масиву таблиці. Іншими словами, CHOOSE постачає таку таблицю пошуку, як це, до VLOOKUP:
Використовуючи значення пошуку в E5, VLOOKUP знаходить відповідність всередині новоствореної таблиці та повертає результат із другого стовпця.
Переупорядкування з константою масиву
У наведеному прикладі ми впорядковуємо таблицю пошуку, змінюючи "рейтинг" та "оцінку" всередині вибраної функції. Однак замість цього ми могли б використовувати константу масиву для переупорядкування таким чином:
CHOOSE((2,1),rating,score)
Результат точно такий же.
За допомогою INDEX та MATCH
Хоча наведений вище приклад працює нормально, він не ідеальний. З одного боку, більшість пересічних користувачів не розуміють, як працює формула. Більш природним рішенням є ІНДЕКС та МАТЧ. Ось еквівалентна формула:
=INDEX(rating,MATCH(E5,score,0))
Насправді це хороший приклад того, як INDEX і MATCH є більш гнучкими, ніж VLOOKUP.
