INDEX MATCH против VLOOKUP: поиск, который выдерживает новый столбец

1. СООТВЕТСТВИЕ само по себе: имя входное, число выходное.

Перед пояснением: ПОИСКПОЗ будет искать «Лампу» в списке из трех строк, где Лампа стоит последней. Скажите, что вы думаете, и он вернет обратно.

=MATCH("Lamp", Products[Product], 0)

3

Не «Лампа», не «Освещение», не 40. Просто 3. Вся работа МАТЧ состоит в том, чтобы сообщать где что-то есть, если считать от верхнего предела диапазона, который вы ему дали. Лампа — третья запись, поэтому ответ — 3.

Два из трёх аргументов очевидны: что найти и где искать. Третий – важный. 0 Означает точное совпадение, и это единственная настройка, которую вам следует вводить, пока у вас не появится конкретная причина не делать этого. Шестой раздел посвящен тому, что с вами делают другие настройки.

Само по себе число выглядит бесполезным, и это впечатление является причиной того, что люди пропускают ПОИСКПОЗ и обращаются к поиску, который делает все за один раз. Держись за номер. Это то, что делает отдых гибким.

2. ИНДЕКС сам по себе: входное число, выходное значение.

Перед объяснением: учитывая приведенную выше таблицу «Продукты», скажите, что INDEX(Products[Category], 3) возвращается до того, как вы прочтете вывод.

=INDEX(Products[Category], 3)

Lighting

INDEX принимает диапазон и позицию и возвращает то, что находится в этой позиции. Он вообще не ищет. Дайте ему 3, и он прочитает третью ячейку, независимо от того, является ли третья ячейка той, которую вы хотели.

Таким образом, ИНДЕКС намеренно глуп, а ПОИСКПОЗ намеренно узок, и ни один из них сам по себе бесполезен. Каждый знает ровно одно: ПОИСКПОЗ знает где, ИНДЕКС знает, что там.

3. Соединяем их вместе и произносим предложение при наборе текста

Введите число ПОИСКПОЗ прямо во второй аргумент ИНДЕКС.

=INDEX(Products[Category], MATCH("Lamp", Products[Product], 0))

Lighting

Прочтите его вслух в том порядке, в котором он выполняется, изнутри наружу, и он перестанет выглядеть как гнездо скобок: «Найдите лампу именно в столбце «Продукт» и дайте мне то, что находится в той же позиции в столбце «Категория». В этом предложении есть два имени столбца, как и в формуле. Каждое ИНДЕКСНОЕ СООТВЕТСТВИЕ, которое вы когда-либо писали, имеет одну и ту же форму.

