값 하나만 입력하면 나머지 셀이 자동으로 채워진다면 얼마나 편리할까요? 대부분의 사용자라면 손쉬운 자동화를 반길 것입니다. 이 글에서는 다른 셀의 값을 기준으로 엑셀에서 셀을 자동으로 채우는 다양한 방법을 소개합니다. 예제는 Excel 2019를 기준으로 작성되었지만, 사용 중인 버전에 맞게 그대로 적용할 수 있습니다.
먼저 오늘 예제의 기반이 되는 데이터셋부터 살펴보겠습니다.
여기에는 직원 이름, 사번(ID), 주소, 소속 부서, 입사일 등 직원 정보가 담긴 표가 준비되어 있습니다. 이 데이터를 활용해 셀을 자동으로 채우는 방법을 하나씩 알아보겠습니다.
참고로 이 데이터셋은 더미 데이터로 구성된 기본적인 예제입니다. 실무에서는 훨씬 방대하고 복잡한 데이터를 다루게 될 수 있습니다.
연습용 워크북
아래 링크에서 연습용 워크북을 내려받아 함께 따라 해 보세요.
다른 셀 값을 기준으로 셀 자동 채우기
이번 예제는 직원 이름을 입력하면 해당 직원의 정보가 자동으로 조회되도록 구성했습니다.
원본 표와 분리된 정보 입력 필드를 만들었습니다. 여기에 이름을 'Robert'로 입력해 보겠습니다.
그러면 Robert의 상세 정보가 자동으로 표시되어야 합니다. 어떻게 구현할 수 있는지 살펴보겠습니다.
1. VLOOKUP 함수 활용
잠시 '자동 채우기'라는 개념은 잊고, 조건에 일치하는 데이터를 조회하는 함수를 떠올려 볼까요? 가장 먼저 생각나는 함수 중 하나가 바로 VLOOKUP일 것입니다.
VLOOKUP은 세로(열) 방향으로 정렬된 데이터를 찾는 함수입니다. 자세한 내용은 VLOOKUP 관련 글을 참고하세요.
이제 원하는 데이터를 셀에 불러오는 VLOOKUP 수식을 작성해 보겠습니다. 직원의 사번(ID)을 가져오는 수식은 다음과 같습니다.
=IFERROR(VLOOKUP($I$4,$B$4:$F$9,2,0),"")
VLOOKUP 함수 안에서 이름이 있는 셀(I4)을 lookup_value(찾을 값)로 지정하고, 전체 표 범위를 lookup_array(찾을 범위)로 지정했습니다.
사번(Employee ID)은 표에서 두 번째 열에 있으므로 column_num(열 번호)은 2로 설정합니다.
VLOOKUP 수식을 IFERROR 함수로 감싸면 수식 실행 중 발생할 수 있는 오류를 깔끔하게 처리할 수 있습니다(IFERROR 함수에 대한 자세한 내용은 관련 문서를 참고하세요).
부서명을 가져오려면 수식을 다음과 같이 수정합니다.
=IFERROR(VLOOKUP($I$4,$B$4:$F$9,3,0),"")
원본 표에서 부서(Department)가 세 번째 열에 위치하므로 column_num을 3으로 변경했습니다.
입사일과 주소를 가져오는 수식은 각각 다음과 같습니다.
=IFERROR(VLOOKUP($I$4,$B$4:$F$9,4,0),"")
그리고
=IFERROR(VLOOKUP($I$4,$B$4:$F$9,5,0),"")
직원의 모든 정보를 성공적으로 조회했습니다. 이제 이름만 바꾸면 나머지 셀들이 자동으로 업데이트됩니다.
VLOOKUP + 드롭다운 목록
앞서는 이름을 직접 입력했지만, 매번 타이핑하다 보면 시간이 걸리고 오타로 인한 혼란도 생길 수 있습니다.
이 문제는 직원 이름 드롭다운 목록을 만들어 해결할 수 있습니다. 드롭다운 목록을 만드는 방법은 관련 글을 참고하세요.
데이터 유효성 검사(Data Validation) 대화상자에서 목록(List)을 선택하고 이름이 있는 셀 참조를 입력합니다.
이름이 들어 있는 범위는 B4:B9입니다.
이제 드롭다운 목록이 완성되었습니다.
이제 이름을 훨씬 빠르고 정확하게 선택할 수 있습니다.
VLOOKUP 수식 덕분에 이름을 선택하는 즉시 다른 셀들이 자동으로 채워집니다.
2. INDEX-MATCH 함수 조합 활용
VLOOKUP으로 수행한 작업은 다른 방식으로도 구현할 수 있습니다. 바로 INDEX-MATCH 조합입니다.
MATCH는 행, 열 또는 표에서 찾을 값의 위치를 반환하고, INDEX는 지정한 범위에서 해당 위치의 값을 반환합니다. 자세한 내용은 INDEX, MATCH 관련 글을 참고하세요.
수식은 다음과 같습니다.
=IFERROR(INDEX($C$4:$C$9,MATCH($I$4,$B$4:$B$9,0)),"")
INDEX 함수에 사번 범위를 넣었고, MATCH 함수가 표(B4:B9)에서 조건 값과 일치하는 행 번호를 찾아주므로 결과적으로 사번이 조회됩니다.
부서를 조회하려면 INDEX의 범위만 변경하면 됩니다.
=IFERROR(INDEX($D$4:$D$9,MATCH($I$4,$B$4:$B$9,0)),"")
부서는 D4:D9 범위에 있습니다.
입사일을 조회하는 수식은 다음과 같습니다.
=IFERROR(INDEX($E$4:$E$9,MATCH($I$4,$B$4:$B$9,0)),"")
주소는 다음과 같습니다.
=IFERROR(INDEX($F$4:$F$9,MATCH($I$4,$B$4:$B$9,0)),"")
동작을 확인하기 위해 선택을 지운 후 다른 이름을 선택해 보세요.
나머지 셀들이 자동으로 채워지는 것을 확인할 수 있습니다.
3. HLOOKUP 함수 활용
데이터가 가로(행) 방향으로 배치되어 있다면 HLOOKUP 함수를 사용해야 합니다. 자세한 내용은 HLOOKUP 관련 글을 참고하세요.
이름 필드는 드롭다운 목록에서 선택하고, 나머지 필드는 자동으로 채워집니다.
사번을 조회하는 수식은 다음과 같습니다.
=IFERROR(HLOOKUP($C$11,$C$3:$H$7,2,0),"")
동작 방식은 VLOOKUP과 유사합니다. HLOOKUP 함수에 이름을 lookup_value로, 표를 lookup_array로 지정했습니다. 사번이 2번째 행에 있으므로 row_num은 2이고, 정확히 일치하는 값을 찾기 위해 마지막 인수로 0을 사용합니다.
부서를 조회하는 수식은 다음과 같습니다.
=IFERROR(HLOOKUP($C$11,$C$3:$H$7,3,0),"")
부서가 3번째 행에 있으므로 row_num은 3입니다.
입사일을 조회하는 수식은 다음과 같습니다.
=IFERROR(HLOOKUP($C$11,$C$3:$H$7,4,0),"")
입사일이 4번째 행에 있으므로 row_num은 4입니다. 주소는 행 번호를 5로 변경하면 됩니다.
=IFERROR(HLOOKUP($C$11,$C$3:$H$7,5,0),"")
이제 셀 내용을 지우고 드롭다운 목록에서 이름을 선택해 보세요.
이름을 선택하면 다른 셀들이 자동으로 채워지는 것을 확인할 수 있습니다.
4. 행 방향 INDEX-MATCH 활용
행 방향으로 배치된 데이터에도 INDEX-MATCH 조합을 사용할 수 있습니다. 수식은 다음과 같습니다.
=IFERROR(INDEX($C$4:$H$4,MATCH($C$11,$C$3:$H$3,0)),"")
사번을 조회하는 수식이므로 INDEX 함수에 사번(Employee ID) 행인 C4:H4를 지정했습니다.
부서를 찾으려면 행 범위를 변경합니다.
=IFERROR(INDEX($C$5:$H$5,MATCH($C$11,$C$3:$H$3,0)),"")
같은 방식으로 입사일과 주소의 행 범위를 변경합니다.
=IFERROR(INDEX($C$6:$H$6,MATCH($C$11,$C$3:$H$3,0)),"")
여기서 C6:H6은 입사일 행입니다.
C7:H7은 주소 행이므로 주소를 조회하는 수식은 다음과 같습니다.
=IFERROR(INDEX($C$7:$H$7,MATCH($C$11,$C$3:$H$3,0)),"")
마무리
지금까지 다른 셀의 값을 기준으로 셀을 자동으로 채우는 여러 가지 방법을 살펴보았습니다. VLOOKUP, INDEX-MATCH, HLOOKUP 등 상황에 맞는 함수를 선택하면 데이터 조회 작업을 훨씬 효율적으로 처리할 수 있습니다. 도움이 되었기를 바라며, 이해하기 어려운 부분이 있다면 언제든 댓글로 질문해 주세요. 여기서 다루지 않은 다른 방법이 있다면 함께 공유해 주세요.
함께 읽으면 좋은 글
- 엑셀에서 자동 채우기 수식 사용하는 방법 (6가지)
- 엑셀에서 다른 셀 기준으로 셀 자동 채우기 (5가지 방법)
- 엑셀에서 자동 번호 매기기 (9가지 접근법)
- 엑셀에서 숫자 자동 채우기 (12가지 방법)
- 해결 방법: 엑셀 자동 채우기가 작동하지 않을 때 (7가지 문제)
- 엑셀에서 여러 시트에 걸쳐 연속 날짜 입력하는 방법