Пользовательская функция, вроде VLOOKUP, возвращающая все значения, а не одно

Функции типа VLOOKUP или INDEX(...;MATCH()) ищут только первое значение сверху списка. Но часто возникает необходимость подтянуть все значения по заданному параметру поиска.

Для этого можно использовать пользовательскую функцию, написанную как-то на коленке.

Function FindAll(LValue As String, SRng As Range, RRng As Range)
For i = 1 To Workbooks(Sheets(SRng.Parent.Name).Parent.Name).Sheets(SRng.Parent.Name).UsedRange.Rows.count
If SRng(i, 1) = LValue Then FindAll = FindAll & RRng(i, 1) & ";"
Next i
End Function

Так как значения повторяются, то есть вариант с уникальными значениями

Function FindAllUnique(LValue As String, SRng As Range, RRng As Range) As String
Dim count As Long, mark As Integer
ReDim s(0) As Variant
count = 0
's(0) = 0
For i = 1 To Workbooks(Sheets(SRng.Parent.Name).Parent.Name).Sheets(SRng.Parent.Name).UsedRange.Rows.count
If CStr(SRng(i, 1)) = CStr(LValue) Then
mark = 0
For Each y In s
If CStr(RRng(i, 1)) = CStr(y) Then
mark = 1
GoTo 10
End If
Next y
10 If mark = 0 Then
count = count + 1
ReDim Preserve s(count)
s(count) = RRng(i, 1)
End If
End If
Next i
For i = 1 To count
FindAllUnique = FindAllUnique & s(i) & ";"
Next i
End Function

Вставить пользовательскую функцию можно через панель

В данном случае у меня она сохранена в файл PERSONAL2.XLSB, но у вас она может быть там, гле вы ее сохраните

Аргументы функции

LValue - искомое значение

SRng - столбец (диапазон) в котором ищется это значение

RRng - столбец, из которого подтягиваются нужные значения.

например, формула будет выглядеть так:

=PERSONAL2.XLSB!FindAllUnique(A7412;Sheet1!K:K;sheet1!J:J)

где PERSONAL2.XLSB! - файл, в котором хранится функция

FindAllUnique - название функции

A7412 - искомое значение

Sheet1!K:K - где ищем

sheet1!J:J - откуда подтягиваем данные

Работает функция небыстро, но зато позволяет достаточно наглядно свести в строчный список все значения по условию поиска.