Как вернуть значение, если заданное значение существует в определенном диапазоне в Excel?
При работе с данными в Excel часто необходимо определить, существует ли конкретное значение в пределах заданного диапазона, и если да, то извлечь значение из соседней ячейки, соответствующей этой записи. Например, как показано на скриншоте слева, если вы ищете число 5 в списке или диапазоне, вы можете захотеть автоматически вернуть соответствующее соседнее значение – это полезно для таких задач, как поиск идентификаторов продуктов, получение информации о пользователях или сопоставление кодов со значениями без ручного поиска.
Вернуть значение, если заданное значение существует в определенном диапазоне
Вернуть значение, если заданное значение существует в определенном диапазоне с использованием функции VLOOKUP
Чтобы быстро получить значение, связанное с определенной записью из таблицы данных или диапазона в Excel, функция VLOOKUP предлагает простое решение.
Этот метод особенно эффективен, если ваш столбец поиска (где вы ищете значение) является крайним левым столбцом вашего диапазона данных, и вы хотите вернуть данные из столбца справа от него. Он обычно используется для поиска кодов, имен, идентификаторов или номеров ссылок и получения связанных деталей легко.
1. Выберите пустую ячейку, где должен появиться результат. Введите следующую формулу в строку формул:
=VLOOKUP(E2,A2:C8,3,TRUE)Нажмите Enter для выполнения формулы. См. скриншот:
В этом примере, если число 5 (в ячейке E2) найдено в указанном числовом диапазоне в столбце A (например, между 4 и 6), Excel выполнит поиск этого значения и немедленно заполнит соответствующее значение из третьего столбца (столбца C) диапазона A2:C8 в выбранную ячейку. В иллюстрации возвращается «Addin 012», потому что число 5 находится в диапазоне 4-6.
Примечание: В формуле E2 относится к значению для поиска, A2:C8 — это диапазон данных, включающий значения для поиска и возвращаемые значения, а 3 указывает, что возвращаемое значение должно быть взято из третьего столбца указанного диапазона. Настройте эти ссылки в соответствии с вашим листом.
Советы и подводные камни:
- Убедитесь, что диапазон поиска (A2:C8) включает как столбец поиска, так и столбец возврата.
- При использовании VLOOKUP с аргументом TRUE столбец поиска должен быть отсортирован по возрастанию, иначе могут возникнуть непредвиденные результаты.
- Для точных совпадений используйте FALSE в качестве четвертого аргумента, но для поиска по диапазонам (как в этом примере) оставьте его как TRUE.
- Если ваши данные меняются часто, дважды проверьте свои ссылки, чтобы избежать ошибок несоответствия.
Возращение значения, если заданное значение существует в определенном диапазоне с использованием функций INDEX и MATCH
Комбинация INDEX и MATCH — это гибкий метод возвращения значения, когда заданное значение существует в определенном диапазоне. В отличие от VLOOKUP, INDEX и MATCH могут искать значение в любом столбце и возвращать результат из любого другого столбца, независимо от порядка. Это особенно полезно, если ваш столбец поиска не является крайним левым столбцом или если вам нужна большая адаптивность в структуре данных.
1. Выберите пустую ячейку, где должен появиться результат (например, F2). Введите следующую формулу в строку формул:
=INDEX(C2:C8, MATCH(E2, A2:A8,1))Нажмите Enter для подтверждения формулы.
- MATCH(E2, A2:A8, 1) ищет позицию наибольшего значения ≤ E2 в столбце A. (Это требует, чтобы столбец A был отсортирован по возрастанию.)
- INDEX(C2:C8, ...) возвращает значение из столбца C в строке, найденной функцией MATCH.
Эта формула ищет значение в E2 в диапазоне A2:A8. Если значение найдено (например, 5 находится между 4 и 6 в одной из строк), функция MATCH возвращает его относительную позицию, а INDEX извлекает значение из соответствующей строки диапазона C2:C8. '1' в MATCH указывает на приблизительное совпадение, поэтому убедитесь, что ваш диапазон поиска отсортирован соответствующим образом.
- Если вы хотите точное совпадение, используйте
0в качестве третьего аргумента в MATCH. - INDEX и MATCH также поддерживают как вертикальную, так и горизонтальную ориентацию данных.
- Если значение не найдено, формула возвращает #N/A; рассмотрите возможность обертывания
IFERRORдля более дружественных выходных данных.
Возращение значения, если заданное значение существует в определенном диапазоне с использованием функции XLOOKUP
Функция XLOOKUP — это современная альтернатива для поиска значений в Excel365 и Excel2019. XLOOKUP преодолевает многие ограничения VLOOKUP, такие как ограничение на положение столбца поиска и автоматическое точное/приблизительное сопоставление.
1. В желаемой ячейке вывода (например, F2) введите следующую формулу:
=XLOOKUP(1, (E2>=A2:A8)*(E2<=B2:B8), C2:C8)После ввода формулы нажмите Enter, чтобы увидеть результат в выбранной ячейке.
- (E2>=A2:A8) проверяет, больше ли E2 или равно каждому значению в столбце A.
- (E2<=B2:B8) проверяет, меньше ли E2 или равно каждому значению в столбце B.
- Умножение этих двух условий создает массив из 1 и 0, где 1 означает, что E2 находится между A и B в этой строке.
- XLOOKUP(1, ..., C2:C8) ищет первое 1 и возвращает соответствующее значение из столбца C.
- XLOOKUP динамически корректируется, если столбцы вставлены или перемещены, в отличие от VLOOKUP с фиксированными номерами столбцов.
- Работает как с вертикальными, так и с горизонтальными данными.
- Требуется Excel 365 или 2021; для старых версий используйте другие методы, описанные выше.
Раскройте магию Excel с Kutools AI
- Умное выполнение: Выполняйте операции с ячейками, анализируйте данные и создавайте диаграммы — всё это посредством простых команд.
- Пользовательские формулы: Создавайте индивидуальные формулы для оптимизации ваших рабочих процессов.
- Кодирование VBA: Пишите и внедряйте код VBA без особых усилий.
- Интерпретация формул: Легко разбирайтесь в сложных формулах.
- Перевод текста: Преодолейте языковые барьеры в ваших таблицах.
Связанные статьи:
- Как использовать vlookup для возврата true или false / yes или no в Excel?
- Как использовать vlookup для возврата значения в соседней или следующей ячейке в Excel?
- Как вернуть значение в другой ячейке, если ячейка содержит определенный текст в Excel?
Лучшие инструменты для повышения продуктивности в Office
Повысьте свои навыки работы в Excel с помощью Kutools для Excel и ощутите эффективность на новом уровне. Kutools для Excel предлагает более300 расширенных функций для повышения производительности и экономии времени. Нажмите здесь, чтобы выбрать функцию, которая вам нужнее всего...
Office Tab добавляет вкладки в Office и делает вашу работу намного проще
- Включите режим вкладок для редактирования и чтения в Word, Excel, PowerPoint, Publisher, Access, Visio и Project.
- Открывайте и создавайте несколько документов во вкладках одного окна вместо новых отдельных окон.
- Увеличьте свою продуктивность на50% и уменьшите количество щелчков мышью на сотни ежедневно!
Все надстройки Kutools. Один установщик
Пакет Kutools for Office включает надстройки для Excel, Word, Outlook и PowerPoint, а также Office Tab Pro — идеально для команд, работающих в разных приложениях Office.
- Комплексный набор — надстройки для Excel, Word, Outlook и PowerPoint плюс Office Tab Pro
- Один установщик, одна лицензия — настройка занимает считанные минуты (MSI-совместимо)
- Совместная работа — максимальная эффективность между приложениями Office
- 30-дневная полнофункциональная пробная версия — без регистрации и кредитной карты
- Лучшее соотношение цены и качества — экономия по сравнению с покупкой отдельных надстроек