Как найти значение и вернуть несколько соответствующих значений в Excel?

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

Поиск значения с возвратом нескольких соответствующих значений с помощью формулы массива

Поиск значения с возвратом нескольких соответствующих значений с помощью Фильтра

Поиск значения с возвратом нескольких соответствующих значений с помощью Определенной Функции

Поиск значения с возвратом нескольких соответствующих значений с помощью формулы массива

Вот длинная формула массива, которая поможет вам найти значение и вернуть несколько соответствующих значений.

1. Введите значение, которое вы хотите найти, в пустую ячейку. См. скриншот:

2. В соседней ячейке введите эту формулу =ЕСЛИ(ЕОШИБКА(ИНДЕКС($A$1:$B$7,НАИМЕНЬШИЙ(ЕСЛИ($A$1:$A$7=$D$4,СТРОКА($A$1:$A$7)),СТРОКА(1:1)),2)),"",
ИНДЕКС($A$1:$B$7,НАИМЕНЬШИЙ(ЕСЛИ($A$1:$A$7=$D$4,СТРОКА($A$1:$A$7)),СТРОКА(1:1)),2)) в нее, и нажмите Shift + Ctrl + Enter клавиши вместе, затем перетащите Автозаполнение для заполнения ячеек до появления первой пустой ячейки. См. скриншот:

Примечание: В приведенной выше формуле $A$1:$B$7 указывает диапазон данных, $A$1:$A$7 обозначает диапазон столбца, в котором вы ищете определенное значение, $D$4 указывает ячейку, в которую вы вводите значение для поиска на шаге 1, 2 означает поиск соответствующих значений во втором столбце.

Поиск значения с возвратом нескольких соответствующих значений с помощью Фильтра

В Excel вы также можете использовать функцию Фильтр для решения этой проблемы.

1. Выберите диапазон столбцов, в которых вы хотите найти значение, и нажмите Данные > Фильтр. См. скриншот:

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

3. Нажмите OK, теперь вы видите, что только искомое значение и его соответствующие значения отфильтрованы.

Поиск значения с возвратом нескольких соответствующих значений с помощью Определенной Функции

Если вас интересует Определенная Функция, вы также можете решить проблему с ее помощью.

1. Нажмите Alt + F11, чтобы открыть окно Microsoft Visual Basic For Applications.

2. Нажмите Модуль > Вставить, чтобы вставить окно Модуля, и скопируйте следующий код VBA в это окно.

VBA: Поиск значения с возвратом нескольких соответствующих значений.

Function MyVlookup(pWorkRng As Range, pRng As Range, pColumnIndex As Integer, Optional pType As String = "v")
'Updateby20140827
Dim xRow As Single
Dim xCol As Single
Dim arr() As Variant
ReDim arr(0)
For i = 1 To pRng.Rows.Count
    If pWorkRng = pRng.Cells(i, 1) Then
        arr(UBound(arr)) = pRng.Cells(i, pColumnIndex)
        ReDim Preserve arr(UBound(arr) + 1)
    End If
Next
If pType = "h" Then
    xCol = Range(Application.Caller.Address).Columns.Count
    For i = UBound(arr) To xCol
        arr(UBound(arr)) = ""
        ReDim Preserve arr(UBound(arr) + 1)
    Next
    ReDim Preserve arr(UBound(arr) - 1)
    MyVlookup = arr
Else
xRow = Range(Application.Caller.Address).Rows.Count
For i = UBound(arr) To xRow
    arr(UBound(arr)) = ""
    ReDim Preserve arr(UBound(arr) + 1)
Next
ReDim Preserve arr(UBound(arr) - 1)
MyVlookup = Application.WorksheetFunction.Transpose(arr)
End If
End Function

3. Закройте окно и введите эту формулу в ячейку =MyVlookup(A10,$A$2:$B$7,2) (A10 указывает значение для поиска, $A$2:$B$7 указывает диапазон данных, 2 указывает номер индекса столбца). Нажмите Shift + Ctrl + Enter. Затем перетащите маркер заполнения вниз по ячейкам, поместите курсор в Строку формул и снова нажмите Shift + Ctrl + Enter.

Совет: Если вы хотите вернуть значения в горизонтальных ячейках, вы можете ввести эту формулу =MyVlookup(A10, $A$2:$B$7, 2, "h").