Вбудована функція 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)