Сэм часто пользуется функцией ВПР. Это очень удобно, но есть одно серьезное ограничение — поиск может ссылаться только на столбцы справа. Это означает, что Сэм не может использовать в качестве третьего параметра для VLOOKUP отрицательное значение для ссылки на столбец слева. Он задается вопросом, есть ли способ обойти это ограничение.

На самом деле есть три способа обойти это ограничение: реструктуризация, использование INDEX и CHOOSE. Я рассмотрю каждый из этих методов по очереди.

Реструктуризация вашего контента

Это может быть наименее желательным подходом, но я сразу уберу его с дороги. Если вы обнаружите, что вам довольно часто нужно возвращать значения слева от поиска, вы можете подумать о реструктуризации своего рабочего листа, чтобы значения располагались справа от формулы.

Альтернативный метод реструктуризации — использовать вспомогательный столбец справа от формулы. Этот вспомогательный столбец просто должен ссылаться на фактические возвращаемые значения. Например, если возвращаемые вами значения находятся в столбце A, а ваша формула — в столбце E, вы можете добавить вспомогательный столбец a J.

Формула в J1 будет просто = A1. Скопируйте его, а затем используйте столбец J в качестве возвращаемых значений в формулах в столбце E.

Использование INDEX и MATCH

Возможно, наиболее распространенный подход к проблеме, с которой сталкивается Сэм, — это использование комбинации функций ИНДЕКС и ПОИСКПОЗ вместо ВПР. Например, предположим, что у вас есть следующая формула ВПР:

=VLOOKUP(G1,$C$1:$E$100,3,TRUE)

Это ищет в диапазоне C1: C100 значение в G1 (как приблизительное совпадение) и получает соответствующее значение в E1: E100. Эта формула эквивалентна следующей формуле:

=INDEX($E$1:$E$100,MATCH(G1,$C$1:$C$100,1))

Итак, если вы хотите получить столбец слева от столбца поиска (например, A1: A100), вы можете использовать что-то вроде:

=INDEX($A$1:$A$100,MATCH(G1,$C$1:$C$100,1))

Если вы хотите получить точное совпадение, возвращаемое поиском, все, что вам нужно сделать, это изменить последнюю 1 в функции ПОИСКПОЗ на 0, например:

=INDEX($A$1:$A$100,MATCH(G1,$C$1:$C$100,0))

Использование ВПР и ВЫБОР

Если вы действительно хотите продолжить использование функции VLOOKUP в своей формуле, вы можете «обмануть» ее и получить значения слева, также включив функцию CHOOSE.

Чтобы проиллюстрировать это, предположим, что ваши значения поиска находятся в столбце D, а возвращаемые значения находятся в столбце A. В ячейке G1 находится ваше значение поиска. Следующая формула вернет правильные значения:

=VLOOKUP(G1,CHOOSE({1,2},$D$1:$D$100,$A$1:$A$100),2,FALSE)

Функция CHOOSE возвращает массив, состоящий из указанных ячеек.

Затем функция ВПР возвращает значение из второго столбца этого массива, который оказывается столбцом A, слева от столбца D.

ExcelTips — ваш источник экономичного обучения Microsoft Excel.

Этот совет (13608) применим к Microsoft Excel 2007, 2010, 2013, 2016, 2019 и Excel в Office 365.