Excel에서 값을 조회하여 여러 개의 해당 값을 반환하는 방법은 무엇인가요?

이 튜토리얼은 Excel에서 값을 조회하여 여러 개의 해당 값을 반환하는 방법에 대해 설명합니다. 아래 스크린샷을 참조하세요:

배열 수식을 사용하여 값을 조회하고 여러 개의 해당 값을 반환하기

필터를 사용하여 값을 조회하고 여러 개의 해당 값을 반환하기

정의된 함수를 사용하여 값을 조회하고 여러 개의 해당 값을 반환하기

배열 수식을 사용하여 값을 조회하고 여러 개의 해당 값을 반환하기

여기에는 값을 조회하고 여러 개의 해당 값을 반환하는 데 도움이 되는 긴 배열 수식이 있습니다.

1. 빈 셀에 조회하려는 값을 입력하세요. 스크린샷을 참조하세요:

2. 인접한 셀에 이 수식을 입력하세요 =IF(ISERROR(INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=$D$4,ROW($A$1:$A$7)),ROW(1:1)),2)),"",
INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=$D$4,ROW($A$1:$A$7)),ROW(1:1)),2)) 입력하고, Shift + Ctrl + Enter 키를 함께 누른 다음, 자동 채우기 를 사용하여 첫 번째 빈 셀이 나올 때까지 셀을 채우세요. 스크린샷을 참조하세요:

참고: 위 수식에서 $A$1:$B$7은 데이터 범위를 나타내고, $A$1:$A$7은 특정 값을 조회하는 열 범위를 나타내며, $D$4는1단계에서 조회 값을 입력한 셀을 나타내고,2는 두 번째 열에서 해당 값을 찾는 것을 나타냅니다.

필터를 사용하여 값을 조회하고 여러 개의 해당 값을 반환하기

Excel에서는 필터 기능을 사용하여 이 문제를 해결할 수도 있습니다.

1. 조회하려는 열 범위를 선택하고 데이터 > 필터를 클릭하세요. 스크린샷을 참조하세요:

2. 선택한 범위의 첫 번째 셀에서 화살표 버튼을 클릭하고 드롭다운 목록에서 조회하려는 값만 선택하세요. 스크린샷을 참조하세요:

3. 확인을 클릭하면 이제 조회 값과 해당 값만 필터링된 것을 볼 수 있습니다.

정의된 함수를 사용하여 값을 조회하고 여러 개의 해당 값을 반환하기

정의된 함수에 관심이 있다면, 정의된 함수를 사용하여 이 문제를 해결할 수도 있습니다.

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").