Как использовать отрицательный ВПР, чтобы вернуть значение, расположенное слева от ключевого столбца в Excel?
Обычно функция ВПР позволяет возвращать значения только из столбцов, расположенных справа от искомого. Если же нужные вам данные находятся в самом левом столбце, может возникнуть соблазн указать отрицательный номер столбца в формуле: =ВПР(F2;D2:D13;-3;0)Однако это приведёт к ошибке, как показано на следующем снимке экрана. Так как же всё-таки получить соответствующее значение слева с помощью ВПР в Excel?
Примените отрицательный ВПР для возврата значения из Самый левый столбец с помощью пользовательской функции
Следующая пользовательская функция поможет вам использовать отрицательный ВПР на листе Excel. Выполните следующие действия:
1. Удерживая клавиши ALT + F11, откройте окно Microsoft Visual Basic для приложений.
2. Нажмите Вставка > Модуль и вставьте следующий код в окно Модуля.
Код VBA: применение отрицательного ВПР для возврата значения из Самый левый столбец:
Function VLOOKUPNEG(lookup_value, table_array As Range, col_index_num As Integer, CloseMatch As Boolean)
Dim RowNr As Long
RowNr = Application.WorksheetFunction.Match(lookup_value, table_array.Resize(, 1), CloseMatch)
VLOOKUPNEG = table_array(RowNr, 1).Offset(0, col_index_num)
End Function
3. Затем сохраните и закройте окно кода, вернитесь на лист и введите следующую формулу: =VLOOKUPNEG(F2,D2:D13,-3,0) в ячейку, где вы хотите получить результат. После этого нажмите клавишу Enter — и соответствующее значение из самого левого столбца будет успешно извлечено (см. снимок экрана):
Примечание: В приведённой выше формуле F2 — это ячейка, содержащая критерий для поиска ВПР, D2:D13 — диапазон столбца, в котором выполняется поиск, а -3 — номер столбца, из которого необходимо вернуть соответствующее значение.
Разблокируйте поиск влево в Excel с помощью расширенной функции ВПР от Kutools
Лучшие инструменты повышения продуктивности в 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-дневная полнофункциональная пробная версия— без регистрации и кредитной карты
- Лучшее соотношение цены и качества— экономия по сравнению с покупкой отдельных надстроек