VLOOKUP с Match

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

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

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

vlookup-with-match

Формула VLookup и Match

# 1 — Формула VLOOKUP

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

Vlookup и формула соответствия

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

  • Lookup_value Здесь необходимо ввести ссылочную ячейку или текст в двойных кавычках, чтобы его можно было идентифицировать в диапазоне столбцов.
  • Таблица массив Этот аргумент требует, чтобы был введен диапазон таблицы, в котором должен выполняться поиск Lookup_value, а данные, которые должны быть получены, находятся в определенном диапазоне столбцов.
  • Col_index_num В этом аргументе необходимо ввести номер индекса столбца или количество столбцов из первого ссылочного столбца, из которого необходимо извлечь соответствующее значение из той же позиции, что и значение, найденное в первом столбце.
  • [Range_lookup] — Этот аргумент дает два варианта.
  • ПРАВДА — Приблизительное совпадение: — Аргумент может быть введен как ИСТИНА или числовое значение «1», которое возвращает приблизительное совпадение, соответствующее ссылочному столбцу или первому столбцу. Причем значения в первом столбце массива таблицы должны быть отсортированы в порядке возрастания.
  • ЛОЖНЫЙ — Точное совпадение: — Здесь вводимый аргумент может быть либо ЛОЖЬ, либо числовым «0». Эта опция вернет только точное совпадение значения, соответствующего определяемому из позиции в первом диапазоне столбцов. Если не найти значение из первого столбца, будет возвращено сообщение об ошибке «# N / A».

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

Vlookup и матч Формула 1

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

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

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

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

= ВПР (искомое_значение; массив_таблиц; ПОИСКПОЗ (искомое_значение; искомый_массив; [match_type]),[range lookup])

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

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

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

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

Примеры формул Vlookup и Match 1

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

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

Примеры формул Vlookup и Match 1-2

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

Примеры формул Vlookup и сопоставления 1-3

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

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

Примеры Vlookup и Match Formula 1-4

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

Примеры формул Vlookup и Match 1-5

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

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

Примеры формул Vlookup и Match 1-6

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

Примеры формул Vlookup и Match 1-7

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

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

Примеры формул Vlookup и Match 1-8

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

Примеры формул Vlookup и Match 1-9

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

примеры 1-10

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

примеры 1-11

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

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

примеры 1-12

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

примеры 1-13

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

примеры 1-14

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

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

УЗНАТЬ БОЛЬШЕ >>

Похожие записи

Добавить комментарий

Ваш адрес email не будет опубликован.