ВПР с МАТЧЕМ | Создавайте гибкие формулы с VLOOKUP MATCH

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

Совместите ВПР с Match

Формула vlookup - это наиболее часто используемая функция, которая используется для поиска и возврата либо того же значения в указанном индексе столбца, либо значения из другого индекса столбца со ссылкой на совпадающее значение из первого столбца. Основная проблема, с которой сталкивается при использовании vlookup, заключается в том, что индекс столбца, который необходимо указать, является статическим и не имеет динамических функций. Особенно, если вы работаете с несколькими критериями, которые требуют от вас изменения индекса ссылочного столбца вручную. Таким образом, эта потребность удовлетворяется за счет использования формулы «ПОИСКПОЗ», чтобы лучше контролировать часто меняющийся индекс столбца в формуле ВПР.

Формула VLookup и Match

# 1 - Формула VLOOKUP

Формула функции ВПР в Excel

Здесь все вводимые аргументы являются обязательными.

Lookup_value - здесь необходимо ввести ссылочную ячейку или текст в двойных кавычках, чтобы его можно было идентифицировать в диапазоне столбцов.
Массив таблиц - этот аргумент требует, чтобы был введен диапазон таблицы, в котором следует искать Lookup_value, а данные, которые нужно получить, находятся в определенном диапазоне столбцов.
Col_index_num - в этом аргументе необходимо ввести номер индекса столбца или счетчик столбца из первого ссылочного столбца, из которого необходимо извлечь соответствующее значение из той же позиции, что и значение, найденное в первом столбце.
[Range_lookup] - этот аргумент дает два варианта.
ИСТИНА - приблизительное совпадение: - Аргумент может быть введен как ИСТИНА или числовое значение «1», которое возвращает приблизительное совпадение, соответствующее ссылочному столбцу или первому столбцу. Кроме того, значения в первом столбце массива таблицы должны быть отсортированы по возрастанию.
FALSE - точное совпадение: - здесь вводимый аргумент может быть FALSE или числовым «0». Эта опция вернет только точное совпадение значения, соответствующего определяемому из позиции в первом диапазоне столбцов. Если не найти значение из первого столбца, будет возвращено сообщение об ошибке «# N / A».

# 2 - Формула матча

Функция Match возвращает позицию ячейки значения, введенного для данного массива таблицы.

Все аргументы синтаксиса обязательны.

Lookup_value - здесь введенный аргумент может быть либо ссылкой на ячейку значения, либо текстовой строкой с двойными кавычками, позиция ячейки которой требуется вытащить.
Lookup_array - необходимо ввести диапазон массива для таблицы, значение или содержимое ячейки которой требуется идентифицировать.
[тип соответствия] - этот аргумент предоставляет три варианта, как описано ниже.
«1-Меньше чем» - здесь необходимо ввести числовой аргумент «1», который вернет значение, которое меньше или равно искомому значению. А также поисковый массив должен быть отсортирован в порядке возрастания.
«0-точное совпадение» - здесь вводимый аргумент должен быть числовым «0». Эта опция вернет точную позицию совпадающего значения поиска. Однако поисковый массив может быть в любом порядке.
«-1-Больше, чем» - вводимый аргумент должен быть числовым «-1». Третий вариант находит наименьшее значение, которое больше или равно искомому значению. Здесь порядок поиска в массиве должен быть расположен в порядке убывания.

# 3 - ВПР с формулой MATCH

= ВПР (искомое_значение, массив_таблицы, ПОИСКПОЗ (искомое_значение, искомое_массив, [тип_сопоставления]), [поиск по диапазону])

Как использовать ВПР с формулой соответствия в Excel?

Приведенный ниже пример поможет понять работу формулы vlookup и сопоставления при объединении.

Вы можете скачать этот VLookup с шаблоном Match Excel здесь - VLookup with Match Excel Template

Обратите внимание на приведенную ниже таблицу данных, в которой описаны характеристики приобретаемого транспортного средства.

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

Шаг №1 - Давайте применим формулу vlookup на индивидуальном уровне, чтобы получить результат.

Результат показан ниже:

Здесь значение поиска относится к $ B9, что является моделью «E», а массив поиска задается как диапазон таблицы данных с абсолютным значением «$», индекс столбца относится к столбцу «4», который является счетчиком для столбец «Тип» и поиск по диапазону получают точное совпадение.

Таким образом, для возврата значения столбца «Топливо» применяется следующая формула .

Результат показан ниже:

Здесь значение поиска с абсолютной строкой «$», примененное к значению поиска, и lookup_array помогает исправить ссылочную ячейку, даже если формула копируется в другую ячейку. В столбце «Топливо» нам нужно изменить индекс столбца на «5», поскольку изменяется значение, из которого необходимо получить данные.

Шаг № 2 - Теперь давайте применим формулу Match, чтобы получить позицию для данного значения поиска.

Результат показан ниже:

Как видно на скриншоте выше, здесь мы пытаемся получить позицию столбца из массива таблицы. В этом случае номер извлекаемого столбца называется ячейкой C8, которая является столбцом «Тип», а диапазон поиска для поиска задается как диапазон заголовков столбцов, а типу соответствия дается точное совпадение как « 0 ”.

Таким образом, приведенная ниже таблица даст желаемый результат для позиций столбца «Топливо».

Теперь в качестве столбца для поиска используется ячейка D8, а желаемый индекс столбца возвращается равным «5».

Шаг № 3 - Теперь формула Match будет использоваться в функции vlookup для получения значения из указанной позиции столбца.

Результат показан ниже:

В приведенной выше формуле функция сопоставления помещается вместо параметра индекса столбца функции vlookup. Здесь функция сопоставления идентифицирует ссылочную ячейку значения поиска «C8» и вернет номер столбца через данный массив таблицы. Эта позиция столбца будет служить в качестве входных данных для аргумента индекса столбца в функции vlookup. Что, в свою очередь, поможет vlookup определить значение, которое должно быть возвращено из номера индекса результирующего столбца?

Точно так же мы применили vlookup с формулой соответствия для столбца «Топливо».

Результат показан ниже:

Таким образом, мы можем применить эту комбинированную функцию для других столбцов «Тип» и «Топливо».

То, что нужно запомнить

ВПР может применяться к значениям поиска только в его крайней левой части. Любые значения, представленные для поиска в правой части таблицы данных, вернут значение ошибки «# N / A».
Диапазон table_array, введенный во втором аргументе, должен быть абсолютной ссылкой на ячейку «$», это сохранит фиксированный диапазон массива таблицы при применении формулы поиска к другим ячейкам, в противном случае ссылочные ячейки для диапазона массива таблицы переместятся на следующую ячейку ссылка.
Значение, введенное в поисковое значение, не должно быть меньше наименьшего значения в первом столбце массива таблицы, иначе функция вернет значение ошибки «# N / A».
Перед применением приблизительного совпадения «ИСТИНА» или «1» в последнем аргументе всегда не забывайте сортировать массив таблицы в порядке возрастания.
Функция сопоставления возвращает только позицию значения в массиве таблицы vlookup и не возвращает значение.
Если функция сопоставления не может определить положение искомого значения в массиве таблицы, тогда формула возвращает «# N / A» в значении ошибки.
Функции Vlookup и match не чувствительны к регистру при сопоставлении значения поиска с совпадающим текстовым значением в массиве таблицы.