Excel에서 종속 드롭다운 목록을 만드는 것은 사용자들 사이에서 널리 활용되는 기능입니다. 하지만 종속 항목 이름에 공백이 포함된 경우에는 단어 사이의 공백을 별도로 처리해야 한다는 까다로운 점이 있습니다.
예를 들어, 두 단어로 된 이름 때문에 공백이 들어간 3개의 제품 목록이 있다고 가정해 보겠습니다. 이때 필요한 것은 종속 항목에 공백이 있어도 정상적으로 작동하는 드롭다운 목록입니다. Excel의 INDEX, MATCH, INDIRECT 함수를 조합한 수식을 활용하면 참조 이름에 공백이 포함되어 있어도 문제없이 처리할 수 있습니다.
이 글에서는 공백이 포함된 Excel 종속 드롭다운 목록을 만드는 2가지 쉬운 방법을 소개합니다.
Excel 워크북 다운로드
공백이 포함된 Excel 종속 드롭다운 목록 만드는 2가지 방법
Excel의 데이터 탭에는 데이터 도구 섹션 안에 데이터 유효성 검사 기능이 있습니다. 이 기능을 사용하면 누구나 손쉽게 드롭다운 목록을 삽입할 수 있습니다. 그러나 종속 드롭다운 목록을 만들 때 종속 항목 이름에 공백이 있으면 오류가 발생합니다.
이 문제를 해결하기 위해 아래의 두 가지 방법을 활용할 수 있습니다.
방법 1: Excel 함수 조합으로 공백 허용 종속 드롭다운 목록 만들기
INDEX 함수와 MATCH 함수를 조합하면 종속 이름이나 제목에 포함된 공백을 무시하고 정상적으로 동작합니다. INDEX 함수의 구문은 다음과 같습니다.
=INDEX(array, row_num, [col_num], [area_num])
array: 셀 범위 또는 배열입니다.
row_num: 배열 내 행 위치입니다.
col_num: 배열 내 열 위치입니다. [선택]
area_num: 배열에서 사용할 범위입니다. [선택]
MATCH 함수의 구문은 다음과 같습니다.
=MATCH(lookup_value, lookup_array, [match_type])
lookup_value: lookup_array에서 찾으려는 값입니다.
lookup_array: lookup_value와 비교할 배열 또는 참조입니다.
match_type: 정확히 일치 또는 그보다 작은 값=1(기본값), 정확히 일치=0, 정확히 일치 또는 그보다 큰 값=-1입니다. [선택]
🔁 드롭다운 목록 만들기
1단계: 빈 셀(예: F4)에 커서를 놓고 데이터 탭 > 데이터 도구 섹션에서 데이터 유효성 검사를 선택합니다.
2단계: 데이터 유효성 검사 창이 열리면 설정 탭에서 다음과 같이 지정합니다.
- 제한 대상(허용)에서 목록을 선택합니다.
- 원본(Source)에 B4:D4를 입력합니다.
- 확인을 클릭합니다.
➤ 이제 워크시트에서 F4 셀의 아래 화살표 아이콘을 클릭하면 데이터 유효성 검사 창에서 원본으로 지정한 셀들이 드롭다운 목록으로 표시됩니다.
🔁 종속 드롭다운 목록 만들기
3단계: F5 셀에 대해 1~2단계를 반복한 뒤, 데이터 유효성 검사 창의 원본에 아래 수식을 입력합니다.
=INDEX(B5:D13,,MATCH($F$4,$B$4:$D$4,0))
앞서 설명한 구문과 비교해 보면, 수식 중 MATCH($F$4,$B$4:$D$4,0) 부분이 INDEX 함수에 col_num(열 번호)을 전달합니다. B5:D13은 INDEX 함수의 array이며, row_num은 별도로 지정하지 않습니다.
MATCH 부분에서는 F4가 lookup_value, B4:D4가 lookup_array이며, 0은 정확히 일치(match_type)를 의미합니다. 이후 확인을 클릭합니다.
➤ 확인을 클릭하면 워크시트로 돌아갑니다. F5 셀의 아래 화살표를 클릭해 보면, 이름에 공백이 포함되어 있어도 종속 드롭다운 목록이 문제없이 생성된 것을 확인할 수 있습니다.
더 읽어보기: Excel에서 수식 기반 드롭다운 목록 만드는 방법 (4가지)
함께 읽으면 좋은 글
- Excel에서 조건부 드롭다운 목록 만들기, 정렬 및 활용하기
- Excel에서 셀 값과 드롭다운 목록 연결하는 방법 (5가지)
- Excel에서 선택 항목에 따라 달라지는 드롭다운 목록
- Excel에서 검색 가능한 드롭다운 목록 만들기 (2가지 방법)
- Excel에서 색상이 적용된 드롭다운 목록 만들기 (2가지 방법)
방법 2: 이름 정의 및 INDIRECT 함수 활용
일반적으로 INDIRECT 함수는 종속 이름이나 참조에 공백이 있으면 드롭다운 목록을 제대로 만들지 못합니다. INDIRECT 함수가 공백을 허용하도록 하려면 SUBSTITUTE 함수를 사용해 수식을 약간 수정해야 합니다. INDIRECT 함수의 구문은 다음과 같습니다.
=INDIRECT(ref_text, [a1])
ref_text: 텍스트 형식의 참조입니다.
a1: A1 참조 스타일 여부를 나타내는 논리값입니다. 기본값은 TRUE(A1 스타일)입니다. [선택]
🔁 이름 정의하기
1단계: 열 머리글(예: B4:D4)을 선택한 후 수식 탭 > 정의된 이름 섹션에서 이름 정의를 선택합니다.
2단계: 새 이름 창이 나타나면 다음을 진행합니다.
- 해당 셀에 사용할 이름을 지정합니다(예: List).
- Excel이 참조 대상 상자에 셀 참조를 자동으로 입력합니다.
- 확인을 클릭합니다.
3단계: 이 방법의 1~2단계를 반복하여 서로 다른 목록의 이름과 범위를 차례로 지정합니다.
➤ 지정한 모든 이름은 수식 탭 > 이름 관리자(정의된 이름 섹션)에서 언제든 확인할 수 있습니다.
🔁 드롭다운 목록 만들기
4단계: F4 셀에 커서를 놓고 데이터 탭 > 데이터 도구 섹션에서 데이터 유효성 검사를 선택합니다.
5단계: 데이터 유효성 검사 대화 상자에서 다음을 지정합니다.
- 제한 대상에서 목록을 선택합니다.
- 원본 상자에 =List(List는 열 머리글에 정의된 이름)를 입력합니다.
- 확인을 클릭합니다.
➤ F4 셀의 아래 화살표를 클릭하면 아래 이미지처럼 열 머리글이 드롭다운 목록으로 표시됩니다.
🔁 종속 드롭다운 목록 만들기
6단계: 4~5단계를 실행해 데이터 유효성 검사 창을 연 뒤, 원본에 아래 수식을 붙여넣습니다.
=INDIRECT(SUBSTITUTE($F$4," ","_"))
이 수식에서 SUBSTITUTE 함수는 F4 참조에 포함된 공백을 밑줄(_)로 대체합니다. 정의된 이름과 INDIRECT 수식에서 공백 대신 다른 문자를 사용할 수도 있습니다. 결과적으로 INDIRECT 함수는 F4의 값을 정의된 이름과 동일한 형태로 변환하며, F4 값에 따라 해당 제품 목록을 표시합니다.
➤ 확인을 클릭하면 아래 스크린샷처럼 종속 목록이 나타납니다.
첫 번째 드롭다운 목록에서 종속 이름 항목을 변경하면 두 번째 드롭다운 목록의 항목도 자동으로 변경됩니다.
더 읽어보기: Excel에서 드롭다운 목록 만드는 방법 (독립형 및 종속형)
결론
이 글에서는 공백이 포함된 Excel 종속 드롭다운 목록을 삽입하는 수식을 살펴보았습니다. INDEX와 MATCH 함수를 조합한 수식은 종속 이름이나 제목에 포함된 공백을 기본적으로 처리할 수 있습니다. 반면 INDIRECT 함수는 SUBSTITUTE 함수 등을 활용해 수정해야만 공백이 있는 종속 항목을 지원합니다. 위 방법들이 실무에 도움이 되기를 바랍니다. 추가 문의 사항이나 덧붙일 내용이 있다면 댓글로 알려주세요.
관련 글
- Excel에서 VLOOKUP과 드롭다운 목록 함께 활용하기
- Excel에서 드롭다운 목록 편집하는 방법 (4가지 기본 방법)
- Excel에서 다른 시트 데이터로 드롭다운 목록 만들기 (2가지 방법)
- Excel에서 드롭다운 목록 삭제하는 방법
- IF 문으로 Excel 드롭다운 목록 만드는 방법
- Excel 드롭다운 목록이 작동하지 않을 때 (8가지 문제와 해결책)