Общая формула
=VLOOKUP(A1,CHOOSE((3,2,1),col1,col2,col3),3,0)
Резюме
Чтобы отменить ВПР, то есть найти исходное значение поиска с использованием результата формулы ВПР, вы можете использовать сложную формулу, основанную на функции ВЫБОР, или более простые формулы, основанные на ИНДЕКС и ПОИСКПОЗ или XLOOKUP, как описано ниже. В показанном примере формула H10 имеет следующий вид:
=VLOOKUP(G10,CHOOSE((3,2,1),B5:B8,C5:C8,D5:D8),3,0)
При такой настройке функция VLOOKUP находит вариант, связанный со стоимостью 3000, и возвращает «C».
Примечание: это более сложная тема. Если вы только начинаете работать с ВПР, начните здесь.
Введение
Ключевым ограничением ВПР является то, что он может искать только значения справа. Другими словами, столбец со значениями поиска должен находиться слева от значений, которые вы хотите получить с помощью ВПР. В результате при стандартной конфигурации невозможно использовать ВПР, чтобы «смотреть влево» и отменить исходный поиск.
С точки зрения ВПР мы можем визуализировать проблему следующим образом:
Обходной путь, описанный ниже, использует функцию ВЫБОР, чтобы изменить порядок таблицы внутри ВПР.
Объяснение
Начиная с самого начала, формула в H5 представляет собой обычную формулу ВПР:
=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 возвращает все три столбца в одном 2D-массиве следующим образом:
(1000,"Silver","A";2000,"Gold","B";3000,"Platinum","C";5000,"Diamond","D")
Если мы визуализируем этот массив в виде таблицы на листе, мы получим:
Примечание: заголовки не являются частью массива и показаны здесь только для ясности.
Фактически мы поменяли местами столбцы 1 и 3. Реорганизованная таблица возвращается непосредственно в VLOOKUP, которая соответствует 3000 и возвращает соответствующее значение из столбца 3, «C».
С ИНДЕКСОМ и МАТЧЕМ
Вышеупомянутое решение работает нормально, но его трудно рекомендовать, поскольку большинство пользователей не понимают, как работает формула. Лучшее решение - ИНДЕКС и ПОИСКПОЗ с использованием такой формулы:
=INDEX(B5:B8,MATCH(G10,D5:D8,0))
Здесь функция ПОИСКПОЗ находит значение 3000 в D5: D8 и возвращает его позицию 3:
MATCH(G10,D5:D8,0) // returns 3
Примечание. ПОИСКПОЗ настраивается для точного совпадения, устанавливая последний аргумент равным нулю (0).
ПОИСКПОЗ возвращает результат непосредственно в ИНДЕКС в виде номера строки, поэтому формула принимает следующий вид:
=INDEX(B5:B8,3) // returns "C"
а ИНДЕКС возвращает значение из третьей строки B5: B8, «C».
Эта формула показывает, как ИНДЕКС и ПОИСКПОЗ могут быть более гибкими, чем ВПР.
С XLOOKUP
XLOOKUP также предлагает очень хорошее решение. Эквивалентная формула:
=XLOOKUP(G10,D5:D8,B5:B8) // returns "C"
Имея значение поиска из G10 (3000), массив al ookup из D5: D8 (затраты) и массив результатов из B5: B8 (параметры), XLOOKUP находит 3000 в массиве поиска и возвращает соответствующий элемент из массива результатов, «С». Поскольку XLOOKUP по умолчанию выполняет точное соответствие, нет необходимости явно устанавливать режим соответствия.