Порядок на странице обратный порядку, который она выполняет, и это действительно неловко. Так что напечатайте его наизнанку. Сначала напишите MATCH отдельно и убедитесь, что он возвращает разумное число. Затем оберните INDEX( вокруг него. Отладка поиска, построенного в два этапа, занимает секунды, потому что вы всегда можете удалить ИНДЕКС и посмотреть, какую позицию, по мнению MATCH, он нашел.

Эта же пара работает с реальным столбцом таблицы заказов. Заказ 1001 — это стол, поэтому выбираем его категорию:

=INDEX(Products[Category], MATCH([@Product], Products[Product], 0))

Furniture

4. Ответ, который сидит слева

Перед объяснением: попробуйте написать VLOOKUP, который начинается с Продукт и возвращает категорию, используя таблицу Товары точно так, как она напечатана выше. Дайте этому тридцать секунд.

Нет возможности написать это. ВПР берет блок столбцов, ищет первый столбец этого блока и отсчитывается справа от ответа. Категория находится слева от «Продукт», поэтому она находится за пределами каждого блока, начинающегося с «Продукт». Запрос номера столбца 0 или -1 не меняет направление; оба просто терпят неудачу, и в моем случае оба вернулись #N/A.

=VLOOKUP("Lamp", Products, -1, FALSE)

#N/A

INDEX MATCH не имеет направления, поскольку поиск и выборка происходят с разными аргументами. MATCH получает указание на продукт и INDEX указывается на Категория, и для любого из них не имеет значения, находится ли Категория слева, справа или на другом листе.

Обычный обходной путь — изменить порядок справочной таблицы так, чтобы ключ был первым. Иногда это нормально. Часто таблица принадлежит кому-то другому, обновляется из системы или используется тремя другими книгами, которые ожидают текущего макета, и незаметное перемещение ее столбцов - это то, как вы нарушаете чужой понедельник.

5. Жестко запрограммированный номер, который устаревает

Это ошибка, которая на самом деле стоит денег, поэтому здесь это скорее бегство, чем предупреждение. Три формулы, задающие один и тот же вопрос: сколько нам стоит Лампа:

=VLOOKUP("Lamp", Products[[Product]:[Cost]], 3, FALSE)      22
=INDEX(Products[Cost], MATCH("Lamp", Products[Product], 0))   22
=XLOOKUP("Lamp", Products[Product], Products[Cost])           22

Все трое согласны. Теперь кто-то добавляет столбец «Поставщик» в справочную таблицу между Список цен и Расходы. Больше ничего не меняется. Те же три формулы, нетронутые:

=VLOOKUP("Lamp", Products[[Product]:[Cost]], 3, FALSE)      Northlight
=INDEX(Products[Cost], MATCH("Lamp", Products[Product], 0))   22
=XLOOKUP("Lamp", Products[Product], Products[Cost])           22

ВПР теперь возвращает имя поставщика там, где раньше была стоимость. Он не сделал ничего плохого: ему было приказано посчитать три столбца поперек, он посчитал три столбца поперек, а третий столбец теперь — «Поставщик». Число 3 было фактом макета в тот день, когда оно было напечатано, замороженным в формуле.

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

INDEX MATCH сохраняется, потому что ничто в нем не относится к позиции, которую выбрал человек. Products[Cost] означает столбец «Стоимость» там, где сейчас находится столбец «Стоимость». Если вы предпочитаете использовать VLOOKUP, вы можете добиться большей эффективности, заменив число на ПОИСКПОЗ в строке заголовка: MATCH("Cost", Products[#Headers], 0), что является самым ценным изменением во всем этом руководстве.

6. Четвертый аргумент, который возвращает неправильный ответ без ошибок

Перед пояснением: в нашей таблице «Продукты» стол, стул и лампа перечислены в том порядке, который не является алфавитным. Вот VLOOKUP для цены стола с последним аргументом, установленным в TRUE. Правильный ответ — 220. Предскажите, что он вернет.

=VLOOKUP("Desk", Products[[Product]:[Cost]], 2, TRUE)

85

85 - цена стула. Ни ошибок, ни предупреждений, ни цвета. Именно это делает последний аргумент, когда он имеет значение ИСТИНА, и то же самое происходит, когда вы его полностью опускаете.

ИСТИНА означает приблизительное совпадение, а приблизительное совпадение предполагает одно предположение: столбец поиска отсортирован по возрастанию. При этом предположении он может остановиться раньше и принять последнее переданное значение, которое не было слишком большим, что действительно полезно для группирования, когда вы хотите «в любую скобку, в которую попадает это число». Наведите его на неотсортированный список, и предположение окажется ложным, поэтому он остановится не в том месте и с полной уверенностью сообщит обо всем, что там было.

Урок не в том, что ИСТИНА – это плохо. Дело в том, что поиск выполняет две разные задачи, и им нужны разные настройки. Поиск определенного известного ключа — это работа с точным совпадением, и для этого требуется FALSE в ВПР или 0 в МАТЧ, набираемый каждый раз. Определение того, в какой диапазон попадает число, — это задача приблизительного сопоставления, и для нее требуется отсортированная таблица, которую вы специально создали.

ПОИСКПОЗ имеет ту же трехстороннюю настройку и ту же ловушку: его третий аргумент по умолчанию равен 1, что является приблизительным. Ввод 0 это не обязательная вежливость. В этом разница между ответом и предположением.

7. Двусторонний поиск: сразу по строке и столбцу

Второй аргумент INDEX — это позиция строки. Он также принимает третий аргумент — позицию столбца, и оба они могут быть ПОИСКПОЗ. Это превращает любую сетку в нечто, что можно запросить по двум меткам.

=INDEX(Products[[ListPrice]:[Cost]],
       MATCH("Chair", Products[Product], 0),
       MATCH("Cost",  Products[[#Headers],[ListPrice]:[Cost]], 0))

52

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

Это случай, который по-прежнему относится к INDEX MATCH, потому что двусторонний поиск - это не то, что VLOOKUP вообще может выразить, и XLOOKUP должен быть вложен внутри себя, чтобы управлять им. Один ИНДЕКС с двумя СОВПАДЕНИЯМИ читать легче, чем любую из альтернатив.

Представьте на секунду свои собственные справочные таблицы: прейскурант, матрицу доставки, таблицу налогов, таблицу границ оценок. В каком из них вы сейчас просматриваете информацию, прокручивая и указывая на экран? Это тот, который конвертируется первым.

8. К чему МАТЧ привередлива, и что она прощает

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

Случай не имеет значения. Поиск "lamp" в списке, содержащем Lamp возвращает 3, то же самое, что и поиск "Lamp". При сравнении текста в Excel здесь игнорируются заглавные буквы, поэтому ключ, поступающий в другом регистре, все равно находит свою строку.

Пространство имеет огромное значение. Поиск "Lamp ", с одним пробелом в конце, возвращает #N/A.

=MATCH("lamp",  Products[Product], 0)      3
=MATCH("Lamp ", Products[Product], 0)      #N/A

Эта пара объясняет большинство неудачных загадочных поисков. Значение на экране выглядит идентично значению в справочной таблице, поскольку завершающий пробел невидим, а формула совершенно правильна, поскольку это разные строки. Прежде чем переписывать поиск, который «должен работать», заключите ключ в TRIM() и посмотрите, исчезнет ли проблема.

Третье несоответствие столь же распространено, и его еще труднее обнаружить: ключ, представляющий собой число в одной таблице и текст в другой. 1001 и "1001" никогда не совпадать. Суть в том, что неработающие ключи всегда имеют числовой вид, и решение состоит в том, чтобы заставить одну сторону согласиться с другой, а не продолжать корректировать формулу. Когда ключи различаются более беспорядочно, чем пробелы и заглавные буквы, это более сложная работа с именем: Разрешение объекта.

9. XLOOKUP, и когда INDEX MATCH все еще занимает свое место

XLOOKUP выполняет эту работу с помощью одной функции, по умолчанию использует точное совпадение и принимает столбец поиска и столбец возврата как два отдельных аргумента, что является тем же разделением, которое делает ИНДЕКС ПОИСКПОЗ надежным.

=XLOOKUP("Lamp", Products[Product], Products[Category])

Lighting

Если XLOOKUP доступен вам и всем, кто открывает ваш файл, используйте его для обычного однонаправленного поиска. Он короче, и его настройки по умолчанию являются безопасными.

Три ситуации по-прежнему относятся к INDEX MATCH. Книга, которую нужно открыть в более старой версии Excel или в другой программе для работы с электронными таблицами, где XLOOKUP отсутствует, а формула появляется в виде #NAME?. Двусторонний поиск из седьмого раздела. И чтение чужой модели — это честная причина изучить ее, даже если вы никогда ее не вводите: ИНДЕКС-ПОИСКПОЗ присутствует в миллионах действующих книг, и вам будет предложено изменить одну из них.

Ни один из трех поисков не защитит вас от ключа, которого на самом деле нет. Все они возвращаются #N/A, что является правильным поведением, и его сокрытие является собственной ошибкой. То, что нужно обернуть вокруг него и когда скрытие ошибки является неверным шагом, — это отдельная страница.

Крайние случаи, которые незаметно нарушают поиск

Шесть, каждая из которых стоила кому-то дня.

Ключ появляется дважды в справочной таблице. MATCH, VLOOKUP и XLOOKUP останавливаются на первом совпадении и никогда не упоминают второе. Если ваша справочная таблица может содержать дубликаты, подсчитайте их, прежде чем доверять поиску: COUNTIF в ключевом столбце, и все, что выше 1, — это решение, которое вам придется принять, а не Excel, который должен принять за вас.

Диапазоны имеют разную длину. ИНДЕКС в столбце из 4 строк с помощью ПОИСКПОЗ в столбце из 5 строк успешно вернет неправильную строку или #REF! если позиция выходит за пределы. Наведите оба на целые именованные столбцы, и они не смогут разойтись.

Полузакрытые ссылки. Копирование MATCH(A2, B2:B4, 0) вниз по листу перемещается диапазон поиска до B3:B5, поэтому последние строки ищут диапазон, в котором отсутствуют записи. Заблокируйте его с помощью $B$2:$B$4или используйте именованную таблицу, где диапазон вообще не может перемещаться.

Ан #N/A это спряталось слишком рано. Обертывание поиска чем-то, что превращает ошибки в пробел или ноль, делает лист чистым и делает несовпадающий ключ невидимым. Сначала посчитайте промахи, решите, что они означают, и только потом решайте, что отображать.

Поиск по целому столбцу в большой таблице. MATCH(A2, D:D, 0) сканирует миллион ячеек в строке. В нескольких сотнях рядов этого никто не замечает; на пятидесяти тысячах файл начинает пересчитываться за секунды. Укажите на таблицу, а не на букву столбца.

Справочная таблица, которая выросла. Фиксированный диапазон, например $B$2:$B$4 никогда не видит четвертого товара, поэтому новинки молча возвращаются #N/A навсегда. Это тот же аргумент, что и Ctrl+Т повсюду в этом наборе руководств: именованная таблица увеличивается, а типизированный диапазон — нет.