VBA 다른 통합문서나 시트의 데이터에 대해 VLOOKUP 또는 XLOOKUP 사용하기

이 튜토리얼에서는 다른 시트 또는 다른 통합 문서의 값에 대해서 VBA에서 VLOOKUP 및 XLOOPUP 함수를 사용하는 방법을 보여줍니다.

Excel의 VLOOKUP 및 XLOOKUP 함수는 범위에서 일치하는 값을 검색하고 다른 열에서 해당 값을 반환할 수 있는 유용한 함수입니다. Excel의 수식에서 직접 사용할 수도 있고, WorksheetFunction 메서드를 사용하여 VBA 코딩에서 사용할 수도 있습니다. 결과를 생성하려는 시트가 아닌 다른 시트 또는 다른 통합 문서에 있는 값을 대상으로 함수를 사용할 수도 있습니다.

다른 시트 데이터를 대상으로 VLOOKUP과 XLOOKUP 함수 사용하기

다른 시트 데이터를 대상으로 VLOOKUP 사용하기

조회 데이터가 있는 시트와 다른 시트에서 VLOOKUP을 사용하려면 다음 코드를 사용하면 됩니다:

Sub LookupPrice()
   ActiveCell = Application.WorksheetFunction.VLookup(Range("B3"), Sheets("제품목록").Range("B2:C6"), 2, False)
End Sub

또는 아래 코드를 사용할 수 있습니다:

Sub LookupPrice() 
   ActiveCell = Application.VLookup(Range("B3"), Sheets("제품목록").Range("B2:C6"), 2, False) 
End Sub

결과 값은 다음과 같습니다:

코드를 간소화하기 위해 변수를 사용할 수도 있습니다.

Sub LookupPrice()
   Dim ws As Worksheet
   Dim rng As Range
   Dim strProduct As String
   strProduct = Range("B3")
   Set ws = Sheets(1)
   Set rng = ws.Range("B2:C6")
   ActiveCell = Application.WorksheetFunction.VLookup(strProduct, rng, 2, False)
End Sub

위 코드는 동일한 결과를 반환합니다.

존재하지 않는 값을 조회하는 경우, VBA 오류가 발생할 수 있는 Application.WorksheetFunction.VLookup 함수 대신 Application.VLookup 함수를 사용하여 셀에 #N/A를 반환하는 것이 더 좋을 수 있습니다.

대상 셀에 값이 아닌 수식을 반환하려는 경우 아래 코드를 사용할 수 있습니다.

Sub LookupPrice()
   ActiveCell = "=VLOOKUP(RC[-1],제품목록!RC[-1]:R[3]C,2,FALSE)"
End Sub

여기서 수식은 A1(범위) 구문 대신 R1C1(행 및 열) 구문을 사용합니다.

결과는 다음과 같습니다:

다른 시트 데이터를 대상으로 XLOOKUP 사용하기

다른 시트의 데이터를 조회하기 위해 XLOOKUP을 사용하는 것은 위에서 설명한 VLOOKUP과 거의 동일합니다. XLOOKUP은 값을 찾기 위해 특정 열이 아닌 여러 범위를 살펴본다는 점을 기억하세요.

Sub LookupPrice()
   ActiveCell = Application.WorksheetFunction.XLookup(Range("B3"), Sheets("제품목록").Range("B2:B6"), Sheets("제품목록").Range("C2:C6"))
End Sub

함수에 변수를 사용한 코드는 다음과 같습니다:

Sub LookupPrice()
  Dim ws As Worksheet
  Dim strProduct As String
  Dim rngProduct As Range
  Dim rngPrice As Range
  strProduct = Range("B3")
  Set ws = Sheets("제품목록")
  Set rngProduct = ws.Range("B2:B6")
  Set rngPrice = ws.Range("C2:C6")
  ActiveCell = Application.WorksheetFunction.XLookup(strProduct, rngProduct, rngPrice)
End Sub

위 두 예제의 실행 결과는 다음과 같습니다:

XLOOKUP을 사용하여 수식을 반환하려면 범위(A1) 구문이 아닌 행/열(R1C1) 구문을 사용해야 합니다.

Sub EnterFormula
  ActiveCell.FormulaR1C1 = "=XLOOKUP(RC[-1],ProductList!RC[-1]:R[3]C[-1],제품목록!RC:R[3]C)"
End Sub

코드 실행 결과는 다음과 같습니다:

VLOOKUP과 비교했을 때 XLOOKUP사용의 주요 장점 중 하나는 다양한 열을 조회하고 각 열의 값을 모두 한번에 반환할 수 있다는 점입니다.

