Загрузите образец книги
В этом руководстве будет показано, как выполнить «обратную ВПР» в Excel и Google Таблицах.
Обратный ВПР - справа налево
При использовании функции ВПР столбец подстановки должен быть крайним левым столбцом в наборе данных. Таким образом, вы можете выполнять поиск только слева направо.
Однако вы можете обойти это ограничение, используя комбинацию INDEX / MATCH (или новую функцию XLOOKUP, как вы увидите ниже).
| 1 | = ИНДЕКС (B3: D6; ПОИСКПОЗ (F3; C3: C6,0); 1) |
Формула работает следующим образом:
Функция ПОИСКПОЗ
Мы используем функцию ПОИСКПОЗ, чтобы вернуть относительную позицию (т.е. номер строки в массиве) элемента поиска.
| 1 | = ПОИСКПОЗ (F3; C3: C6,0) |
Думайте о функции ПОИСКПОЗ как о функции ВПР, выполняемой для одного столбца, за исключением того, что вместо возврата значения возвращается номер строки.
ИНДЕКС Функция
Получив номер строки, мы используем функцию ИНДЕКС, чтобы вернуть значение из этого номера строки.
| 1 | = ИНДЕКС (массив; номер_строки; [номер_столбца]) |
| 1 | = ИНДЕКС (B3: D6; G3,1) |
Функция ИНДЕКС возвращает значение ячейки определенной строки и столбца в вашем диапазоне. Функция ПОИСКПОЗ предоставляет номер строки. Мы устанавливаем номер столбца равным 1, потому что нам нужен первый столбец данных.
Комбинируя функции, мы получаем нашу оригинальную формулу:
| 1 | = ИНДЕКС (B3: D6; ПОИСКПОЗ (F3; C3: C6,0); 1) |
Функция XLOOKUP
Функция XLOOKUP была представлена пользователям Office 365 после выхода Excel 2022. Функция XLOOKUP также позволяет выполнять обратный поиск:
| 1 | = XLOOKUP (F3; C3: C6; B3: B6) |
Обратить ВПР справа налево в Google Таблицах
Эти формулы работают в Google Таблицах точно так же, как и в Excel.
