Excel: используйте впр для возврата нескольких значений по горизонтали

Часто вам может потребоваться использовать функцию ВПР в Excel для возврата нескольких значений по горизонтали.

К сожалению, это невозможно с помощью функции ВПР , но можно использовать следующую формулу с функциями ИНДЕКС , МАЛЕНЬКИЙ и ЕСЛИ :

 =INDEX( $B$2:$B$13 , SMALL(IF( $A$17 = $A$2:$A$13 ,ROW( $A$2:$A$13 )-ROW( $B$2 )+1), COLUMN( A1 )))

Эта формула находит значение в ячейке A17 в диапазоне A2:A13 и возвращает соответствующее значение в диапазоне B2:B13 .

Вы можете перетащить эту формулу горизонтально, чтобы просмотреть несколько значений.

Следующий пример показывает, как использовать эту формулу на практике.

Пример: используйте ВПР для возврата нескольких значений по горизонтали.

Предположим, у нас есть следующий набор данных в Excel, содержащий информацию об очках, набранных баскетболистами разных команд:

Допустим, мы хотим найти «Mavs» в столбце команды и вернуть каждое значение балла по горизонтали.

Для этого мы можем ввести следующую формулу в ячейку B17 :

 =INDEX( $B$2:$B$13 , SMALL(IF( $A$17 = $A$2:$A$13 ,ROW( $A$2:$A$13 )-ROW( $B$2 )+1), COLUMN( A1 )))

Затем мы можем щелкнуть и перетащить эту формулу горизонтально вправо, чтобы отобразить каждое из значений баллов, соответствующих «Mavs», в столбце команды:

Формула корректно возвращает значения 24, 40, 15 и 25 в одной строке.

Обратите внимание, что каждое из этих значений представляет собой значение в столбце очков, соответствующее значению «Mavs» в столбце команды:

Обратите внимание, что вам нужно будет щелкнуть и перетащить эту формулу вправо, пока не встретите #ЧИСЛО! в одной из ячеек.

Это означает, что вы успешно вернули все значения, соответствующие искомому значению.

Дополнительные ресурсы

В следующих руководствах объясняется, как выполнять другие распространенные задачи в Excel:

Excel: как использовать ВПР для возврата всех совпадений
Excel: используйте ВПР, чтобы вернуть пустое значение вместо нуля.
Excel: как использовать TRUE или FALSE в VLOOKUP