Функции типа 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
Вставить пользовательскую функцию можно через панель
Аргументы функции
LValue - искомое значение
SRng - столбец (диапазон) в котором ищется это значение
RRng - столбец, из которого подтягиваются нужные значения.
например, формула будет выглядеть так:
=PERSONAL2.XLSB!FindAllUnique(A7412;Sheet1!K:K;sheet1!J:J)
где PERSONAL2.XLSB! - файл, в котором хранится функция
FindAllUnique - название функции
A7412 - искомое значение
Sheet1!K:K - где ищем
sheet1!J:J - откуда подтягиваем данные
Работает функция небыстро, но зато позволяет достаточно наглядно свести в строчный список все значения по условию поиска.