ВВЕРХ

VLOOKUP для сочетания по двум условиям (VLOOKUP3)

YouLibreCalc for Excel logo

Описание

Рассмотрим усовершенствованный вариант функции VLOOKUP – VLOOKUP3, которая дает нам возможность подставлять значение при совпадении двух условий. Данная функция может оказаться полезной в случаях, когда у нас нет уникальных значений в поле, по которому нам нужно осуществить сочетание.


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

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

Как видим, у нас есть заемщики с одинаковыми фамилиями и кредитные соглашения с одинаковыми номерами. Совместить обычной функцией VLOOKUP будет проблематично, поскольку она совмещает только по одному условию и только первое найденное значение.

Переделаем привычный нам VLOOKUP для подстановки значений по двум условиям.

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

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

Function VLOOKUP3(Table1       As Range, _
                  SearchValue1 As Variant, _
                  Table2       As Range, _
                  SearchValue2 As Variant,_
				  ResultColumn As Range)
  'moonexcel.com.ua
  Dim i As Integer
               
  For i = 1 To Table1.Rows.Count
    If Table1.Cells(i, 1) = SearchValue1 Then
      If Table2.Cells(i, 1) = SearchValue2 Then
        VLOOKUP3 = ResultColumn.Cells(i, 1)
        Exit For
      End If
    End If
  Next i
                
End Function 

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

Использование функции

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

=VLOOKUP3( диапазон1 ; искомое_значение1 ; диапазон2 ; искомое_значение2 ; диапазон из которого подставляем значение )

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

=VLOOKUP3($A$2:$A$11; A15; $B$2:$B$11; B15; $C$2:$C$11)

Также, не забываем фиксировать диапазоны знаком доллара ($) , чтобы нам не съезжали диапазоны при копировании формулы (для быстрого фиксирования можно также воспользоваться клавишей F4).

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