다른 통합 문서에서 값을 조회/찾는 방법은 무엇인가요？

VLOOKUP 을 사용하여 조회 참조를 구성하는 것은 특히 원본 파일의 경로, 이름 또는 워크시트를 자주 변경하는 경우 혼란스러울 수 있습니다. 이러한 상황에서는 VBA 를 사용해 조회 프로세스를 자동화하는 것이 더 효율적인 해결책이 될 수 있습니다。 이를 통해 원본 워크북이 닫혀 있어도 값을 조회하고 범위 선택 및 데이터 반환을 자동화할 수 있습니다。

VBA 를 사용하여 워크북 간 조회를 수행하려면 다음 단계를 따르세요：

1.Alt+F11을 동시에 눌러Microsoft Visual Basic for Applications편집기 창을 엽니다。

2.VBA 편집기에서삽입>모듈을 클릭한 후 다음 코드를 모듈 창에 복사하여 붙여넣습니다：

VBA： 다른 닫힌 워크북에서 Vlookup 데이터 및 반환 값 가져오기

Option Explicit

' Convert column number to column letter
Private Function GetColumn(ByVal Num As Integer) As String
    If Num <= 26 Then
        GetColumn = Chr(Num + 64)
    Else
        GetColumn = Chr((Num - 1) \ 26 + 64) & _
                    Chr((Num - 1) Mod 26 + 65)
    End If
End Function

Sub FindValue()

    Dim xAddress As String
    Dim xString As String
    Dim xFileName As Variant
    Dim xUserRange As Range
    Dim xRg As Range
    Dim xFCell As Range
    Dim xSourceSh As Worksheet
    Dim xSourceWb As Workbook
    
    On Error Resume Next
    
    ' Get current selection address
    xAddress = Application.ActiveWindow.RangeSelection.Address
    
    ' Ask user to select lookup range
    Set xUserRange = Application.InputBox( _
        Prompt:="Lookup values :", _
        Title:="Kutools for Excel", _
        Default:=xAddress, _
        Type:=8)
    
    If Err.Number <> 0 Then Exit Sub
    On Error GoTo 0
    
    ' Limit selection to used range
    Set xUserRange = Application.Intersect(xUserRange, _
                                           Application.ActiveSheet.UsedRange)
    
    ' Ask user to select source workbook
    xFileName = Application.GetOpenFilename( _
                "Excel Files (*.xlsx), *.xlsx", _
                1, _
                "Select a Workbook")
                
    If xFileName = False Then Exit Sub
    
    Application.ScreenUpdating = False
    
    ' Open source workbook
    Set xSourceWb = Workbooks.Open(xFileName)
    Set xSourceSh = xSourceWb.Worksheets.Item(1)
    
    ' Build external reference string
    xString = "='" & xSourceWb.Path & Application.PathSeparator & _
              "[" & xSourceWb.Name & "]" & _
              xSourceSh.Name & "'!$"
    
    ' Loop through user range
    For Each xRg In xUserRange
    
        ' Find matching value in source sheet
        Set xFCell = xSourceSh.Cells.Find( _
                        What:=xRg.Value, _
                        LookIn:=xlValues, _
                        LookAt:=xlWhole, _
                        MatchCase:=False)
        
        ' If found, write formula 2 columns to the right
        If Not xFCell Is Nothing Then
            xRg.Offset(0, 2).Formula = _
                xString & _
                GetColumn(xFCell.Column + 1) & _
                "$" & xFCell.Row
        End If
        
    Next xRg
    
    ' Close source workbook without saving
    xSourceWb.Close False
    
    Application.ScreenUpdating = True

End Sub

중요한 세부 사항：

• 이 코드는 조회 범위에서 오른쪽으로 2 열 떨어진 열에 일치하는 값을 반환합니다. 예를 들어, B 열을 선택하면 결과는 D 열에 나타납니다。
• 결과를 다른 열에 표시하려면 다음 코드에서 숫자2를xRg.Offset(0,2).Formula다른 값(예：1다음 열의 경우，3오른쪽 세 번째 열의 경우)으로 변경하세요。
• 메시지가 표시되면 올바른 워크북과 워크시트를 선택하세요。 이 코드는 항상 선택한 파일의 첫 번째 워크시트를 사용합니다。 원본 시트가 첫 번째가 아니라면 코드를 수정하세요。
• 익숙하지 않은 매크로를 실행하기 전에는 항상 파일을 저장하세요。 매크로는 실행 후 취소할 수 없습니다。

3.F5키를 누르거나실행버튼을 클릭하여 매크로를 실행합니다。 「Kutools for Excel」이라는 제목의 대화 상자가 나타나 조회할 셀 범위를 선택하라는 메시지를 표시합니다。

