ВВЕРХ

Улучшенная функция VLOOKUP (VLOOKUP2)

Описание

Встроенная функция VLOOKUP – одна из наиболее мощных функций в Excel. Но она имеет один существенный недостаток – находит только первое вхождение нужного значения в таблице и только в крайней правой колонке. Но если нужно 2-е, 3-е и не в последнем?


Пример задачи

Предположим, у нас есть такая таблица оформленных кредитов:

Нам необходимо узнать, например, какова была сумма третьего выданного кредита Майку или когда Джон оформил свое второе соглашение. Встроенная функция VLOOKUP умеет искать только первое вхождение имени в таблице и нам не поможет.

Напишем свою функцию, которая будет искать не только первое, но и любое последующее (N-е) вхождение. Назовем ее, например, VLOOKUP2.

VBA код для функции VLOOKUP2

Откройте меню Сервис - Макрос - Редактор Visual Basic , вставьте новый модуль (меню Insert - Module) и скопируйте туда текст этой функции:

Function VLOOKUP2(Table           As Range, _
                  SearchColumnNum As Integer, _
                  SearchValue     As Variant, _
                  N               As Integer, _
                  ResultColumnNum As Integer)        
  Dim i      As Integer
  Dim iCount As Integer
       
  For i = 1 To Table.Rows.Count
    If Table.Cells(i, SearchColumnNum) = SearchValue Then
      iCount = iCount + 1
    End If
    If iCount = N Then
      VLOOKUP2 = Table.Cells(i, ResultColumnNum)
      Exit For
    End If
  Next i
End Function 

Закройте редактор Visual Basic и вернитесь в Excel.

Теперь в мастере функций в категории Определенные пользователем можно найти нашу функцию VLOOKUP2 и воспользоваться ею. Синтаксис функции следующий:

=VLOOKUP2(таблица ; номер_столбца_где_ищем ; искомое_значение; номер_вхождения ; номер_столбца_из_какого_берем_значения )

То есть, для того чтобы найти сумму выданного Майку третьего кредита, нужно будет ввести:

=VLOOKUP2(A2:A19 ; 1 ; "Mike"; 3 ; 4 )

Статьи по теме: