특정 값을 기준으로 원하는 데이터를 추출하려면 드롭다운 목록을 활용하는 것이 효율적입니다. 특히 두 개 이상의 종속 드롭다운 목록을 연동해야 하는 경우가 많은데요. 이 글에서는 엑셀에서 셀 값에 따라 드롭다운 목록이 자동으로 변경되도록 설정하는 방법을 소개합니다.
셀 값에 따라 드롭다운 목록을 변경하는 2가지 방법
아래에서는 가장 실용적인 두 가지 방법을 다룹니다. 첫 번째는 OFFSET 함수와 MATCH 함수를 조합하여 셀 값에 따라 목록을 동적으로 변경하는 방식이고, 두 번째는 Microsoft Excel 365에서 제공되는 XLOOKUP 함수를 활용하는 방식입니다. 아래 이미지는 작업 진행에 사용할 샘플 데이터입니다.
방법 1. OFFSET 함수와 MATCH 함수를 결합하여 드롭다운 목록 변경하기
예시 데이터에는 영업 사원 세 명과 각자 판매한 제품이 정리되어 있습니다. 이제 특정 영업 사원을 선택하면 해당 사원이 판매한 제품만 드롭다운 목록에 표시되도록 설정해 보겠습니다.
1단계: 데이터 유효성 검사 목록 만들기
- 데이터 탭으로 이동합니다.
- 데이터 유효성 검사를 클릭합니다.
2단계: 목록의 원본 범위 선택하기
- 제한 대상 옵션에서 목록을 선택합니다.
- 원본 입력란에 영업 사원 이름이 있는 범위 E4:G4를 절대 참조 형식으로 입력합니다.
- 확인(Enter)을 누릅니다.
- 그러면 B5 셀에 드롭다운 목록이 생성됩니다.
3단계: OFFSET 함수 적용하기
- 다음과 같이 OFFSET 함수의 기본 수식을 입력합니다.
=OFFSET($E$4)
- 여기서 E4는 절대 참조 형식의 기준 셀입니다.
- rows 인수에 1을 입력하면 기준 셀 E4에서 한 행 아래를 가리키게 됩니다.
=OFFSET($E$4,1
4단계: MATCH 함수로 열 위치 정의하기
- cols 인수에는 다음 수식처럼 MATCH 함수를 중첩하여 열을 선택합니다.
=OFFSET($E$4,1,MATCH($B$5
- 여기서 B5는 드롭다운 목록에서 선택된 셀 값입니다.
- MATCH 함수의 lookup_array 인수로는 E4:G4 범위를 절대 참조 형식으로 추가합니다.
=OFFSET($E$4,1,MATCH($B$5,$E$4:$G$4
- 일치 유형은 0(정확히 일치)으로 지정합니다. 이 수식은 선택된 이름의 위치인 3을 반환합니다.
MATCH($B$5,$E$4:$G$4,0)
- OFFSET 함수는 첫 번째 열을 0으로 계산하므로, MATCH 함수 결과에서 -1을 빼줍니다.
MATCH($B$5,$E$4:$G$4,0)-1
5단계: 열 높이 지정하기
- height 인수에 1을 입력하면 각 열의 값이 하나씩 있다고 계산합니다.
=OFFSET($E$4,1,MATCH($B$5,$E$4:$G$4,0)-1,1
6단계: 너비 값 입력하기
- width 인수에도 1을 입력합니다.
=OFFSET($E$4,1,MATCH($B$5,$E$4:$G$4,0)-1,1,1)
- 이제 B5에서 Jacob을 선택하면 Jacob이 판매한 첫 번째 제품인 Chocolate이 결과로 표시됩니다.
7단계: 각 열의 요소 개수 세기
- 열에 포함된 항목 수를 세려면 C13 셀에 COUNTA 함수를 다음과 같이 적용합니다.
=COUNTA(OFFSET($E$4,1,MATCH($B$5,$E$4:$G$4,0)-1,10))
- 이렇게 하면 특정 영업 사원(Jacob)의 제품 개수가 계산됩니다.
8단계: 개수 결과를 OFFSET 함수의 height 인수로 사용하기
- 다음 수식처럼 높이 자리에 C13 셀 참조를 넣어줍니다.
=OFFSET($E$4,1,MATCH($B$5,$E$4:$G$4,0)-1,C13,1)
9단계: 수식 복사하기
- Ctrl + C 키를 눌러 완성된 수식을 복사합니다.
=OFFSET($E$4,1,MATCH($B$5,$E$4:$G$4,0)-1,C13,1)
10단계: 수식 붙여넣기
- 복사한 수식을 데이터 유효성 검사의 원본 입력란에 붙여넣습니다.
=OFFSET($E$4,1,MATCH($B$5,$E$4:$G$4,0)-1,C13,1)
- 마지막으로 확인(Enter)을 누르면 변경 사항이 적용됩니다.
- 이제 드롭다운 목록의 값이 다른 셀의 값에 따라 자동으로 바뀝니다.
- 예를 들어 Bryan 대신 Juliana를 선택하면 Juliana가 판매한 제품명이 표시됩니다.
방법 2. XLOOKUP 함수로 드롭다운 목록 변경하기
Microsoft 365를 사용 중이라면 XLOOKUP 함수 하나만으로 간단하게 처리할 수 있습니다. 아래 단계를 따라 해 보세요.
1단계: 데이터 유효성 검사 목록 만들기
- 데이터 유효성 검사 옵션에서 목록을 선택합니다.
2단계: 원본 범위 입력하기
- 원본 입력란에 범위 E4:G4를 선택합니다.
- 그런 다음 확인(Enter)을 누릅니다.
- 그러면 데이터 유효성 검사 목록이 생성됩니다.
3단계: XLOOKUP 함수 삽입하기
- 조회값으로 B5 셀을 지정합니다.
=XLOOKUP(B5)
4단계: lookup_array 선택하기
- 검색 범위로 E4:G4를 입력합니다.
=XLOOKUP(B5, E4:G4)
5단계: return_array 삽입하기
- 반환할 값이 있는 범위 E5:G11을 입력합니다.
- 그러면 선택한 영업 사원에 해당하는 제품들이 반환됩니다.
- 이제 드롭다운 목록에서 아무 이름이나 선택하면 해당 사원의 제품명이 표시됩니다.
참고: 위 이미지를 잘 보면 0이 표시되어 있는데, 이는 범위 안의 일부 셀이 비어 있기 때문입니다. 빈 셀은 0으로 처리되므로, 이를 제거하려면 아래 단계를 따르세요.
6단계: UNIQUE 함수 적용하기
- 다음과 같이 UNIQUE 함수를 중첩한 수식을 입력합니다.
=UNIQUE(XLOOKUP(B5,E4:G4,E5:G11),,TRUE)
- 마지막으로 원하는 깔끔한 결과를 얻을 수 있습니다.
마무리
지금까지 엑셀에서 셀 값에 따라 드롭다운 목록을 업데이트하는 두 가지 방법을 살펴보았습니다. 배운 내용을 실제 데이터에 직접 적용해 보면서 연습하면 훨씬 빠르게 익힐 수 있습니다. 궁금한 점이 있다면 언제든지 댓글로 남겨주세요. 최대한 빠르게 답변드리겠습니다.
앞으로도 유익한 엑셀 활용 팁으로 찾아오겠습니다. 함께 꾸준히 배워 나가요!
함께 보면 좋은 글
- 엑셀 필터 드롭다운 목록 복사하는 방법 (5가지)
- VBA로 드롭다운 목록에서 값 선택하기 (2가지 방법)
- 엑셀에서 고유 값으로 드롭다운 목록 만들기 (4가지 방법)
- 엑셀 드롭다운 목록이 작동하지 않을 때 (8가지 문제와 해결책)
- 선택 항목에 따라 달라지는 엑셀 드롭다운 목록 만들기
- 엑셀 독립형·종속형 드롭다운 목록 만드는 방법
- 엑셀 드롭다운 목록 자동 업데이트하기 (3가지 방법)