엑셀 검색형 드롭다운 만들기 — 데이터 유효성 + 동적 배열 완전 가이드
환경: Excel 365 기준(동적 배열). 표(Table) 사용을 권장합니다. 초안 정리에 AI 도구를 보조적으로 활용했지만 모든 수식은 직접 재현·검증했습니다.
연계 가이드(내부 링크 7개)
1) 개요 & 선택 가이드
- 빠르게 한 셀에서 검색·선택을 하고 싶다 → 단일 목록 검색형
- 카테고리 선택 후 상품만 고르고 싶다 → 2단 종속 검색형
- Excel 2016/2019 등 동적 배열이 없다 → 구버전 대안
핵심은 검색어 셀과 스필 리스트를 만든 뒤, 데이터 유효성의 ‘원본’을 =스필셀#로 연결하는 것입니다.
2) 단일 목록 검색형 드롭다운
2-1. 표 만들기
품목 범위를 선택하고 Ctrl+T → 표 이름 ItemsTbl, 열 이름 Item.
2-2. 검색어 입력칸(H2)
H2에 검색어를 입력하면, 일치 항목만 스필 목록에 표시됩니다. 비워두면 전체 표시.
2-3. 스필 목록 수식(A2)
=IFERROR(
SORT(UNIQUE(
FILTER(ItemsTbl[Item],
IF($H$2="",
TRUE,
ISNUMBER(SEARCH($H$2, ItemsTbl[Item]))
)
)
)),
"(일치 없음)")2-4. 데이터 유효성 연결
- 드롭다운 대상 셀 선택
- 데이터 ▸ 데이터 유효성 ▸ 목록
- 원본에
=A2#입력 → 완료
TIP 입력 메시지 탭에 “검색어를 H2에 입력하세요”를 적어 UX를 높이세요.
3) 2단 종속(카테고리→상품) 검색형
ProductsTbl에 열 Category, Product가 있다고 가정.
3-1. 1단계(카테고리) 드롭다운
// D2: 카테고리 목록(고유 정렬)
=SORT(UNIQUE(ProductsTbl[Category]))데이터 유효성(목록) 원본에 =D2#.
3-2. 2단계(상품) 검색형 + 종속
// 검색어(H3), 선택된 카테고리(C3)
=IFERROR(
SORT(UNIQUE(
FILTER(ProductsTbl[Product],
(ProductsTbl[Category]=$C$3) *
IF($H$3="", TRUE, ISNUMBER(SEARCH($H$3, ProductsTbl[Product])))
)
)),
"(일치 없음)")생성된 스필을 데이터 유효성(목록)의 원본으로 =E2#처럼 연결하세요.
4) 구버전(동적 배열 없음) 대안
- 도움말 열에
=IF(ISNUMBER(SEARCH($H$2,$A2)), $A2, "")로 후보를 추출 - 다른 범위에
=IFERROR(INDEX($A:$A,SMALL(IF($B:$B<>"",ROW($A:$A)),ROW(1:1))),"")(배열 수식)로 연속 리스트 구성 - 데이터 유효성 원본에 위 연속 리스트 범위를 지정
가능하면 365로 업그레이드하여 스필 기반 구성을 권장합니다.
5) 오류·한글 검색 팁
- #SPILL! — 스필 범위에 값/병합 셀/그림이 있는지 확인
- 대소문자 이슈 →
SEARCH는 대소문자 무시,FIND는 구분 - 한글 자모·특수공백 →
TRIM(SUBSTITUTE(text,CHAR(160), " "))전처리 - 속도 저하 → 표 범위를 최소화, 필요하면 Power Query로 사전 정제
6) 템플릿 & 재방문 과제
과제 A — 최근 선택값 ‘자동 완성’ 힌트 표시
=IFERROR(XLOOKUP($B2, $B$1:B1, $B$1:B1, ""), "")과제 B — 선택 결과에 따라 가격/재고 자동 채우기
=XLOOKUP($B2, ProductsTbl[Product], CHOOSECOLS(ProductsTbl, XMATCH({"Price","Stock"}, ProductsTbl[#Headers])))이 글로 검색형을 만들었다면 다음 글 “LEFT/RIGHT/MID × FIND/SEARCH 심화”로 이어가면 텍스트 파이프라인을 완성할 수 있어요.