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