Skip to main content

Kutools for Office — 하나의 패키지. 다섯 가지 도구. 더 많은 일을 처리하세요.

Excel에서 활성 하이퍼링크를 반환하기 위해 조회하는 방법은 무엇입니까?

Author Xiaoyang Last modified

일상적인 Excel 작업에서, 특정 데이터 항목과 관련된 값을 찾고 반환하기 위해 VLOOKUP 함수를 사용하는 것은 일반적입니다. 그러나 조회 결과로 하이퍼링크가 반환될 때 자주 발생하는 문제는, 클릭 가능한 활성 링크 대신에 일반 텍스트 URL만 얻게 된다는 것입니다. 예를 들어, 소스 데이터에 삽입된 하이퍼링크가 포함된 웹사이트 링크가 있는 경우, 일반적인 VLOOKUP 이후에는 결과가 서식 없는 텍스트로만 표시됩니다. 아래에 예시가 있습니다.

A screenshot showing the issue of VLOOKUP returning plain text instead of hyperlinks in Excel

이러한 경우 조회 결과로 활성 하이퍼링크(클릭할 수 있으며 브라우저에서 열리는 하이퍼링크)를 반환하도록 하는 것은 사용성을 향상시키고 시간을 절약하며 웹 주소, 파일 경로 또는 기타 클릭 가능한 리소스를 포함하는 데이터 세트에 중요합니다.

이 튜토리얼에서는 조회를 통해 활성 하이퍼링크를 반환하기 위한 여러 실용적인 솔루션을 소개하고, 그 시나리오, 적용 가능한 데이터 유형 및 잠재적인 제한 사항을 분석합니다. 또한 최선의 방법을 선택하기 위한 주요 주의사항, 문제 해결 팁 및 권장 사항도 배울 수 있습니다.

arrow blue right bubble수식을 이용하여 활성 하이퍼링크를 조회하여 반환하기

