Как использовать комбинацию vlookup match?
Сочетание ВПР и ПОИСКПОЗ похоже на суперсилу. Ну, как мы все знаем, ВПР — одна из самых популярных функций .
ВЕРНО?
Это может помочь вам быстро найти значение в столбце. Но когда вы будете использовать его все больше и больше, вы столкнетесь с некоторыми проблемами. И самая большая проблема в том , что это не динамично.
В ВПР col_index_no — это статическое значение, поэтому ВПР не работает как динамическая функция . И здесь вам нужно объединить ВПР с ПОИСКПОЗ.
Если вы работаете с данными из нескольких столбцов , изменить ссылку на них затруднительно, вам придется делать это (изменять номер столбца) вручную.
Лучший способ решить эту проблему — использовать функцию ПОИСКПОЗ в ВПР для col_index_number. Сегодня в этой статье я расскажу вам все, что вам нужно знать, чтобы использовать эту формулу комбо.
Проблемы с ВПР
Я нашел две основные причины для создания комбинации этих двух функций.
1. Статическая ссылка
Просто посмотрите на таблицу данных ниже, где указаны продажи за 12 месяцев для четырех разных сотрудников.
Теперь предположим, что вы хотите найти распродажу «Джона» за февраль. Формула должна быть такой.
=VLOOKUP("May",A1:E13,2,0)И в этой формуле вы указали 2 как col_index_num, потому что продажа Джона указана во втором столбце. Но что вы будете делать, если ваш начальник скажет вам узнать стоимость продажи «Питера»?
Вам необходимо изменить значение в col_index_num, поскольку оно не является динамическим.
2. Добавьте или удалите столбцы
Теперь подумайте по-другому. Вам нужно добавить новый столбец для нового сотрудника непосредственно перед столбцом Джона.
И здесь номер столбца Джона равен 3, а результат вашей формулы неверен.
Опять же, поскольку col_index_num является статическим значением, вам придется вручную изменить его с 2 на 3 и еще раз, если вам нужно что-то еще.
На данный момент вы ясно знаете одну вещь: вам нужно сделать col_index_num динамическим. И для этого лучше всего заменить ее функцией ПОИСКПОЗ.
Почему функция соответствия
Прежде чем комбинировать ВПР и ПОИСКПОЗ, вам необходимо понять функцию сопоставления и то, как она работает. Основное использование ПОИСКПОЗ — найти номер ячейки искомого значения в диапазоне.
Синтаксис: ПОИСКПОЗ(искомое_значение,просматриваемый_массив,[тип_соответствия])
В основном он имеет три аргумента: значение поиска, диапазон для поиска значения и тип соответствия для указания точного или нечеткого соответствия.
Например, в приведенных ниже данных я ищу имя «Джон» с помощью функции сопоставления из строки заголовка.
И в результате он вернул 2, потому что имя находится во второй ячейке строки.
ВПР и ПОИСКПОЗ вместе
Теперь пришло время объединить ВПР и ПОИСКПОЗ. Итак, продолжим наш предыдущий пример.
Сначала давайте создадим формулу, используя обе функции, тогда мы поймем, как эти две работают вместе.
Шаги для создания этой комбинированной формулы:
- Сначала в одну ячейку введите название месяца, а в другую — имя сотрудника.
- После этого введите формулу ниже в третью ячейку.
=VLOOKUP(C15,A1:E13,MATCH(C16,A1:E1,0),0)
В приведенной выше формуле вы использовали ВПР, чтобы найти месяц МАЙ, а для аргумента col_index_num вы использовали функцию сопоставления вместо статического значения.
А в функции сопоставления вы использовали «Джон» (имя сотрудника) в качестве значения поиска.
Здесь функция сопоставления вернула номер ячейки для «Джона» в строке выше. После этого ВПР использовал этот номер ячейки для возврата значения.
Проще говоря, функция ПОИСКПОЗ сообщает ВПР номер столбца, в котором нужно получить значение.
Проблемы решены?
Выше вы узнали о двух разных проблемах, вызванных статическим col_index_num .
И для этого вы объединили ВПР и ПОИСКПОЗ. Теперь нам нужно проверить, решены ли эти проблемы или нет.
1. Статическая ссылка
Вы указали имя сотрудника в функции сопоставления, чтобы получить номер столбца для VLOOKUP.
Когда вы меняете имя сотрудника в ячейке, функция сопоставления меняет номер столбца. А когда вам нужно получить значение другого сотрудника, вам нужно изменить имя сотрудника в ячейке.
Таким образом, у вас есть динамический col_index_number.
Наконец, вам не придется менять формулу снова и снова.
2. Добавьте или удалите столбцы
Перед добавлением нового столбца данные Джона находились во втором столбце, и функция сопоставления возвращала значение 2. А после вставки нового столбца данные Джона находились в третьем столбце, и совпадение возвращало значение 3.
Когда вы добавляете новый столбец для нового сотрудника, значение формулы не изменяется, поскольку функция сопоставления обновляет его значение.
Таким образом, вы всегда будете получать правильный номер столбца, даже если вставите/удалите столбец. Функция ПОИСКПОЗ вернет правильный номер столбца.