VLOOKUP против XLOOKUP: какой использовать и как переключаться

1. Что на самом деле делает поиск

Перед пояснением: у вас есть лист заказа с идентификаторами сотрудников, а на другом листе - таблица выше. Какие две отдельные вещи должна сделать формула, чтобы поставить отдел каждого сотрудника рядом с его заказами?

Он должен найти строку, а затем вернуть значение. Вот и вся работа. Поиск берет имеющееся у вас значение, идентификатор, например 1008, находит строку в другой таблице, где находится это значение, и возвращает что-то еще из этой же строки, например отдел. Найдите строку, верните значение. Каждая когда-либо написанная формула поиска представляет собой эти два хода.

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

Если вы знаете SQL, поиск — это соединение одной рукой. Курс Excel уроки поиска и Руководство по SQL-СОЕДИНЕНИЯ одна и та же идея в двух диалектах.

2. ВПР, аргумент за аргументом

Перед пояснением: VLOOKUP принимает четыре аргумента, и только один из них — это число, которое нужно посчитать вручную. Какой аргумент, по вашему мнению, вызывает больше всего проблем?

Вот формула, которая извлекает отдел Джии Тран из таблицы выше.

=VLOOKUP(1008, A2:D11, 3, FALSE)

Прочитайте слева направо. 1008 это значение, которое нужно найти. А2: Д11 — это вся таблица для поиска, и VLOOKUP будет искать 1008 только в первом столбце этого диапазона. 3 означает вернуть третий столбец диапазона, считая слева, то есть столбец C, Отдел. FALSE означает, что совпадение должно быть точным.

Запустите его, и вы получите Finance. Измените 3 на 4, и вы получите 67 250 — зарплату. Оба варианта верны, и я проверил их по таблице вручную и с помощью компьютера.

Учтенный аргумент, 3, является тем, который вызывает больше всего поломок, а четвертый аргумент находится на втором месте, потому что люди его не учитывают. В следующем разделе показаны оба сбоя.

3. Три причины, по которым VLOOKUP идет не так, как надо

Перед объяснением: слово, за которое следует держаться, молча. Сообщение об ошибке раздражает, но честно. Как бы выглядел сбой поиска, если бы он вообще не выдавал ошибок?

Казалось бы, нормальный ответ. Вот три способа, которыми VLOOKUP создает его.

Ошибка 1: столбцы подсчитываются, поэтому вставка столбца нарушает его.

3 в =VLOOKUP(1008, A2:D11, 3, FALSE) не привязан к столбцу «Отдел». Он прикреплен к позиции три. Теперь коллега вставляет столбец «Электронная почта» между «Имя» и «Отдел», что совершенно нормально. Третья позиция теперь — электронная почта. Формула возвращает gia@co.com где раньше был отдел, без ошибок, и каждая формула, указывающая на эту таблицу, мгновенно смещается.

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

Ошибка 2: по умолчанию используется приблизительное совпадение, поэтому отсутствующий идентификатор возвращает неправильное значение строки.

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

=VLOOKUP(1005, A2:D11, 3, FALSE)   → #N/A
=VLOOKUP(1005, A2:D11, 3)          → Operations

Первый говорит правду: числа 1005 не существует. Второй спускается на номер 1004, Дана Рейес, и передает вам свой отдел. Operations, как будто это был ответ. Вместо этого попросите столбец 4, и вы получите ее зарплату в размере 49 800 долларов, прикрепленную к несуществующему сотруднику. Никаких ошибок, никаких предупреждений, чистый, уверенный неправильный номер. Попросите недостающий ID 1011 тем же способом и получите IT, из ряда Айви Нэш.

Скажите своими словами, почему приблизительная версия вернула Шефу, прежде чем читать дальше. Если вы можете объяснить слайд до 1004, вы достаточно хорошо понимаете причину неудачи, чтобы заметить ее в рабочей тетради.

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

Ошибка 3: это может только выглядеть правильно

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

4. XLOOKUP и какой сбой устраняет каждый аргумент

Прежде чем объяснить: зная три ошибки, что бы вы изменили в аргументах ВПР, если бы вам разрешили их перепроектировать?

Редизайн Майкрософт назывался XLOOKUP, и он выглядит как список исправлений. Вот тот же поиск.

=XLOOKUP(1008, A2:A11, C2:C11)

Четыре аргумента имеют значение, и каждый из них закрывает дыру.

искомое_значение — это значение, которое нужно найти, такое же, как и раньше: 1008.

