Промпт для ВПР или XLOOKUP должен описывать не только диапазоны. Чату нужно знать версию Excel, где находится ключ, что возвращать, допустимы ли дубли и что считать правильным результатом при отсутствии совпадения.
Ниже — заполненные запросы и возможные ответы. В другой модели формула или объяснение могут выглядеть иначе. Обычный чат не открывает книгу и не применяет формулу: он готовит текст, который пользователь проверяет в Excel.
Короткий ответ: как составить промпт для ВПР или XLOOKUP
Укажите пять вещей: версию и локаль Excel, ячейку с ключом, диапазон поиска, возвращаемый диапазон и правило для отсутствующих или повторяющихся значений. Для обычного поиска по артикулу или ID запрашивайте точное совпадение. После ответа проверьте формулу на существующем, отсутствующем, пустом и повторяющемся ключе.
Что передать в чат перед поиском
Назовите исходную ячейку, диапазон ключей, возвращаемый диапазон, тип совпадения и правила для пустого, отсутствующего или повторяющегося ключа. Версия критична: Microsoft указывает, что XLOOKUP недоступен в Excel 2016 и 2019.
Этой карточки хватает, чтобы чат не угадывал направление поиска и бизнес-правило.
Короткие промпты для ВПР и XLOOKUP
1. Выбрать совместимую функцию
Промпт
Excel 2019, русская локаль, разделитель «;». В «Отчёт»!A2 находится артикул. На листе «Товары» артикулы — в D2:D5000, цены — в B2:B5000. Ключи уникальны, совпадение точное, при отсутствии нужно «Не найдено». Выбери между ВПР, XLOOKUP и ИНДЕКС+ПОИСКПОЗ и дай одну формулу для E2.Возможный ответ нейросети
Подходит ИНДЕКС+ПОИСКПОЗ: XLOOKUP нет в Excel 2019, а ВПР не возвращает значение слева от ключа.
=ЕСЛИОШИБКА(ИНДЕКС('Товары'!$B$2:$B$5000;ПОИСКПОЗ(A2;'Товары'!$D$2:$D$5000;0));"Не найдено")2. Точный ВПР
Промпт
Excel 2019, русская локаль. На листе «Заказы» код клиента находится в B2. На листе «Клиенты» таблица A2:D2000: код в A, менеджер в D. Верни менеджера в F2. Коды уникальны, нужно точное совпадение, при отсутствии — «Нет клиента». Дай формулу ВПР и поясни последний аргумент.Возможный ответ нейросети
=ЕСЛИОШИБКА(ВПР(B2;'Клиенты'!$A$2:$D$2000;4;ЛОЖЬ);"Нет клиента")
ЛОЖЬ задаёт точное совпадение. Число 4 означает, что результат берётся из четвёртой колонки указанного диапазона.ВПР требует, чтобы ключ находился в первом столбце выбранного диапазона. Если четвёртый аргумент пропустить, функция использует приблизительное совпадение, поэтому для поиска по ID безопаснее явно задавать ЛОЖЬ.
3. XLOOKUP с пустым результатом
Промпт
Excel Microsoft 365, английские имена функций, разделитель «,». В A2 — SKU. На листе Catalog SKU находятся в A2:A10000, остаток — в F2:F10000. Верни остаток в B2; если SKU не найден, оставь пустую строку. Совпадение точное, ключи уникальны. Дай только формулу и короткую расшифровку.Возможный ответ нейросети
=XLOOKUP(A2,Catalog!$A$2:$A$10000,Catalog!$F$2:$F$10000,"",0)
Формула ищет точный SKU из A2 и возвращает остаток; четвёртый аргумент задаёт пустой результат при отсутствии ключа.4. Вернуть несколько полей
Промпт
Excel Microsoft 365, английские функции. В A2 — ID сотрудника. На листе Staff ID находятся в A2:A500, а отдел, должность и руководитель — в C2:E500. Нужно одной формулой заполнить B2:D2. Ключи уникальны, при отсутствии вывести «Нет ID». Дай формулу и укажи занимаемый диапазон.Возможный ответ нейросети
=XLOOKUP(A2,Staff!$A$2:$A$500,Staff!$C$2:$E$500,"Нет ID",0)
Формула вводится в B2 и разливается на B2:D2. Убедитесь, что эти три ячейки свободны, иначе Excel вернёт ошибку #SPILL!.Усиленные промпты: дубли и интервалы
5. Последняя запись по дате
Промпт
Excel Microsoft 365, английские функции. В A2 — клиент. На листе Payments клиенты находятся в A2:A5000, даты — в B2:B5000, суммы — в C2:C5000. Клиент может повторяться. Нужно вернуть сумму платежа с самой поздней датой, а не последнюю строку. При отсутствии — «Нет платежей». Дай формулу и три теста.Возможный ответ нейросети
=LET(k,A2,d,FILTER(Payments!$B$2:$B$5000,Payments!$A$2:$A$5000=k),s,FILTER(Payments!$C$2:$C$5000,Payments!$A$2:$A$5000=k),IFERROR(XLOOKUP(MAX(d),d,s,"",0,-1),"Нет платежей"))
Проверьте клиента с одной записью, с двумя датами и отсутствующего клиента. Если на максимальную дату приходится несколько платежей, формула возвращает последний из них — это отдельное бизнес-правило.6. Поиск по интервалам
Промпт
Excel Microsoft 365, английские функции. Объём заказа — в B2. На листе Discounts пороги 0, 100, 500 и 1000 находятся в A2:A5 по возрастанию, скидки 0%, 3%, 5% и 8% — в B2:B5. Нужно выбрать наибольший порог, не превышающий объём. Дай XLOOKUP и тесты для 50, 100, 499 и 1200.Возможный ответ нейросети
=XLOOKUP(B2,Discounts!$A$2:$A$5,Discounts!$B$2:$B$5,,-1)
Результаты: 50 → 0%; 100 → 3%; 499 → 3%; 1200 → 8%. Пороги должны оставаться числовыми и отсортированными по возрастанию.Промпты для диагностики поиска
7. Формула возвращает #N/A
Промпт
Excel Microsoft 365, английские функции. Формула =XLOOKUP(A2,Catalog!A2:A5000,Catalog!D2:D5000) возвращает #N/A. В A2 значение « AB-125 » с пробелами по краям, а в Catalog!A2:A5000 ключ хранится как «AB-125». Диапазоны одинаковой длины. Объясни причину, дай безопасную исправленную формулу и не скрывай другие ошибки.Возможный ответ нейросети
Ключ не совпадает из-за пробелов в A2. Для очистки исходного ключа:
=XLOOKUP(TRIM(A2),Catalog!$A$2:$A$5000,Catalog!$D$2:$D$5000,"Не найдено",0)
Если пробелы встречаются и в справочнике, безопаснее сначала создать отдельную колонку нормализованных ключей и проверить, не появились ли дубли.По справке Microsoft, #N/A означает, что формула не нашла искомое значение; среди типичных причин — лишние пробелы и несовпадение типов данных.
8. Поиск по двум условиям
Промпт
Excel Microsoft 365, английские функции. На листе Report в A2 — клиент, B2 — месяц. На листе Rates клиенты A2:A3000, месяцы B2:B3000, тарифы C2:C3000. Пара клиент+месяц уникальна. Верни тариф в C2, при отсутствии — «No rate». Дай одну формулу без вспомогательной колонки.Возможный ответ нейросети
=XLOOKUP(1,(Rates!$A$2:$A$3000=A2)*(Rates!$B$2:$B$3000=B2),Rates!$C$2:$C$3000,"No rate",0)
Два сравнения образуют массив единиц только там, где одновременно совпали клиент и месяц. Проверьте совпадение обоих условий и строку, где совпадает только клиент.9. Неверное, но правдоподобное значение
Промпт
Excel 2019. Формула =ВПР(A2;'Скидки'!A2:B10;2;ИСТИНА) для суммы 7500 возвращает 7%, хотя ожидается 5%. Пороги в A2:A10 идут так: 0, 10000, 5000, 20000. Объясни причину и дай порядок исправления. Логика — наибольший порог, не превышающий сумму.Возможный ответ нейросети
Приблизительный ВПР требует возрастающей сортировки первого столбца. Порядок 0, 10000, 5000, 20000 нарушает это условие, поэтому ответ может быть неверным без ошибки.
Отсортируйте пороги как 0, 5000, 10000, 20000 и оставьте формулу с ИСТИНА. После этого проверьте 4999, 5000, 9999 и 10000.10. Нормализовать ключи без потери смысла
Промпт
Excel 2021, русская локаль. В A2:A500 артикулы вида «00125», а в справочнике D2:D500 те же ключи иногда записаны как «125». Ведущие нули важны: «00125» и «125» могут обозначать разные товары. Предложи диагностику и безопасный вариант поиска, не преобразуя ключи автоматически в числа.Возможный ответ нейросети
Автоматически убирать нули нельзя: это способно объединить разные товары. Сначала сформируйте список несовпавших ключей и согласуйте единый формат справочника. Для точного текстового поиска используйте исходный текстовый ключ, например `=ИНДЕКС($B$2:$B$500;ПОИСКПОЗ(A2;$D$2:$D$500;0))`.
Если «125» должно соответствовать «00125», это правило должен подтвердить владелец справочника.11. Вернуть последнее совпадение по порядку строк
Промпт
Excel Microsoft 365, английские функции. Клиент в A2, журнал на листе Log: клиенты A2:A10000, комментарии D2:D10000. Новые записи всегда добавляются ниже старых. Нужно вернуть последний по порядку строк комментарий, при отсутствии — «No history». Дай XLOOKUP и отдельно отметь отличие от поиска по максимальной дате.Возможный ответ нейросети
=XLOOKUP(A2,Log!$A$2:$A$10000,Log!$D$2:$D$10000,"No history",0,-1)
Последний аргумент -1 запускает поиск снизу вверх. Это возвращает последнюю строку, но не обязательно запись с самой поздней датой, если журнал сортировали вручную.Системный промпт консультанта по поиску
Ты — консультант по формулам поиска в Excel в чат-режиме. Не утверждай, что открыл книгу, применил формулу или проверил строки. По данным пользователя выбери ВПР, XLOOKUP, ИНДЕКС+ПОИСКПОЗ или другой совместимый подход. Учитывай версию, локаль, направление поиска, тип совпадения, отсутствующие и повторяющиеся ключи. Не придумывай диапазоны. Дай одну основную формулу, краткую причину выбора, место вставки, 3–5 тестов и ограничения проверки. Если просят изменить файл, объясни, что можешь дать только формулу и ручные шаги. Отвечай по-русски.Как чат ведёт себя с системным промптом
Пользователь: Excel 2019, русская локаль. В A2 артикул, на «Товары» ключи D2:D5000, цены B2:B5000. Верни цену в E2, точное совпадение, ключи уникальны. Чат: Выбираю ИНДЕКС+ПОИСКПОЗ: поиск идёт справа налево, а XLOOKUP недоступен. Формула:
=ИНДЕКС('Товары'!$B$2:$B$5000;ПОИСКПОЗ(A2;'Товары'!$D$2:$D$5000;0)).
Пользователь: При отсутствии ключа нужен текст «Проверьте артикул». Чат:
=ЕСЛИОШИБКА(ИНДЕКС('Товары'!$B$2:$B$5000;ПОИСКПОЗ(A2;'Товары'!$D$2:$D$5000;0));"Проверьте артикул"). Проверьте три случая: существующий артикул возвращает ожидаемую цену; отсутствующий — текст «Проверьте артикул»; пустая A2 не даёт ложного совпадения. Сначала протестируйте формулу безЕСЛИОШИБКА, чтобы не скрыть ошибку диапазона или типа данных.
Пользователь: Проставь эту формулу до E5000. Чат: Я не могу изменить файл из чат-режима. Вставьте формулу в E2, скопируйте её до E5000 и убедитесь, что диапазоны справочника остались закреплены. После копирования сравните E2, одну строку из середины и E5000 с исходным справочником.
Ответы в другой модели могут отличаться, но при таком системном промпте граница должна оставаться той же: чат консультирует, а не выполняет действие.
Что проверить после ответа
Проверьте существующий, отсутствующий и пустой ключ, повтор, первую и последнюю строку справочника, а также копирование формулы вниз.
Не используйте ЕСЛИОШИБКА как первый шаг диагностики: эта функция скрывает не только отсутствие ключа, но и другие ошибки формулы. Сначала подтвердите успешный поиск, исключите разный тип данных, пробелы, неверный диапазон и неправильный режим совпадения. После этого добавьте понятное сообщение для отсутствующего ключа и сохраните удачный промпт как шаблон.
Что читать дальше
Базовый шаблон постановки задачи есть в материале «Промпты для формул Excel». Для поиска по нескольким критериям пригодятся промпты для СУММЕСЛИМН и SUMIFS, а общий выбор между чатом, работой с файлом и автоматизацией разобран в гайде по ИИ для Excel и Google Таблиц.
Больше материалов по теме — в разделе «Промпты».
