엑셀에서 검색 가능한 드롭다운 목록 만드는 2가지 방법 (수식 & VBA)

엑셀에서 검색 가능한 드롭다운 목록을 만들면 방대한 데이터 속에서도 원하는 항목을 빠르게 찾을 수 있습니다. 이 글에서는 수식과 VBA를 활용해 검색 기능이 있는 드롭다운 목록을 만드는 두 가지 방법을 단계별로 소개합니다.

아래 다운로드 버튼을 통해 실습용 통합 문서를 받아 직접 따라 해볼 수 있습니다.

방법 1. 엑셀 수식으로 검색 가능한 드롭다운 목록 만들기

먼저 수식만으로 검색 가능한 드롭다운 목록을 만드는 방법을 알아보겠습니다. 예시로 미국 초기 13개 주의 정보가 담긴 States 시트의 데이터를 활용하고, Dropdown 시트의 B4 셀에 드롭다운 목록을 생성한다고 가정합니다.

1단계. FILTER 함수로 검색 결과 목록 준비

📌 순서

• States 시트의 E5 셀에 아래 수식을 입력합니다.

• SEARCH 함수: 지정한 값(검색어)이 데이터 내에 있는지 찾습니다.
• ISNUMBER 함수: SEARCH 함수의 결과가 숫자이면 TRUE, 아니면 FALSE를 반환합니다.
• FILTER 함수: 주어진 조건에 맞는 데이터만 걸러서 표시합니다.

2단계. 데이터 유효성 검사 설정

• Dropdown 시트에서 B4 셀을 선택한 뒤, 리본 메뉴에서 데이터 >> 데이터 유효성 검사를 클릭합니다.

• 데이터 유효성 창에서 설정 탭을 열고, 제한 대상 항목의 드롭다운 화살표를 눌러 목록을 선택합니다.
• 원본 입력란에 아래 수식을 입력합니다.

• 이어서 오류 경고 탭으로 이동합니다.

• 잘못된 데이터가 입력되면 오류 메시지 표시 옵션의 체크를 해제하고 확인 버튼을 누릅니다.

• 이제 검색 가능한 드롭다운 목록이 완성되었습니다. B4 셀에 검색어를 입력하면, 셀 오른쪽 아래에 나타나는 드롭다운 화살표를 클릭했을 때 해당 검색어와 일치하는 결과들만 목록에 표시됩니다.

함께 읽으면 좋은 글: 엑셀에서 수식 기반 드롭다운 목록 만드는 4가지 방법

참고할 만한 관련 자료

• 엑셀에서 필터링된 드롭다운 목록 복사하기 (5가지 방법)
• 엑셀 테이블로 드롭다운 목록 만들기 (5가지 예제)
• 엑셀에서 범위로 목록 만들기 (3가지 방법)
• 엑셀 드롭다운 목록 자동 업데이트 (3가지 방법)
• 엑셀에서 다중 선택 리스트박스 만들기

방법 2. 엑셀 VBA로 구글 검색처럼 작동하는 드롭다운 만들기

첫 번째 방법은 드롭다운 화살표를 클릭해야 결과를 확인할 수 있습니다. 만약 구글 검색처럼 입력하는 즉시 검색 결과가 나타나게 하고 싶다면 VBA를 활용한 아래 방법을 사용하세요.

📌 순서

• 먼저 앞선 방법의 데이터 >> 데이터 유효성 검사 단계까지 동일하게 진행합니다.
• States 시트에서 E5 셀을 선택한 후, 수식 >> 이름 관리자를 클릭합니다.

• 이름 관리자 창에서 새로 만들기를 클릭해 목록 이름을 정의합니다.

• 새 이름 창에서 이름을 Dropdown_List로 변경합니다.
• 참조 대상 입력란에 아래 수식을 입력하고 확인 버튼을 누릅니다. 이 수식은 INDEX 함수와 COUNTIF 함수를 활용합니다.

• Dropdown 시트로 이동한 뒤, 개발 도구 탭에서 삽입 >> 콤보 상자를 선택합니다.

• 마우스를 드래그하여 ComboBox 크기를 적절히 조절합니다.

• 그러면 아래와 같은 새 ComboBox가 생성됩니다.

• ComboBox를 마우스 오른쪽 버튼으로 클릭하고 속성을 선택합니다.

• 속성 창에서 Alphabetic 탭을 연 뒤, 아래와 같이 값을 변경합니다.
  AutoWordSelect >> False, LinkedCell >> B4, MatchEntry >> 2 – fnMatchEntryNone

• 아래 VBA 코드를 복사합니다.

Private Sub ComboBox1_Change()

ComboBox1.ListFillRange = "Dropdown_List"

Me.ComboBox1.DropDown

End Sub

• ComboBox를 더블클릭하면 VBA 편집기(Microsoft Visual Basic)의 새 모듈로 이동합니다. 빈 모듈에 복사한 코드를 붙여넣고 F5 키를 눌러 코드를 실행합니다.

• 이제 검색 가능한 드롭다운이 구글 검색처럼 실시간으로 작동합니다.

함께 읽으면 좋은 글: 엑셀 VBA로 드롭다운 목록에 고유 값 넣기 (완벽 가이드)

꼭 기억해야 할 사항

• ComboBox에 직접 입력하려면 개발 도구 탭에서 디자인 모드를 해제해야 합니다.
• 수식에 절대 참조($ 기호)가 정확히 입력되었는지 반드시 확인하세요.
• 배열 수식이 제대로 작동하지 않을 경우 Ctrl + Shift + Enter로 입력해 보세요.

마무리

지금까지 엑셀에서 검색 가능한 드롭다운 목록을 만드는 두 가지 방법, 즉 수식 활용법과 VBA 활용법을 알아보았습니다. 추가 질문이나 제안 사항이 있다면 아래 댓글로 남겨주세요. 더 많은 엑셀 활용 팁은 ExcelDemy 블로그에서 확인할 수 있습니다. 꾸준히 학습하며 엑셀 실력을 키워보세요!

관련 글

• 엑셀에서 드롭다운 목록으로 서식(폼) 만드는 방법
• 엑셀에서 드롭다운 선택에 따라 열 숨기기/숨기기 해제하기
• 엑셀에서 드롭다운으로 항목을 선택해 다른 시트의 데이터 불러오기
• 엑셀 드롭다운 목록에서 사용한 항목 제거하기 (2가지 방법)
• 엑셀 드롭다운 목록에서 중복 항목 제거하기 (4가지 방법)