Как с помощью функции ВПР найти первое, второе или n-е совпадающее значение в Excel?
Допустим, у вас есть два столбца — с товарами и количеством, как на скриншоте ниже. Как быстро определить количество для первого или второго банана?
Здесь на помощь приходит функция ВПР! В этой статье мы покажем, как с её помощью найти первое, второе или n-е совпадающее значение в Excel.
Поиск первого, второго или n-го совпадающего значения в Excel с помощью формулы
Легко находите первое совпадающее значение в Excel с помощью Kutools для Excel
Поиск первого, второго или n-го совпадающего значения в Excel
Выполните следующие действия, чтобы найти первое, второе или n-е совпадающее значение в Excel.
1. Введите в ячейку D1 критерий для поиска с помощью ВПР — например, «Банан».
2. Здесь мы найдём первое совпадающее значение для банана. Выберите пустую ячейку, например E2, скопируйте и вставьте формулу =INDEX($B$2:$B$6,MATCH(TRUE,EXACT($D$1,$A$2:$A$6),0)) в строку формул и нажмите клавиши Ctrl+Shift+Enter одновременно.
Примечание: В этой формуле $B$2:$B$6 — диапазон искомых значений; $A$2:$A$6 — диапазон, содержащий все критерии для функции ВПР; $D$1 — ячейка с заданным критерием поиска.
Теперь в ячейке E2 появится первое совпадающее значение для банана. Эта формула позволяет получить только первое значение, соответствующее заданному критерию.
Чтобы получить любое n-е соответствующее значение, используйте следующую формулу:=INDEX($B$2:$B$6,SMALL(IF($D$1=$A$2:$A$6,ROW($A$2:$A$6)-ROW($A$2)+1),1)) + Ctrl + Shift + Enter — эта формула вернёт первое найденное значение.
Примечания:
1. Чтобы найти второе совпадающее значение, измените приведённую выше формулу на =INDEX($B$2:$B$6,SMALL(IF($D$1=$A$2:$A$6,ROW($A$2:$A$6)-ROW($A$2)+1),2)) и нажмите клавиши Ctrl + Shift + Enter одновременно. См. снимок экрана:
2. Последнее число в приведённой выше формуле указывает на n-е совпадающее значение по критерию ВПР: заменив его на 3, вы получите третье совпадение, а подставив n — n-е совпадающее значение.
Поиск первого совпадающего значения в Excel с помощью Kutools для Excel
В ы можете легко находить первое совпадающее значение в Excel, не запоминая формулы, с помощью формулы Найти данные в диапазонеиз Kutools для Excel.
Перед применением Kutools для Excel пожалуйста, сначала загрузите и установите его.
1. Выберите ячейку для размещения первого найденного значения (например, ячейку E2), затем щелкните Kutools > Помощник формул > Помощник формул. См. снимок экрана:
3. В диалоговом окне Помощник формул выполните следующую настройку:
- 3,1 В поле Выберите формулу найдите и выберите Найти данные в диапазоне;
Советы: вы можете установить флажок Фильтр и ввести нужное слово в текстовое поле, чтобы быстро отфильтровать формулы. - 3,2 В поле Table_arrayвыберите таблицу,содержащую первые совпадающие значения.;
- 3,2 В поле Lookup_valueвыберите ячейку, содержащую критерий, на основе которого будет возвращено первое значение;
- 3,3 В поле Column укажите столбец, из которого нужно вернуть найденное значение. Либо просто введите номер столбца прямо в текстовое поле — как вам удобнее.
- 3,4 Нажмите кнопку OK. См. снимок экрана:
Теперь соответствующее значение автоматически появится в ячейке C10 на основе вашего выбора в раскрывающемся списке.
Если вы хотите воспользоваться бесплатной пробной версией (30 дней) этой утилиты, нажмите, чтобы скачать её, а затем выполните операцию в соответствии с приведёнными выше шагами.
Лучшие инструменты повышения продуктивности в 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-дневная полнофункциональная пробная версия— без регистрации и кредитной карты
- Лучшее соотношение цены и качества— экономия по сравнению с покупкой отдельных надстроек