Встроенная функция VLOOKUP – одна из наиболее мощных функций в Excel. Но она имеет один существенный недостаток – находит только первое вхождение нужного значения в таблице и только в крайней правой колонке. Но если нужно 2-е, 3-е и не в последнем?
Предположим, у нас есть такая таблица оформленных кредитов:
Нам необходимо узнать, например, какова была сумма третьего выданного кредита Майку или когда Джон оформил свое второе соглашение. Встроенная функция VLOOKUP умеет искать только первое вхождение имени в таблице и нам не поможет.
Напишем свою функцию, которая будет искать не только первое, но и любое последующее (N-е) вхождение. Назовем ее, например, VLOOKUP2.
Откройте меню Сервис - Макрос - Редактор Visual Basic , вставьте новый модуль (меню Insert - Module ) и скопируйте туда текст этой функции:
Function VLOOKUP2(Table As Range, _
SearchColumnNum As Integer, _
SearchValue As Variant, _
N As Integer, _
ResultColumnNum As Integer)
'moonexcel.com.ua
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)