VBA VLOOKUP과 XLOOKUP

이 튜토리얼에서는 VBA에서 VLOOKUP 및 XLOOKUP 함수를 사용하는 방법을 설명합니다.

Excel의 VLOOKUP 및 XLOOKUP 함수는 매우 유용합니다. VBA 코딩에서도 사용할 수 있습니다.

VLOOKUP

VBA에서 VLOOKUP 함수를 호출하는 방법에는 두 가지가 있습니다:

• Application.WorksheetFunction.Vlookup
• Application.Vlookup

두 메서드는 오류 처리 방법을 제외하고는 동일하게 작동합니다.

Application.WorksheetFunction.VLookup

아래는 첫 번째 메서드에 대한 예제입니다.

여기서는 VBA에서 사용해볼 VLOOKUP 수식을 Excel에서 만들었습니다:

Sub LookupPrice()
   ActiveCell = Application.WorksheetFunction.VLookup(Range("E3"), Range("B2:C6"), 2, False)
End Sub

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

실제 결과는 수식 대신 값이 F3 셀에 기록되었음을 주목하세요!

 

값 대신 수식을 Excel에 반환하려면 VLOOKUP을 수식으로 작성합니다:

Sub LookupPrice()
   ActiveCell = "=VLOOKUP(E3,B2:C6,2,FALSE)"
End Sub

R1C1 표기법을 사용하여 셀에 수식을 작성하면 여러 셀 범위에서 사용할 수 있는 상대 참조가 있는 수식을 만들 수 있습니다:

Sub LookupPrice() 
  ActiveCell = "=VLOOKUP(RC[-1],RC[-4]:R[3]C[-3],2,FALSE)"
End Sub

VLOOKUP 함수에 변수를 사용할 수도 있습니다:

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

물론 함수의 결과를 셀에 쓰는 대신 변수에 저장할 수도 있습니다:

Sub LookupPrice() 
  Dim strProduct As String
  Dim rng As Range 
  Dim strResult as String
 
  strProduct = Range("E3") 
  Set rng = Range("B2:C6") 
  strResult = Application.WorksheetFunction.VLookup(strProduct, rng, 2, False) 
End Sub

Application.VLookup과 Application.WokrsheetFunction.VLookup

위의 예제에서는 두 메서드 중 하나를 사용할 수 있으며 동일한 결과를 반환합니다. 하지만 존재하지 않는 제품을 조회하는 경우 어떤 결과가 반환될까요? 각 함수는 매우 다른 결과를 반환합니다.

일반적으로 Excel에서 VLOOKUP을 사용할 때 조회에서 정답을 찾지 못하면 셀에 #N/A를 반환합니다. 그러나 위의 예제를 사용하여 존재하지 않는 제품을 찾으면 VBA 오류가 발생합니다.

코드에서 이 오류를 트랩하여 찾으려는 항목이 없을 때 오류 원인을 설명하는 메시지를 반환할 수 있습니다.

Sub LookupPrice()
  On Error GoTo eh
  ActiveCell = Application.WorksheetFunction.VLookup(Range("E3"), Range("B2:C6"), 2, False)
  Exit Sub
eh:
  MsgBox "제품이 없습니다, 다른 제품을 찾아보세요"
End Sub

VBA 코드를 수정하여 Application.WorksheetFunction.VLookup 대신 Application.Vlookup을 사용하도록 할 수도 있습니다.

Sub LookupPrice()
  ActiveCell = Application.VLookup(Range("E3"), Range("B2:C6"), 2, False)
End Sub

이렇게 하면 조회 값을 찾을 수 없을 때 #N/A가 셀에 반환됩니다.

 

WorksheetFunction.XLOOKUP

XLOOKUP 함수는 Excel에서 VLOOKUP 및 HLOOKUP 함수를 대체하도록 설계되었습니다. 이 함수는 Office 365에서만 사용할 수 있으므로 이전 버전의 Excel을 사용하는 경우 이 함수를 사용할 수 없습니다.

이 함수는 VBA에서 VLOOKUP 함수와 거의 동일한 방식으로 작동합니다.

Sub LookupPriceV()
   ActiveCell = Application.WorksheetFunction.XLookup(Range("E3"), Range("B2:B6"), Range("C2:C6"))
End Sub

아래 코드도 동일하게 작동합니다:

Sub LookupPriceV() 
   ActiveCell = Application.XLookup(Range("E3"), Range("B2:B6"), Range("C2:C6")) 
End Sub

함수에 변수를 사용하려면 다음 코드를 참고하세요.

Sub LookupPriceV()
   Dim strProduct As String
   Dim rngProduct As Range
   Dim rngPrice As Range
   strProduct = Range("E3")
   Set rngProduct = Range("B2:B6")
   Set rngPrice = Range("C2:C6")
   ActiveCell = Application.WorksheetFunction.XLookup(strProduct, rngProduct, rngPrice)
End Sub

값이 아닌 수식을 Excel로 반환하려면 VBA 에서 수식을 Excel에 작성해야 합니다.

Sub LookupPrice()
   ActiveCell = "=XLOOKUP(E3,B3:B6,C3:C6)"
End Sub

R1C1 표기법을 사용하여 셀에 수식을 작성할 수도 있습니다.

Sub LookupPrice() 
  ActiveCell.Formula2R1C1 = "=XLOOKUP(RC[-1],RC[-4]:R[3]C[-4],RC[-3]:R[3]C[-3])"
End Sub

 

VLOOKUP  함수 대비하여 XLOOKUP 함수 사용의 주요 장점 중 하나는 다양한 열을 조회하고 각 열의 값을 반환할 수 있다는 점입니다.

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

H3셀에 C2:E6 범위의 값을 조회하는 수식을 만들었습니다. 수식에 반환할 결과값을 여러 열로 지정하였기 때문에 결과값을 I 열과 J 열에도 자동으로 채웁니다. CTRL+SHIFT로 배열 수식을 사용하지 않고도 수식이 I 열과 J 열로 넘어가는 이 기능은 Excel 365에 새로 추가된 기능 중 하나입니다.

이 기능을 VBA 코드를 통해 사용하려면 다음 코드를 참고하면 됩니다:

Sub LookupPrice()
   ActiveCell.Formula2R1C1 = "=XLOOKUP(RC[-1],RC[-6]:R[3]C[-6],RC[-5]:R[3]C[-3])"
End Sub

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

WorksheetFunction 메서드를 사용하는 경우 여러 열에 대한 결과값을 한번에 추출할 수 없습니다.