Пишем свой VLOOKUP для того, чтобы не зависеть от стандартного (Excel functions)

Амбула Фабула
Заранее простите за занудство :)

Что-то мне подсказывает, что vba — куда старше, чем T-SQL. Так что это еще вопрос, что из них олдскульнее…

На самом деле к ВПР(), а так же практически ко всему что в Экселе связано с поиском или выборками есть масса претензий :)
Я в свое время написал аналог ВПР() за исключением:
— не нужно чтобы значения в столбце поиска были отсортированы.
— ищет только точное совпадение
— возвращает не значение, а номер строки (соответствующая строчка закомментирована)
— возвращает номер строки ПОСЛЕДНЕГО найденного совпадения.

Function VPRL(SearchValue As Variant, Table As Range, SearchColumnNum As Integer, ResultColumnNum As Integer)
Dim i As Integer
For i = 1 To Table.Rows.Count
If Table.Cells(i, SearchColumnNum).Value = SearchValue Then
' VPRL = Table.Cells(i, ResultColumnNum)
VPRL = i
End If
Next i
End Function

>знает как убрать дубликаты из списка

Я не знаю, научите.
Стыдно сказать, но без сортировки и ручной правки не умею…

index + match и никаких проблем с сортировками или порядком столбцов
index + match + match для совсем продвинутых -> получаеться резиновый поиск по x,y

Как вариант, для работы с массивами данных, на мой субъективный взгляд, очень удобно пользовать Google Refine, очень мощно, удобно и свободно.

На тему «фишек» — недавно пришлось сравнивать много-много строк в разных таблицах по группам столбцов — пригодились хэшироваие, создавал в нужных таблицах столбец, куда записывал md5.

Исходный код модуля pastebin.com/AA7d7ewL
Там просто функция MD5(st as String) as String.

Также хочу здесь оставить код аналогичный стандартному ВПР

Function VLOOKUP2(SearchValue As Variant, Table As Range, ResultColumnNum As Integer)
Dim i As Long
SearchColumnNum = 1
Rem MsgBox (SearchValue)
For i = 1 To Table.Rows.Count
Rem MsgBox (Table.Cells(i, SearchColumnNum))
If UCase(RTrim(Table.Cells(i, SearchColumnNum))) = RTrim(UCase(SearchValue)) Then
VLOOKUP2 = Table.Cells(i, ResultColumnNum)
Exit For
End If
Next i
End Function

Также бывает полезен следующий несложный макрос, он обеспечивает защиту от зависаний при пересчете больших сегментов

Sub SelectionCalculate()
Set ss = Selection
For Each cl In Selection.Cells
cl.Select
cl.Calculate
Next cl
ss.Select
End Sub