조회하고 활성 하이퍼링크로 값을 반환하려면 HYPERLINK와 VLOOKUP 기능을 결합할 수 있습니다. 이 접근법은 하이퍼링크가 텍스트 URL 주소(예: “https://www.example.com” 또는 네트워크 파일 경로)로 깨끗하게 저장된 소스 데이터에 간단하고 적합합니다. 이를 통해 반환된 값이 워크시트에서 클릭 가능하게 됩니다.

조회 값(예: 이름) 하나와 URL을 일반 텍스트 또는 하이퍼링크로 포함하는 두 개의 열이 있는 테이블이 있다고 가정해 보겠습니다. 사용자 입력 값에 따라 해당 활성 하이퍼링크를 검색하려면 다음 단계를 따르세요:

1. 결과를 표시하려는 빈 셀에 다음 수식을 입력하세요:

=HYPERLINK(VLOOKUP(D2, $A$1:$B$8,2, FALSE))

2. Enter 키를 눌러 확인하세요. 이제 셀에 하이퍼링크가 활성화되어 클릭 가능한 링크로 표시됩니다. 아래에 표시된 것처럼 말이죠:

A screenshot showing the use of HYPERLINK and VLOOKUP formula to return active hyperlinks in Excel

매개변수 및 사용법 참고:

  • D2: 조회하려는 값을 포함하는 셀입니다.
  • $A$1:$B$8: 첫 번째 열에 조회 값이 있고 두 번째 열에 하이퍼링크가 있는 데이터 범위입니다. 수식을 복사할 계획이라면 절대 참조를 사용하세요.
  • 2: 하이퍼링크가 범위의 두 번째 열에 있음을 나타냅니다.

팁:

  • 조회 값이 발견되지 않으면 수식은 오류(#N/A)를 반환합니다. 조회 값이 테이블 범위에 정확히 일치하는지 다시 확인하세요.
  • 표시되는 텍스트를 실제 하이퍼링크와 다르게 하고 싶다면(예: URL 대신 이름 표시), 다음과 같이 선택적 두 번째 매개변수를 추가할 수 있습니다: HYPERLINK:
    =HYPERLINK(VLOOKUP(D2,$A$1:$B$8,2,FALSE),D2)
    이는 D2의 값을 링크 텍스트로 표시합니다.
  • 이 방법은 하이퍼링크가 표준 URL이나 파일 경로 텍스트로 저장된 경우에만 작동합니다. 셀 내부에 원시 URL이 없는 상태에서 표시 텍스트와 하이퍼링크 주소가 다른 Excel 삽입 하이퍼링크를 복구하지 않으며, '친근한' 표시 이름에도 적용되지 않습니다.

일반적인 문제 및 문제 해결:

  • 결과가 클릭 가능하지 않다면, 데이터에 전체 유효한 웹 URL(“http://” 또는 “https://” 포함)이 있는지 확인하세요.
  • 잘못되거나 누락된 결과가 나오면 조회 범위를 확인하고, 하이퍼링크가 포함된 열 인덱스가 맞는지 확인하세요.
  • 로컬 파일의 경우, 하이퍼링크 경로가 올바른 형식(예: "C:\Folder\file.xlsx")을 사용하는지 확인하세요.

장점: 설정이 간단하며, 수식을 드래그해서 여러 행에 적용 가능하고, 하이퍼링크가 일반 텍스트 URI로 저장된 표에 가장 적합합니다.

제한 사항: 표시 텍스트와 하이퍼링크 주소가 다른 경우 각각을 별도로 검색할 수 없으며, 셀에 표시 텍스트만 있을 경우 수동으로 삽입된 하이퍼링크를 인식하지 못합니다.

arrow blue right bubbleVBA 코드 – 조회를 통한 활성 하이퍼링크 반환 및 삽입 (고급 시나리오)

특정 값을 범위에서 조회하고, 단순한 일반 URL이 아닌 원본 활성 하이퍼링크(표시 텍스트와 클릭 가능한 링크 모두 포함)를 반환해야 할 경우, VBA는 신뢰할 수 있는 해결책을 제공합니다. 이 코드는 선택한 범위에서 목표 값을 검색하고, 일치하는 항목이 발견되면 정확한 하이퍼링크(텍스트 + 주소)를 지정한 셀에 복사합니다. 이는 표시 텍스트와 하이퍼링크 주소가 다른 경우 또는 수식 기반 방법으로 실제 링크를 캡처할 수 없는 경우 특히 유용합니다.

이 방법은 '친근한' 표시 이름과 함께 기본 하이퍼링크가 포함된 데이터, 파일이나 폴더에 대한 하이퍼링크 또는 비표준 하이퍼링크 형식을 포함하는 데이터에 특히 유용합니다. VBA를 사용하면 표시되는 링크 텍스트와 기본 하이퍼링크 주소를 모두 복사하거나 조회 결과를 사용하여 새 위치에 하이퍼링크를 재삽입할 수 있습니다.

주의 사항: Excel 환경에서 매크로가 활성화되어 있는지 확인하세요. 중요한 데이터를 다룰 때는 항상 VBA 스크립트 실행 전에 워크북을 백업하세요.

장점: 셀에 삽입된 하이퍼링크 및 표시 텍스트와 하이퍼링크 주소의 분리를 포함한 복잡한 사례를 처리할 수 있습니다. 배치 처리를 지원하며 결과를 사용자 정의할 수 있습니다.

제한 사항: 기본적인 VBA 지식이 필요하며 모든 제한적이거나 웹 기반 Excel 환경에서 지원되지 않을 수 있습니다.

1. 개발 도구 > Visual Basic을 클릭하여 VBA 편집기를 엽니다. 새로 열린 창에서 삽입 > 모듈을 클릭하고 다음 코드를 모듈에 붙여넣습니다:

Sub LookupAndInsertHyperlink()
    Dim LookupValue As String
    Dim LookupRange As Range
    Dim ResultCell As Range
    Dim cell As Range
    Dim hyperlinkFound As Boolean
    Dim linkAddress As String
    Dim linkText As String
    
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    
    Set LookupRange = Application.InputBox("Select the lookup range (must include display text/cell and hyperlink)", xTitleId, Selection.Address, Type:=8)
    Set ResultCell = Application.InputBox("Select the cell to output the hyperlink", xTitleId, "", Type:=8)
    LookupValue = Application.InputBox("Enter the value to lookup", xTitleId, "", Type:=2)
    
    hyperlinkFound = False
    For Each cell In LookupRange
        If cell.Value = LookupValue Then
            If cell.Hyperlinks.Count > 0 Then
                linkAddress = cell.Hyperlinks(1).Address
                linkText = cell.Value
                ResultCell.Hyperlinks.Add Anchor:=ResultCell, Address:=linkAddress, TextToDisplay:=linkText
                hyperlinkFound = True
                Exit For
            End If
        End If
    Next
    
    If Not hyperlinkFound Then
        ResultCell.Value = "No matching hyperlink found"
    End If
End Sub

2. 스크립트를 실행하려면 워크북이 열린 상태에서 Alt + F8을 누르고 LookupAndInsertHyperlink를 선택한 다음 실행을 클릭하세요.

3. 나타나는 대화 상자에서:

  • 조회 데이터 범위를 선택하세요(값과 하이퍼링크 모두 포함).
  • 출력 하이퍼링크의 대상 셀을 선택하세요.
  • 검색하려는 조회 값을 입력하세요. 매크로는 일치하는 값을 찾아 해당 하이퍼링크를 추출하고(표시 텍스트가 기본 링크와 다르더라도) 선택한 위치에 활성 하이퍼링크로 삽입합니다.

실용적인 팁과 오류 알림:

  • 값을 찾을 수 없거나 셀에 하이퍼링크가 없는 경우, 대상은 “일치하는 하이퍼링크를 찾을 수 없습니다.”라고 표시됩니다.
  • 여러 조회를 한 번에 처리하려면 VBA 코드를 확장하거나 필요한 만큼 여러 번 스크립트를 실행하는 것을 고려하세요.
  • 이 스크립트는 하이퍼링크가 셀에 단순한 URL 문자열이 아닐 때에도 효과적이며, 주소와 표시 텍스트 모두를 복사합니다.

문제 해결 제안:

  • 입력 범위에 실제 하이퍼링크가 포함된 열이 있는지 확인하세요.
  • VBA 매크로가 실행되지 않으면 Excel 설정에서 매크로가 활성화되어 있는지 확인하세요.
  • “일치하는 하이퍼링크를 찾을 수 없습니다.”가 나타난다면 조회 값이 올바른지 그리고 해당 행에 하이퍼링크가 있는지 다시 확인하세요.
  • 매크로를 실행하기 전에 언제나 워크북을 저장하여 변경 사항을 되돌릴 수 있도록 하세요.

요약:

  • 표준 텍스트 기반 하이퍼링크 및 빠른 조회에는 수식 방법을 사용하세요.
  • 수동으로 삽입된 하이퍼링크 복원, 표시 텍스트와 링크 주소 검색 또는 동적으로 범위에 걸쳐 결과 적용과 같은 더 발전된 요구 사항에는 VBA 방법을 사용하세요.

최고의 오피스 생산성 도구

🤖 Kutools AI 도우미: 데이터 분석에 혁신을 가져옵니다. 방법: 지능형 실행   |  코드 생성  |  사용자 정의 수식 생성  |  데이터 분석 및 차트 생성  |  Kutools Functions 호출…
인기 기능: 중복 찾기, 강조 또는 중복 표시   |  빈 행 삭제   |  데이터 손실 없이 열 또는 셀 병합   |   반올림(수식 없이) ...
슈퍼 LOOKUP: 다중 조건 VLOOKUP  |   다중 값 VLOOKUP  |   다중 시트 조회   |   퍼지 매치 ....
고급 드롭다운 목록: 드롭다운 목록 빠르게 생성   |  종속 드롭다운 목록   |  다중 선택 드롭다운 목록 ....
열 관리자: 지정한 수의 열 추가  |  열 이동  |  숨겨진 열의 표시 상태 전환  |  범위 및 열 비교 ...
추천 기능: 그리드 포커스   |  디자인 보기   |   향상된 수식 표시줄   |  통합 문서 & 시트 관리자   |  자동 텍스트 라이브러리   |  날짜 선택기   |  데이터 병합   |  셀 암호화/해독   |  목록으로 이메일 보내기   |  슈퍼 필터   |   특수 필터(굵게/이탤릭/취소선 필터 등) ...
15대 주요 도구 세트: 12 가지 텍스트 도구(텍스트 추가, 특정 문자 삭제, ...)  |  50+ 종류의 차트(간트 차트, ...)  |  40+ 실용적 수식(생일을 기반으로 나이 계산, ...)  |  19 가지 삽입 도구(QR 코드 삽입, 경로에서 그림 삽입, ...)  |  12 가지 변환 도구(단어로 변환하기, 통화 변환, ...)  |  7 가지 병합 & 분할 도구(고급 행 병합, 셀 분할, ...)  |  ... 등 다양
Kutools는 사용자가 선호하는 언어로 이용할 수 있습니다 – 영어, 스페인어, 독일어, 프랑스어, 중국어 및40개 이상의 언어 지원!

Kutools for Excel과 함께 엑셀 능력을 한 단계 끌어 올리고, 이전에 없던 효율성을 경험하세요. Kutools for Excel은300개 이상의 고급 기능으로 생산성을 높이고 저장 시간을 단축합니다.  가장 필요한 기능을 바로 확인하려면 여기를 클릭하세요...

Office Tab은 Office에 탭 인터페이스를 제공하여 작업을 더욱 간편하게 만듭니다

  • Word, Excel, PowerPoint에서 탭 편집 및 읽기를 활성화합니다.
  • 새 창 대신 같은 창의 새로운 탭에서 여러 파일을 열고 생성할 수 있습니다.
  • 생산성이50% 증가하며, 매일 수백 번의 마우스 클릭을 줄여줍니다!

모든 Kutools 추가 기능. 한 번에 설치

Kutools for Office 제품군은 Excel, Word, Outlook, PowerPoint용 추가 기능과 Office Tab Pro를 한 번에 제공하여 Office 앱을 활용하는 팀에 최적입니다.

Excel Word Outlook Tabs PowerPoint
  • 올인원 제품군 — Excel, Word, Outlook, PowerPoint 추가 기능 + Office Tab Pro
  • 설치 한 번, 라이선스 한 번 — 몇 분 만에 손쉽게 설정(MSI 지원)
  • 함께 사용할 때 더욱 효율적 — Office 앱 간 생산성 향상
  • 30일 모든 기능 사용 가능 — 회원가입/카드 불필요
  • 최고의 가성비 — 개별 추가 기능 구매 대비 절약