Загальна формула
=VLOOKUP(A1,CHOOSE((3,2,1),col1,col2,col3),3,0)
Резюме
Щоб змінити VLOOKUP, тобто знайти вихідне значення підстановки за допомогою формули VLOOKUP, ви можете використовувати хитру формулу, засновану на функції ВИБІР, або більш прості формули на основі INDEX та MATCH або XLOOKUP, як пояснено нижче. У наведеному прикладі формула в H10 має вигляд:
=VLOOKUP(G10,CHOOSE((3,2,1),B5:B8,C5:C8,D5:D8),3,0)
За допомогою цього налаштування VLOOKUP знаходить варіант, пов'язаний із вартістю 3000, і повертає "C".
Примітка: це більш досконала тема. Якщо ви тільки починаєте роботу з VLOOKUP, почніть тут.
Вступ
Ключовим обмеженням VLOOKUP є те, що він може шукати лише значення праворуч. Іншими словами, стовпець зі значеннями підстановки повинен знаходитися зліва від значень, які потрібно отримати за допомогою VLOOKUP. Як результат, у стандартній конфігурації немає можливості використовувати VLOOKUP, щоб "виглядати ліворуч" і змінити початковий пошук.
З точки зору VLOOKUP, ми можемо візуалізувати проблему так:
Нижче описаний обхідний спосіб використовує функцію ВИБІР для перестановки таблиці всередині VLOOKUP.
Пояснення
Починаючи з самого початку, формула в H5 є звичайною формулою VLOOKUP:
=VLOOKUP(G5,B5:D8,3,0) // returns 3000
Використовуючи G5 як значення пошуку ("C") та дані в B5: D8 як масив таблиці, VLOOKUP виконує пошук значень у стовпці B і повертає відповідне значення зі стовпця 3 (стовпець D), 3000. Зверніть увагу нуль (0) надається як останній аргумент для примусування точного збігу.
Формула в G10 просто витягує результат з H5:
=H5 // 3000
Для виконання зворотного пошуку в H10 формула:
=VLOOKUP(G10,CHOOSE((3,2,1),B5:B8,C5:C8,D5:D8),3,0)
Хитрий біт - це функція CHOOSE, яка використовується для перестановки масиву таблиці так, щоб Cost була першим стовпцем, а Option - останнім:
CHOOSE((3,2,1),B5:B8,C5:C8,D5:D8) // reorder table 3, 2, 1
Функція ВИБІР призначена для вибору значення на основі числового індексу. У цьому випадку ми надаємо три значення індексу в константі масиву:
(3,2,1) // array constant
Іншими словами, ми просимо стовпець 3, потім стовпець 2, потім стовпець 1. Після цього йдуть три діапазони, що представляють кожен стовпець таблиці в тому порядку, в якому вони з’являються на аркуші.
За допомогою цієї конфігурації CHOOSE повертає всі три стовпці в одному двовимірному масиві так:
(1000,"Silver","A";2000,"Gold","B";3000,"Platinum","C";5000,"Diamond","D")
Якщо ми візуалізуємо цей масив як таблицю на аркуші, ми маємо:
Примітка: заголовки не є частиною масиву і наведені тут лише для наочності.
Фактично ми поміняли місцями стовпці 1 і 3. Реорганізована таблиця повертається безпосередньо до VLOOKUP, який відповідає 3000, і повертає відповідне значення зі стовпця 3, "C".
За допомогою INDEX та MATCH
Вищевказане рішення працює нормально, але його важко рекомендувати, оскільки більшість користувачів не розуміють, як працює формула. Кращим рішенням є INDEX та MATCH, використовуючи таку формулу:
=INDEX(B5:B8,MATCH(G10,D5:D8,0))
Тут функція MATCH знаходить значення 3000 у D5: D8 і повертає своє положення 3:
MATCH(G10,D5:D8,0) // returns 3
Примітка: MATCH налаштовано на точну відповідність, встановивши для останнього аргументу нуль (0).
MATCH повертає результат безпосередньо до INDEX як номер рядка, тому формула стає:
=INDEX(B5:B8,3) // returns "C"
і INDEX повертає значення з третього рядка B5: B8, "C".
Ця формула показує, як INDEX та MATCH можуть бути більш гнучкими, ніж VLOOKUP.
З XLOOKUP
XLOOKUP також забезпечує дуже хороше рішення. Еквівалентна формула:
=XLOOKUP(G10,D5:D8,B5:B8) // returns "C"
Зі значенням підстановки від G10 (3000), всім масивом пошуку D5: D8 (витрати) та масивом результатів B5: B8 (варіанти), XLOOKUP визначає 3000 в масиві підстановки та повертає відповідний елемент із масиву результатів, "С". Оскільки XLOOKUP виконує точну відповідність за замовчуванням, немає необхідності встановлювати режим відповідності явно.
