We write our own VLOOKUP in order 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, he knows how to remove duplicates from the list - all the doors to the happy office world before him are wide open.

The Habrahabr community, of course, such knowledge can only cause a smile of emotion. Working with data (if at all to condescend to this) is permissible only on old-school ANSI T-SQL - 92.

But sometimes the harsh reality makes comparing arrays of data. In this case, as a rule, there is no time to transfer data to the DBMS, or it is simply inappropriate. Therefore, I suggest that the respected community share their “chips” with each other for convenient and fast data processing in EXCEL. Do not lose the good ...

Ambula itself

The VLOOKUP function (in the Russian version - CDF) is really a very convenient and powerful helper when you need to find the data matches in different tables. It works simply and reliably, like a Kalashnikov assault rifle.
')
But bad luck, sometimes you need to clarify some additional parameters during the comparison. For example, there may be a lot of correspondences, and all of them are in general correct, but some are still “more correct”. That is, it is necessary to carry out additional validation during the comparison.

In general, such additional tasks can be of any kind - gaining control over such a valuable function as VLOOKUP can be extremely useful.

I bring to your attention a code that performs this double check. This is essentially a DOUBLE LOOKUP. If a match is made for the “base” column, the function looks for the parameter of the “specifying” column and, if successful, returns the result, by analogy with the standard VLOOKUP.

If the basic match is found, but there is no clarification, then for my purposes I have set the alert to issue a result, but of course you can now redo the function as needed for your project.

His Majesty 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