Как с помощью функции ВПР найти и вернуть последнее совпадающее значение в Excel?
Функция ВПР (VLOOKUP) в Excel — один из самых популярных инструментов для поиска данных в таблице по заданному критерию. Однако по умолчанию она возвращает только первое найденное совпадение, что может стать серьёзным ограничением, если в вашем диапазоне поиска есть повторяющиеся значения, а вам нужно получить именно последнее вхождение. Такая задача часто возникает при отслеживании актуального статуса, поиске последней продажи по клиенту или определении самой свежей записи в хронологических списках. Чтобы обойти это ограничение, Excel предлагает несколько эффективных решений: с помощью функций ПРОСМОТР (LOOKUP), XПОИСК (XLOOKUP), а также комбинации ИНДЕКС и ПОИСКПОЗ (MATCH). Кроме того, удобные сторонние инструменты, такие как Kutools для Excel, значительно упрощают эту задачу. В этой статье мы подробно разберём каждый метод: как он работает, где применяется на практике, какие имеет преимущества и недостатки, а также дадим полезные советы, чтобы вы без труда находили и возвращали последнее совпадающее значение — даже там, где стандартный ВПР бессилен.
Функция ВПР и возврат последнего совпадающего значения в Excel
Поиск последнего совпадающего значения с помощью функции ПРОСМОТР (LOOKUP)
Хотя ВПР не может напрямую найти последнее совпадающее значение, функция ПРОСМОТР (LOOKUP) предлагает элегантное решение. Этот подход особенно удобен, если ваши данные не отсортированы и вы хотите использовать формулу, совместимую практически со всеми версиями Excel. Формула использует особенности обработки массивов и ошибок функцией ПРОСМОТР, чтобы быстро определить последнее вхождение.
Чтобы извлечь последнее совпадающее значение с помощью функции ПРОСМОТР, выполните следующие шаги:
1.Выделите ячейку, в которой нужно отобразить последнее совпадающее значение, и введите следующую формулу:
=LOOKUP(2,1/($A$2:$A$12=E2),$C$2:$C$12)2. Нажмите Enter. Если нужно применить формулу к дополнительным строкам, перетащите маркер заполнения вниз до нужного диапазона — и вы сможете без усилий находить последнее совпадение для нескольких значений поиска.
$A$2:$A$12— это диапазон, являющийся столбцом поиска (критерия).E2— ячейка, содержащая искомое значение.$C$2:$C$12— это диапазон столбца с возвращаемыми значениями (результатами).
1/($A$2:$A$12=E2)формирует массив со значением 1 там, где условие истинно, и ошибками#DIV/0!в остальных случаях.LOOKUP(2, ...)использует особенность функции LOOKUP: она игнорирует ошибки и ищет число 2 (которое заведомо отсутствует). В результате LOOKUP находит последнюю 1 в массиве и возвращает соответствующее значение из результирующего массива — таким образом получается последнее совпадающее значение.
Советы и примечания:
- Убедитесь, что диапазоны поиска и возврата имеют одинаковый размер, и применяйте абсолютные ссылки при копировании формулы вниз.
- Если в диапазоне поиска есть пустые ячейки или ошибки, результаты могут быть искажены. Очистите данные или, при необходимости, оберните формулу в
IFERROR(1). - Если вы получаете
#N/A, убедитесь, что искомое значение действительно присутствует в диапазоне «Исходный диапазон».
Преимущества: Работает во всех версиях Excel и не требует специального ввода массивов.
Ограничения:Менее надёжна при наличии пустых ячеек или ошибок в массиве поиска; не выводит пользовательские сообщения об ошибках без использования дополнительных функций (например,)ЕСЛИОШИБКА).
Поиск последнего совпадающего значения с помощью Kutools для Excel
Kutools для Excel предлагает интуитивный и эффективный способ получения последнего совпадающего значения — идеальное решение как для пользователей, предпочитающих графический интерфейс без формул, так и для тех, кому нужно быстро обрабатывать большие объёмы данных. Встроенный инструмент Поиск снизу вверх из пакета «Супер ПОИСК» от Kutools устраняет сложности работы с формулами, автоматически обрабатывает ошибки и позволяет выводить результаты сразу в несколько ячеек, экономя ваше время и минимизируя ошибки при вводе. Этот метод особенно подходит тем, кто не знаком с продвинутыми функциями Excel, или пользователям, стремящимся свести к минимуму ручное редактирование формул.
После установки Kutools для Excelвыполните следующие действия:
1. Щёлкните Kutools > Супер ПОИСК > Поиск снизу вверх. См. снимок экрана:
2.В диалоговом окне Поиск снизу вверх:
- Выберите ячейки с искомыми значениями и целевые ячейки для вывода в разделах «Область размещения списка» и «Диапазон значений поиска».
- Укажите соответствующий диапазон в разделе «Диапазон данных». Убедитесь, что все диапазоны совпадают по размеру и порядку, чтобы избежать несоответствий.
- Нажмите кнопку ОК, чтобы применить изменения.
После выполнения Kutools мгновенно вернёт последние совпадающие элементы, как показано ниже:
Совет: Если вы хотите отображать собственное сообщение вместо ошибки #N/A при отсутствии совпадений, нажмите Параметры, установите флажок Заменить результат вывода, который не найден, и вернуть „#N/A" со специфицированным значением и введите предпочитаемый текст.
Плюсы: Не требуется редактировать формулы. Поддерживает массовые операции и замену ошибок. Идеален для начинающих пользователей Excel и отлично справляется с обработкой больших объёмов данных.
Минусы: Требуется установленное дополнение Kutools; функция доступна только в полной или пробной версии Kutools.
Практический совет: Всегда проверяйте результат и входной диапазон перед подтверждением, особенно если ваши данные динамически изменяются — это гарантирует точность результатов.
Поиск последнего совпадающего значения с помощью функций ИНДЕКС и ПОИСКПОЗ (INDEX и MATCH)
Комбинация функций ИНДЕКС и ПОИСКПОЗ (INDEX и MATCH) предлагает ещё один гибкий и универсальный способ поиска последнего совпадающего значения в Excel. Этот метод отличается высокой адаптивностью, не требует предварительной сортировки данных и совместим со всеми версиями Excel, включая устаревшие. Однако в зависимости от вашей версии Excel для получения корректных результатов может потребоваться ввести формулу как формулу массива.
Чтобы использовать этот метод, выполните следующие действия:
1.В целевой ячейке введите приведённую ниже формулу:
=INDEX($C$2:$C$12,MATCH(2,1/($A$2:$A$12=E2)))2.Подтвердите формулу:
- В версии Excel 2019 или более ранней завершите ввод комбинацией клавиш Ctrl + Shift + Enter (Excel автоматически добавит фигурные скобки {}).
- В версии Microsoft 365 / Excel 2021 и более поздних достаточно нажать клавишу Enter.
3.Если у вас несколько диапазонов значений для поиска, перетащите маркер заполнения вниз, чтобы применить формулу ко всем соседним строкам и выполнить пакетную обработку.
$A$2:$A$12— это диапазон is the lookup column (criteria).E2— ячейка, содержащая искомое значение.$C$2:$C$12— это диапазон Столбец для возврата (результаты).
1/($A$2:$A$12=E2)формирует массив со значением 1 там, где условие поиска истинно, и ошибками#DIV/0!в остальных случаях. Это преобразует логические значения ИСТИНА/ЛОЖЬ в числовые сигналы.MATCH(2, 1/($A$2:$A$12=E2))указывает Excel найти число 2 (которое отсутствует). Функция ПОИСКПОЗ возвращает позицию последней 1 в массиве — то есть позицию последнего истинного совпадения.INDEX($C$2:$C$12, ...)использует эту позицию, чтобы получить соответствующее значение из диапазона возврата.
Рекомендации и советы:
- Убедитесь, что диапазоны поиска и возврата содержат одинаковое количество строк, и используйте абсолютные ссылки при копировании формулы вниз.
- Если вы видите ошибки
#N/Aили#DIV/0!, проверьте наличие несовпадающих ключей, пустых ячеек или ошибок в диапазоне поиска. Для более чистого результата оберните формулу вIFERROR, например:=IFERROR(your_formula, "").
Преимущества: Универсальность и обратная совместимость со всеми версиями Excel.
Недостатки: Сложнее запомнить; в старых версиях Excel требует ввода как формулы массива.
Поиск последнего совпадающего значения с помощью функции XПОИСК (XLOOKUP)
Функция XПОИСК (XLOOKUP), доступная в Excel 365, Excel 2021 и более поздних версиях, предлагает самое простое и современное решение для поиска последнего совпадения. Благодаря гибким параметрам, управляющим направлением поиска и обработкой ошибок, XПОИСК легко находит значения снизу вверх и извлекает последнее совпадение — без сложных устаревших формул массива.
Чтобы использовать XПОИСК для поиска последнего совпадающего значения:
1. В целевой ячейке введите приведённую ниже формулу, а затем перетащите маркер заполнения, если она требуется для дополнительного диапазона значений поиска.
=XLOOKUP(E2, $A$2:$A$12, $C$2:$C$12, , , -1)
E2: искомое значение.$A$2:$A$12: диапазон для поиска.$C$2:$C$12: возвращаемый массив., ,: две запятые означают, что необязательные аргументыif_not_foundиmatch_modeопущены (используются значения по умолчанию).-1:search_mode=-1выполняет поиск с конца к началу (снизу вверх), поэтому вы получаете последнее совпадение.
Практические примечания:
- Специальный ввод массива не требуется. При необходимости вы можете задать собственное сообщение с помощью аргумента
if_not_found. - Функция XLOOKUP возвращает последнее совпадение в соответствии с порядком элементов в заданном массиве (снизу вверх при использовании параметра)
-1), независимо от применённой фильтрации. - Для пакетной обработки скопируйте формулу вниз; каждая строка вычисляется независимо.
Преимущества: Простой синтаксис, встроенная возможность поиска с конца и отсутствие необходимости использовать устаревшие формулы массива.
Ограничение: Доступна только в Excel 365, Excel 2021 и более поздних версиях.
Поиск последнего совпадающего значения с помощью макроса VBA
В некоторых случаях, особенно если вам нужно автоматизировать процесс поиска или работать с очень большими наборами данных, использование макроса VBA может оказаться практичным решением. VBA позволяет создавать собственную логику поиска и обрабатывать исключения или особые условия, которые сложно реализовать с помощью стандартных формул.
Применимый сценарий:Выбирайте это решение, если вам часто приходится выполнять одну и ту же операцию поиска в разных книгах или вы хотите инкапсулировать свою логику в многократно используемый скрипт.
1. Щёлкните Разработчик > Visual Basic, чтобы открыть окно Microsoft Visual Basic для приложений. Затем выберите Вставка > Модуль и вставьте следующий код в модуль:
Option Explicit
Sub FindLastMatch()
Dim searchRange As Range
Dim returnRange As Range
Dim searchValue As Variant
Dim i As Long
Dim foundValue As Variant
Dim found As Boolean
Const xTitleId As String = "KutoolsforExcel"
' Get ranges and value from user
On Error GoTo CleanFail
Set searchRange = Application.InputBox("Select the lookup column (single column):", xTitleId, Type:=8)
If TypeName(searchRange) = "Boolean" Then Exit Sub ' Cancel pressed
Set returnRange = Application.InputBox("Select the return column (single column):", xTitleId, Type:=8)
If TypeName(returnRange) = "Boolean" Then Exit Sub ' Cancel pressed
searchValue = Application.InputBox("Enter the lookup value:", xTitleId, Type:=2)
If VarType(searchValue) = vbBoolean And searchValue = False Then Exit Sub ' Cancel pressed
' Basic validations
If searchRange.Columns.Count <> 1 Or returnRange.Columns.Count <> 1 Then
MsgBox "Please select a single column for both lookup and return ranges.", vbExclamation
Exit Sub
End If
If searchRange.Rows.Count <> returnRange.Rows.Count Then
MsgBox "Lookup and return ranges must have the same number of rows.", vbExclamation
Exit Sub
End If
If Not searchRange.Parent Is returnRange.Parent Then
MsgBox "Lookup and return ranges must be on the same worksheet.", vbExclamation
Exit Sub
End If
' Scan from bottom to top
found = False
For i = searchRange.Rows.Count To 1 Step -1
If CStr(searchRange.Cells(i, 1).Value) = CStr(searchValue) Then
foundValue = returnRange.Cells(i, 1).Value
found = True
Exit For
End If
Next i
If found Then
MsgBox "The last matching value is: " & foundValue, vbInformation
Else
MsgBox "No match found.", vbInformation
End If
Exit Sub
CleanFail:
MsgBox "Operation cancelled or invalid selection.", vbExclamation
End Sub
2. Нажмите кнопку 
, чтобы запустить код. В появившихся диалоговых окнах выберите столбец для поиска, столбец для возврата и введите искомое значение по запросу. Макрос просматривает данные с последней строки вверх и отображает последнее найденное совпадающее значение.
Примечания:
- Убедитесь, что диапазоны поиска и возврата состоят из одного столбца, содержат одинаковое количество строк и расположены на одном листе.
- Макрос сравнивает значения как текст для упрощения. Если вам необходимо различать числовые форматы (например, 00123 и 123), соответствующим образом измените логику сравнения.
- Если совпадение не найдено или выделение недействительно/отменено, появится уведомление.
Плюсы: Полностью автоматизировано, можно использовать многократно и не требует ввода или копирования формул по ячейкам.
Минусы: Немного более сложная начальная настройка; требует книги с поддержкой макросов (.xlsm) и доверенной среды для их выполнения.
Возврат последнего совпадающего значения в Excel — распространённая задача, будь то отслеживание последних транзакций, анализ обновлений или учёт изменений во времени. С помощью описанных выше методов вы можете выбрать подходящий вариант в зависимости от используемой версии Excel, ваших предпочтений в рабочем процессе и уровня знакомства с инструментами Excel. Подходы с использованием ПРОСМОТР, ИНДЕКС и ПОИСКПОЗ, XПОИСК, Kutools и макросов VBA имеют собственные сильные стороны и оптимальные сценарии применения.
Устранение неполадок: Если формула возвращает ошибку #Н/Д или #ДЕЛ/0!, проверьте правильность выбора диапазона, убедитесь, что искомое значение существует, и подтвердите корректное выравнивание ваших диапазонов. По возможности избегайте пустых ячеек в столбце поиска — это повысит надёжность формул. В случае сомнений протестируйте формулу на небольшом фрагменте данных, чтобы проверить настройку: так вы быстрее выявите возможные ошибки.
Чтобы глубже освоить методы поиска — например, получение нескольких результатов, объединение совпадений или поиск по разным листам, — обязательно загляните на специализированные ресурсы, такие как наша страница учебных материалов по Excel, где собрано множество полезных статей и пошаговых руководств. Эти навыки помогут вам увереннее обрабатывать, анализировать и представлять важные данные в Excel!
Другие связанные статьи:
- Поиск значений с помощью ВПР по нескольким листам
- В Excel функцию ВПР легко использовать для поиска совпадающих значений в одной таблице на листе. Но задумывались ли вы, как выполнить поиск с помощью ВПР сразу по нескольким листам? Допустим, у вас есть три листа с диапазонами данных, и теперь вы хотите получить соответствующие значения на основе заданных критериев из этих трёх листов.
- Использование точного и приблизительного поиска с помощью ВПР в Excel
- В Excel функция ВПР — одна из самых важных: она ищет значение в крайнем левом столбце таблицы и возвращает данные из той же строки указанного диапазона. Но уверенно ли вы используете ВПР в Excel? В этой статье я покажу, как эффективно применять функцию ВПР.
- ВПР: возврат пустой ячейки или определённого значения вместо 0 или #N/A
- Обычно при использовании функции ВПР, если ячейка совпадения пуста, возвращается 0, а если искомое значение не найдено — ошибка #N/A, как показано на скриншоте ниже. Как вместо 0 или #N/A сделать так, чтобы отображалась пустая ячейка или заданное текстовое значение?
- ВПР и возврат всей строки / Вся строка совпадающего значения в Excel
- Обычно функция ВПР позволяет искать и возвращать совпадающее значение из диапазона данных, но пробовали ли вы находить и возвращать всю строку данных на основе заданного критерия, как показано на следующем скриншоте?
- ВПР и объединение нескольких соответствующих значений в 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-дневная полнофункциональная пробная версия— без регистрации и кредитной карты
- Лучшее соотношение цены и качества— экономия по сравнению с покупкой отдельных надстроек