искомый_массив — столбец для поиска с прямым именем: A2:A11. Поскольку вы сами даете имя столбцу поиска, он может располагаться в любом месте листа, в том числе справа от ответа. =XLOOKUP("Gia Tran", B2:B11, A2:A11) смотрит влево и возвращает 1008. Это устраняет ошибку 3.

return_array — это столбец, который нужно вернуть, он также имеет прямое имя: C2:C11. В формуле нет учитываемой позиции. Вставьте столько столбцов, сколько захотите, и C2:C11 будет следовать за столбцом «Отдел» везде, где Excel его смещает, поскольку Excel обновляет ссылки и никогда не обновляет введенный номер 3. Это устраняет ошибку 1.

if_not_found — четвертый аргумент, и он необязателен в безопасном направлении. Во-первых, значением по умолчанию XLOOKUP является точное совпадение, поэтому =XLOOKUP(1005, A2:A11, C2:C11) возвращает #N/A вместо того, чтобы скатиться к Дане Рейес. Опасное значение по умолчанию исчезло, что устраняет ошибку 2. Во-вторых, если вам нужно что-то более дружелюбное, чем #Н/Д, вы указываете это в самой формуле:

=XLOOKUP(1005, A2:A11, C2:C11, "Not found")   → Not found

С помощью VLOOKUP вы добились такого поведения, обернув все это в другую функцию: =IFERROR(VLOOKUP(...), "Not found"), который также поглощает все остальные ошибки, которые может возникнуть в формуле, включая те, которые вам нужно было увидеть. if_not_found улавливает ровно одно условие — пропущенное значение — и пропускает реальные ошибки.

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

5. Полное до и после

Та же таблица, та же задача: укажите отдел каждого сотрудника рядом с его идентификатором и скажите что-нибудь разумное, когда идентификатора нет в файле.

До

=IFERROR(VLOOKUP(E2, A2:D11, 3, FALSE), "Not found")

Это работает сегодня. Он также имеет три долга по содержанию. 3 ломается в тот день, когда кто-то вставляет столбец. ЛОЖЬ необходимо запоминать каждый раз, потому что ее забывание не приводит к ошибке. А оболочка ЕСЛИОШИБКА с радостью скроет действительно неработающую ссылку за словами «Не найдено».

после

=XLOOKUP(E2, A2:A11, C2:C11, "Not found")

Короче, и все долги пропали. Столбец поиска и столбец возврата имеют имена, совпадение по умолчанию является точным, и только действительно отсутствующий идентификатор выдает сообщение «Не найдено». Для E2 = 1008 обе формулы возвращают Финансы. Для E2 = 1005 оба возвращают значение «Не найдено». Разница в том, что произойдет с каждой формулой в течение следующего года редактирования.

6. Крайние случаи, которые нарушают поиск в реальных книгах

Перед объяснением: поиск возвращает #Н/Д для идентификатора, который вы можете увидеть прямо здесь, в таблице. Что бы вы проверили в первую очередь?

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

Номер сохраняется в виде текста. Это ответ на предварительный вопрос, и он самый распространенный. Ячейка, показывающая 1008, может содержать текст «1008», часто помеченный маленькой зеленой угловой отметкой. Число 1008 и текст «1008» не совпадают ни по одной функции. Исправьте столбец один раз, используя данные, текст в столбцы, или умножьте идентификаторы на 1, вместо того чтобы исправлять каждую формулу.

Повторяющиеся идентификаторы возвращают только первое совпадение. Обе функции останавливаются при первом попадании сверху вниз. Если 1008 появляется дважды, вы получаете значение верхней строки и не предупреждаете о существовании второй строки. Если дубликаты вообще возможны, сначала посчитайте их. Руководство по COUNTIFS показывает однострочную проверку, и поиск дубликатов в SQL это тот же аудит в базе данных.

Невидимые пробелы разрывают матч. «Гиа Тран» с пробелом в конце имеет другое значение, чем «Гиа Тран». Данные, вставленные из других систем, постоянно содержат их. TRIM по дороге в загадку #Н/Д позже.

В старом Excel нет XLOOKUP. XLOOKUP существует в Excel 2021, Excel 365 и текущей версии Excel в Интернете. В книге, открытой в Excel 2019 или более ранней версии, отображается #NAME? где был каждый XLOOKUP. Если ваш файл должен переместиться в неизвестные версии, это реальная причина написать VLOOKUP, и он принадлежит к ответвлению ниже.