We write our VLOOKUP so as not to depend on the standard (Excel functions)
Preamble
As you know, knowledge of the VLOOKUP function in MS EXCEL is enough to become an average analyst in Moscow. If a person is also familiar with PIVOT or, for example, knows how to remove duplicates from the list, all doors to the happy office world are wide open before him.
In the Habrahabr community, of course, such knowledge can only cause a smile of tenderness. Working with data (if at all condescending to it) is permissible only on the old-school ANSI T-SQL - 92.
But sometimes harsh reality compels data arrays to be compared. In this case, as a rule, there is no time to transfer data to the DBMS, or it is simply impractical. Therefore, I invite the respected community to share with each other their "chips" for convenient and fast data processing in EXCEL. Do not disappear good ...
Ambula, actually
The VLOOKUP function (in the Russian version - VLOOKUP) is really a very convenient and powerful assistant when you need to find data matches in different tables. It works simply and reliably, like a Kalashnikov assault rifle.
But this is bad luck, sometimes you need to clarify some additional parameters during the comparison. For example, there can be a lot of correspondences, and all of them are generally true, but some are still more “true”. That is, you need to conduct additional validation during the comparison.
In the general case, such additional tasks can be anything - gaining control over such a valuable function as VLOOKUP can be extremely useful.
I bring to your attention a code that performs such a double check. This is essentially DOUBLE LOOKUP. If the correspondence is satisfied for the “base” column, then the function searches for the parameter of the “qualifying” column and, if successful, returns the result, by analogy with the standard VLOOKUP.
If the basic correspondence is found, but there is no specifying one, then for my purposes I set the output as a result of the notification, but of course you can now remake the function as needed for your project.
His Majesty the code
Формат вызова функции:
VLOOKUP2my
([Table] таблица, внутри которой ищем соответствия;
SearchColumnNum колонка в [Table], в которой ищем "базовое" соответствие;
SearchValue значение для поиска "базового" соответствия;
ResultColumnNum колонка в [Table], из которой заберем результат в случае успеха поисков;
N2 значение для поиска "уточняющего" соответствия;
N2col колонка в [Table], в которой ищем "уточняющее" соответствие)
Function VLOOKUP2my(Table As Range, SearchColumnNum As Integer, SearchValue As Variant, ResultColumnNum As Integer, N2 As Variant, N2col As Integer)
Dim i As Long
For i = 1 To Table.Rows.Count
If UCase(Table.Cells(i, SearchColumnNum)) = UCase(SearchValue) Then
If (UCase(Table.Cells(i, N2col)) = UCase(N2)) Then
VLOOKUP2my = Table.Cells(i, ResultColumnNum)
Exit For
End If
VLOOKUP2my = "Second option not exists"
End If
Next i
End Function