Диапазон значений поиска с другого листа или из другой книги

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

Поиск значений ВПР с другого листа

В этом разделе показано, как искать значения с помощью ВПР с другого листа в Excel.

Универсальная формула

=VLOOKUP(lookup_value,sheet_range,col_index,[range_lookup])

Аргументы

• Lookup_value (обязательно): значение, которое вы ищете. Оно должно находиться в первом столбце диапазона на листе.
• Sheet_range(обязательно): Диапазон ячеек на определённом листе, включающий два или более столбцов, среди которых есть как столбец с искомым значением, так и столбец с возвращаемым значением.
• Col_index (обязательно): укажите номер столбца (целое число) в таблице-массиве, из которого будет возвращено найденное значение.
• Range_lookup (необязательно): логическое значение, определяющее, будет ли функция ВПР искать точное или приближённое совпадение.

Подробнее о функции ВПР.

В данном случае мне нужно Диапазон значений поиска в диапазоне B3:C14 на листе с именем «Sales»,и вернуть соответствующие результаты на лист Summary.

1. Выберите пустую ячейку на листе Summary, вставьте в неё приведённую ниже формулу и нажмите клавишу Enter.

=VLOOKUP($B5,Sales!B3:C14,2,0)

Примечания:

• B5— это ссылка на ячейку, содержащую искомое значение;
• Sales— имя листа, из которого выполняется поиск значения;
• B3:C14— диапазон, содержащий столбец с Диапазон значений поиска и столбец с возвращаемыми значениями;
• 2означает, что возвращаемое значение находится во втором столбце диапазона B3:C14;
• 0 означает, что функция ВПР вернёт точное совпадение. Если точное совпадение не найдено, будет возвращена ошибка #Н/Д.

2. Затем перетащите вниз маркер заполнения, чтобы получить все результаты.

Поиск значений ВПР из другой книги

Предположим, есть книга с именем «Sales report». Чтобы напрямую Диапазон значений поиска на определённом листе этой книги, даже если она закрыта, выполните следующие действия.

Универсальная формула

=VLOOKUP(lookup_value,[workbook]sheet!range,col_index,[range_lookup])

Аргументы

• Lookup_value(обязательно): Значение, которое вы ищете. Оно должно находиться в первом столбце диапазона листа.
• [workbook]sheet!range(обязательно): Диапазон ячеек на листе указанной книги, включающий два или более столбцов, среди которых — столбец с искомым значением и столбец со значением для возврата.
• Col_index(обязательно): Номер Укажите столбец (целое число) в массиве таблицы, из которого будет возвращено найденное значение.
• Range_lookup(необязательно): Логическое значение, определяющее, будет ли функция ВПР возвращать точное или приближённое совпадение.

1. Выберите пустую ячейку, вставьте в неё приведённую ниже формулу и нажмите клавишу Enter.

=VLOOKUP($B5,'[Sales report.xlsx]Sales'!B3:C14,2,0)

Примечание: Если точное совпадение не найдено, функция ВПР вернёт ошибку #Н/Д.

2. Затем перетащите маркер заполнения вниз, чтобы применить формулу ко всем нужным ячейкам и мгновенно получить полный набор результатов. См. снимок экрана:

Связанная функция

Связанные формулы