4.범위를 선택한 후확인을 클릭합니다。 잠시 후 또 다른 대화 상자가 나타나 원본 워크북(닫혀 있어도 가능)을 선택하라는 메시지를 표시합니다。 해당 파일을 찾아 선택한 후열기를 클릭하여 확인합니다。

매크로가 완료되면 원본 워크북의 해당 값이 현재 워크시트의 대상 열로 반환됩니다。 일부 값이 누락된 경우， 현재 워크시트의 검색 값 범위이 원본 데이터의 항목과 정확히 일치하는지 확인하세요(대소문자 및 앞뒤 뒤쪽의 공백이 정확히 일치해야 합니다)。

장점：닫힌 워크북을 처리하고 수식 내 하드코딩된 파일 경로을 피하며 소스 파일을 실시간으로 유연하게 선택할 수 있습니다。
고려 사항：매크로를 사용하려면 매크로가 활성화되어 있어야 하며, 보호된 시트나 비-Excel 파일에서는 VBA 가 작동하지 않을 수 있습니다。 이 방법을 자주 사용할 계획이라면 매크로 지원(*。xlsm) 형식으로 워크북을 저장하세요。

오류가 발생하면 시트나 파일 이름에 오타가 없는지， 범위 선택이 적절한지， 파일 경로가 접근 가능한지 확인하세요。 디버깅을 위해 VBA 편집기를 사용하여 코드를 한 줄씩 단계별로 실행해 보는 것도 좋습니다。

클래식한 VLOOKUP 방식과 VBA 외에도 Excel 에서 워크북 간 조회를 수행하는 대체 방법이 있습니다。 데이터 구조가 다른 경우， 매크로보다 수식을 선호하는 경우， 또는 왼쪽 검색이나 다중 조건 등 더 많은 유연성이 필요한 상황에서 이러한 방법이 더 적합할 수 있습니다。

워크북 간 INDEX 및 MATCH 함수 사용

INDEX 와 MATCH 를 조합하면 다른 워크북에서 왼쪽, 오른쪽, 위, 아래 어느 방향으로든 값을 조회할 수 있습니다. 이는 조회 열의 오른쪽에 반환할 열이 없을 때(즉, VLOOKUP 의 제한사항인 경우) 특히 유용합니다。

시나리오：과일 이름이 첫 번째 열이 아닐 수도 있는 다른 열린 워크북에서 가격을 조회하려고 한다고 가정합니다。

1.대상 워크북에서 결과를 표시할 셀(예： C2)을 선택하고 아래 수식을 입력하세요(워크북， 시트， 범위는 필요에 따라 변경)：

=INDEX([Price.xlsx]Sheet1!$B$1:$B$24, MATCH(B2, [Price.xlsx]Sheet1!$A$1:$A$24,0))

2.Enter를 누른 후 채우기 핸들을 드래그하여 필요에 따라 다른 행으로 수식을 복사하거나 채우세요。

매개변수 설명：

• [Price.xlsx]Sheet1!$B$1:$B$24： 가격이 저장된 범위입니다。
• B2： 조회할 과일 이름입니다。
• [Price.xlsx]Sheet1!$A$1:$A$24： 조회 값을 검색할 범위입니다。
• 맨 끝의0는 정확히 일치함을 보장합니다。

강점：왼쪽 또는 오른쪽 조회가 가능하며 더 유연한 레이아웃을 지원합니다。
팁：소스 파일을 이동하거나 이름을 변경할 경우 수식을 반드시 업데이트하세요。

워크북 간 조회를 위한 XLOOKUP 사용(Excel 365 이상)

Excel 365 또는 Excel 2021 을 사용하는 경우 새롭게 도입된 XLOOKUP 함수가 더욱 유연합니다。 정확히 일치하는 항목을 쉽게 찾고 왼쪽 조회를 지원하며 조회 실패 시 오류 없이 자동으로 미리 지정한 값을 표시할 수 있습니다。

사용 방법：
결과를 표시할 셀에 다음을 입력하세요：

=XLOOKUP(B2, [Price.xlsx]Sheet1!$A$1:$A$24, [Price.xlsx]Sheet1!$B$1:$B$24, "Not found")

Enter를 누르고 필요에 따라 수식을 복사하세요。 여기서 「Not found」는 조회 실패 시 표시할 사용자 지정 텍스트로 변경할 수 있습니다。

장점：VLOOKUP 보다 유연하고 관리가 쉬우며 기존 수식에서 발생하는 일반적인 오류를 많이 방지합니다. 다만 XLOOKUP 은 최신 Excel 버전에서만 사용 가능합니다。

다중 조건 매칭, 병합된 시트에서 검색, 대용량 파일의 성능 문제 회피 등 복잡한 요구사항이 있는 경우 구조화된 테이블로 원본 데이터을 구성하거나 Excel 의 Power Query 도구를 사용하여 여러 워크북 간에 데이터 연결를 효율적으로 수행하는 것을 고려하세요。