다음 예제를 살펴보겠습니다:

C3 셀에 C2:E6 범위의 값을 조회하는 수식을 만들었습니다. 이 수식은 여러 열이 있기 때문에 발견된 일치하는 결과로 열 D와 열 E를 자동으로 채웁니다. 배열 수식에 CTRL+SHIFT를 사용하지 않고도 수식이 D 및 E 열로 넘어가는 이 기능은 Excel 365에 새로 추가된 기능 중 하나입니다.

이 기능을 VBA 코드로 복제하려면 매크로에 다음과 같이 입력하면 됩니다:

Sub LookupPrice()
   ActiveCell.Formula2R1C1 = "=XLOOKUP(RC[-1],제품목록!RC[-1]:R[3]C[-1],제품목록!RC:R[3]C[2])"
End Sub

여기서 수식은 A1(범위) 구문 대신 R1C1(행 및 열) 구문을 사용합니다. 이렇게 하면 위 이미지와 같이 수식이 셀에 입력됩니다.

WorksheetFunction 메서드를 사용하는 경우 여러 열을 조회할 수 없습니다.

다른 통합 문서 데이터를 대상으로 VLOOKUP과 XLOOKUP 함수 사용하기

다른 통합 문서 데이터에 대해 작업하는 방법은 다른 시트의 데이터에 대해 작업하는 방법과 거의 동일합니다.

다른 통합 문서 데이터를 대상으로 VLOOKUP  함수 사용하기

다음 예제를 살펴 보겠습니다:

이를 VBA 코드로 구현하려면 다음 매크로를 참조해 주세요:

Sub LookupPrice()    
  ActiveCell = Application.WorksheetFunction.VLookup(Range("B3"), Workbooks("제품목록.xlsm").Sheets("제품목록").Range("B2:C6"), 2, False)
End Sub

여기서 이 수식이 작동하려면 제품목록.xlsm 파일이 Excel에서 열려 있어야 합니다.

코드에서 변수를 사용할 수도 있습니다:

Sub LookupPrice()
  Dim wb As Workbook
  Dim ws As Worksheet
  Dim rng As Range
  Dim strProduct As String
  Set wb = Workbooks("제품목록.xlsm")
  Set ws = wb.Sheets("제품목록")
  Set rng = ws.Range("B2:C6")
  strProduct = Range("B3")
  ActiveCell = Application.WorksheetFunction.VLookup(strProduct, rng, 2, False)
End Sub

위 두 예제의 결과는 다음과 같습니다:

값이 아닌 실제 수식을 셀에 표시하려면 VBA 코드에 통합 문서 이름과 시트 이름을 포함해야 합니다.

예제 코드는 다음과 같습니다:

Sub LookupPrice()
  ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],[제품목록.xlsm]제품목록!R3C2:R6C3,2,FALSE)"
End Sub

다른 통합 문서 데이터를 대상으로 XLOOKUP  함수 사용하기

다른 통합 문서 데이터를 대상으로 XLOOKUP을사용하는 방법은 위에서 설명한 VLOOKUP과 거의 동일합니다.

값을 Excel로 반환하려면 다음 코드를 참조해 주세요:

Sub LookupPrice()
   ActiveCell = Application.WorksheetFunction.XLookup(Range("B3"),  Workbooks("제품목록.xlsm").Sheets("제품목록").Range("B2:B6"), Workbooks("제품목록.xlsm").Sheets("제품목록").Range("C2:C6"))
End Sub

아래와 같이 코드에서 변수를 사용할 수도 있습니다:

Sub LookupPrice()
  Dim wb as Workbook
  Dim ws As Worksheet
  Dim strProduct As String
  Dim rngProduct As Range
  Dim rngPrice As Range
  strProduct = Range("B3")
  Set wb = Workbooks("제품목록.xlsm")
  Set ws = wb.Sheets("제품목록")
  Set rngProduct = ws.Range("B2:B6")
  Set rngPrice = ws.Range("C2:C6")
  ActiveCell = Application.WorksheetFunction.XLookup(strProduct, rngProduct, rngPrice)
End Sub

위 두 예제 코드의 실행 결과는 다음과 같습니다:

값이 아닌 수식을 Excel로 반환하려면 통합 문서 이름과 시트 이름을 포함하도록 매크로를 수정해야합니다.

Sub LookupPrice()
   ActiveCell.Formula2R1C1 = "=XLOOKUP(RC[-1],[제품목록.xlsm]제품목록!RC[-1]:R[3]C[-1],[제품목록.xlsm]제품목록!RC:R[3]C[2])"
End Sub