다른 워크시트나 같은 워크시트에서 특정 값을 찾아야 할 때 가장 먼저 떠오르는 함수가 바로 VLOOKUP 함수입니다. 하지만 VLOOKUP 함수의 가장 큰 단점은 한 번에 하나의 값만 반환하도록 설계되어 있다는 점입니다. 그래서 이 함수로 여러 개의 값을 조회하려면 조금 다른 접근이 필요합니다. 이번 글에서는 그 방법들을 하나씩 소개해 드리겠습니다.
VLOOKUP 외에도 INDEX, SMALL, MATCH, ROW, COLUMNS 등의 엑셀 함수를 적절히 조합하면 여러 값을 조회하고 반환할 수 있습니다. 또한 고급 필터(Advanced Filter), 자동 필터(AutoFilter), 표 서식(Format as Table) 같은 엑셀 기능을 활용하는 방법도 있습니다.
이 튜토리얼에서는 VLOOKUP 함수를 활용한 1가지 방법, 다른 엑셀 함수를 조합한 4가지 방법, 엑셀의 다양한 도구와 옵션을 활용한 3가지 방법, 총 8가지 방법으로 여러 값을 손쉽게 조회하고 반환하는 방법을 알아보겠습니다.
아래 첨부된 엑셀 파일을 내려받아 직접 따라 해 보시는 것을 추천합니다.
엑셀에서 VLOOKUP으로 여러 값 반환하는 8가지 방법
이 글 전체에서는 아래와 같은 직원 데이터베이스(Employee Database) 예시를 사용하여 8가지 방법을 모두 시연하겠습니다.
그럼 지금부터 8가지 방법을 하나씩 살펴보겠습니다.
1. VLOOKUP 함수만으로 여러 값 반환하기
VLOOKUP 함수는 한 번에 하나의 값만 반환한다는 것을 우리는 알고 있습니다. 하지만 굳이 VLOOKUP 함수를 사용하고 싶다면 포기할 필요가 없습니다. 데이터셋을 약간 변형하면 가능한 방법이 있기 때문입니다. 이 방법에서 사용할 함수는 COUNTIF와 VLOOKUP입니다. 아래 단계를 따라 해 보세요.
🔗 실행 단계:
먼저 모든 부서 이름을 고유하게 만들어야 합니다. 그 방법은 다음과 같습니다.
❶ E5 셀을 선택합니다.
❷ 해당 셀에 아래 수식을 입력합니다.=B5&COUNTIF(B5:B$13,B5)
❸ Enter 키를 누릅니다.
❹ 채우기 핸들(Fill Handle)을 아래로 드래그하여 Dept._Unique 열 전체에 수식을 적용합니다.
❺ 이제 C16 셀을 선택하고 아래 수식을 입력합니다.=VLOOKUP(B16,E5:F13,2,0)
❻ Enter 키를 누릅니다.
❼ 마지막으로 채우기 핸들을 Employee(직원) 열 끝까지 드래그하면 완성됩니다.
이것으로 끝입니다!
2. VLOOKUP 결과를 세로 방향으로 여러 값 불러오기
Engineering(엔지니어링) 부서에 근무하는 직원 수를 구하고, 그 이름들을 열 방향(세로)으로 정렬하고 싶다고 가정해 봅시다. 이 경우 아래 단계를 따라 하세요. 이 방법에서는 INDEX, SMALL, MATCH, ROW 함수를 사용합니다.
🔗 실행 단계:
❶ 먼저 수식 결과를 저장할 F5 셀을 선택합니다.
❷ 해당 셀에 아래 수식을 입력합니다.=INDEX($C$5:$C$13, SMALL(IF(($E$5=$B$5:$B$13), MATCH(ROW($B$5:$B$13), ROW($B$5:$B$13)), ""), ROWS($A$1:A1)))
❸ Enter 키를 누릅니다.
❹ 이제 채우기 핸들을 Employee 열 아래로 드래그합니다.
␥ 수식 분석
- ROW($B$5:$B$13) ▶ 행 번호를 배열 형태로 반환합니다: {5;6;7;8;9;10;11;12;13}
- MATCH(ROW($B$5:$B$13), ROW($B$5:$B$13)) ▶ 앞의 배열을 다음과 같이 변환합니다: {1;2;3;4;5;6;7;8;9}
- IF(($C$15=$B$5:$B$13), MATCH(ROW($B$5:$B$13), ROW($B$5:$B$13)), "") ▶ 조건에 맞는 행 번호는 반환하고, 그렇지 않으면 빈 값을 반환합니다: {"";2;"";4;"";"";"";"";9}
- SMALL(IF(($E$5=$B$5:$B$13), MATCH(ROW($B$5:$B$13), ROW($B$5:$B$13)), "") ▶ 배열에서 k번째로 작은 숫자를 반환합니다: {"";2;"";4;"";"";"";"";9}
- INDEX($C$5:$C$13, SMALL(...)) ▶ SMALL이 반환한 행 인덱스 번호를 기준으로 직원 이름을 반환합니다.
3. VLOOKUP 결과를 가로 방향으로 여러 값 반환하기
이번에는 Engineering 부서에 근무하는 직원 이름들을 행 방향(가로)으로 정렬해 보겠습니다. 이 방법에서는 INDEX, SMALL, MIN, ROW 함수를 사용합니다.
🔗 실행 단계:
❶ 먼저 수식 결과를 저장할 C16 셀을 선택합니다.
❷ 해당 셀에 아래 수식을 입력합니다.=INDEX($C$5:$C$13, SMALL(IF($C$15=$B$5:$B$13, ROW($B$5:$B$13)-MIN(ROW($B$5:$B$13))+1, ""), COLUMNS($A$1:A1)))
❸ Enter 키를 누릅니다.
❹ 이제 채우기 핸들을 Employee 행 오른쪽 방향으로 드래그합니다.
␥ 수식 분석
- ROW($B$5:$B$13) ▶ 행 번호를 배열 형태로 반환합니다: {5;6;7;8;9;10;11;12;13}
- SMALL(IF($C$15=$B$5:$B$13, ROW($B$5:$B$13)-MIN(ROW($B$5:$B$13))+1, "") ▶ 배열에서 k번째로 작은 숫자를 반환합니다.
- INDEX($C$5:$C$13, SMALL(...)) ▶ SMALL이 반환한 행 인덱스 번호를 기준으로 직원 이름을 반환합니다.
4. 복수 조건으로 VLOOKUP하여 여러 값 가져오기
이번에는 Engineering 부서에 속하면서 동시에 Morning(오전) 교대 근무를 하는 직원 이름을 모두 반환해 보겠습니다. 이 방법에서는 INDEX, SMALL, IFERROR, ROW 함수를 사용합니다.
🔗 실행 단계:
❶ 먼저 수식 결과를 저장할 H5 셀을 선택합니다.
❷ 해당 셀에 아래 수식을 입력합니다.=IFERROR(INDEX($C$5:$C$13,SMALL(IF(1=((--($F$5=$B$5:$B$13)) *(--($G$5=$D$5:$D$13))), ROW($C$5:$C$13)-4,""), ROW()-4)),"")
❸ Enter 키를 누릅니다.
❹ 이제 채우기 핸들을 Employee 열 아래로 드래그합니다.
␥ 수식 분석
이 수식은 앞의 두 수식과 상당히 유사합니다. 다른 점은 다음과 같습니다.
- ROW($C$5:$C$13)-4 ▶ 배열 내 행 번호를 반환합니다: {5;6;7;8;9;10;11;12;13}. 여기서 숫자 4는 열 제목 Employee가 위치한 행 번호를 의미합니다.
- ROW()-4 ▶ 여기서 숫자 4는 출력 행이 시작되기 바로 전 행의 번호를 의미합니다.
- IFERROR ▶ 오류가 발생할 경우 사용자가 지정한 출력값을 대신 표시하기 위해 사용됩니다.
5. VLOOKUP으로 여러 값을 한 셀에 모두 추출하기
이제 Engineering 부서에 근무하는 모든 직원 이름을 하나의 셀에 모아 보겠습니다. 이 방법에서는 TEXTJOIN과 IF 함수를 사용합니다.
🔗 실행 단계:
❶ 먼저 수식 결과를 저장할 H5 셀을 선택합니다.
❷ 해당 셀에 아래 수식을 입력합니다.=TEXTJOIN(",",TRUE,IF($B$5:$B$13=$C$15,$C$5:$C$13,""))
❸ Enter 키를 누르면 완성됩니다.
␥ 수식 분석
여기서 IF 함수는 조건에 맞는 모든 직원 이름을 반환하고, TEXTJOIN 함수는 IF 함수가 반환한 직원 이름들을 하나로 연결해 줍니다.
더 읽어보기: 엑셀에서 한 셀에 VLOOKUP 여러 값 반환하는 방법 (2가지 쉬운 방법)
6. 자동 필터(AutoFilter)로 VLOOKUP하여 여러 값 반환하기
엑셀 수식을 사용하지 않고 싶다면 자동 필터 기능을 활용해 데이터 테이블을 조회하고 여러 값을 추출할 수 있습니다. 여기서는 Engineering 부서에 근무하는 모든 직원 이름을 걸러내 보겠습니다.
🔗 실행 단계:
❶ 먼저 전체 데이터 테이블을 선택합니다.
❷ 데이터(Data) 리본 메뉴로 이동합니다.
❸ 필터(Filter) 옵션을 클릭합니다.
❹ Department(부서) 열 제목 바로 아래의 필터 아이콘을 클릭합니다.
❺ 팝업 메뉴에서 Engineering 옵션에 체크 표시를 합니다.
❻ 확인(OK) 버튼을 클릭합니다.
완성되었습니다! 결과는 아래와 같이 나타납니다.
7. 고급 필터(Advanced Filter)로 VLOOKUP하여 여러 값 추출하기
엑셀에는 고급 필터라는 기능이 있는데, 이를 활용하면 데이터 테이블을 세로로 조회하면서 여러 값을 한꺼번에 추출할 수 있습니다. 다음 예시에서는 Department 열 제목 아래에서 Engineering 부서를 선택했으며, 이를 검색 기준으로 사용해 데이터 테이블 전체에서 Engineering 부서에 근무하는 직원을 찾겠습니다. 단계는 다음과 같습니다.
🔗 실행 단계:
❶ 먼저 전체 데이터 테이블을 선택합니다.
❷ 데이터(Data) 리본 메뉴로 이동합니다.
❸ 고급(Advanced) 옵션을 클릭합니다.
❹ 마지막 단계를 완료하면 고급 필터(Advanced Filter) 대화상자가 화면에 나타납니다.
❺ 목록 범위(List range): 입력란에는 $B$4:$C$13을, 조건 범위(Criteria range): 입력란에는 'Advanced Filter'!$E$4:$E$5를 입력합니다.
❻ 확인(OK) 버튼을 클릭합니다.
모든 단계를 완료하면 아래와 같은 결과를 얻을 수 있습니다.
8. 표 서식(Format as Table)으로 VLOOKUP하여 여러 값 가져오기
이 방법은 자동 필터와 고급 필터의 대안으로 활용할 수 있습니다. 이 방법에서는 표 서식 옵션을 사용하여 Engineering 부서에 해당하는 모든 직원 이름을 정렬해 보겠습니다. 단계는 다음과 같습니다.
🔗 실행 단계:
❶ 먼저 전체 데이터 테이블을 선택합니다.
❷ 홈(Home) 리본 메뉴로 이동합니다.
❸ 표 서식(Format as a Table) 옵션을 선택합니다.
❹ 원하는 표 스타일을 선택합니다.
이제 데이터 테이블이 아래 이미지와 비슷하게 표시됩니다.
❺ 이 단계에서 Department 열 제목 아래의 필터 아이콘을 클릭합니다.
❻ 팝업 메뉴에서 Engineering 옵션에만 체크 표시를 합니다.
❼ 확인(OK) 버튼을 클릭합니다.
모든 단계를 완료하면 결과가 아래와 같이 나타납니다.
알아두어야 할 사항
📌 VLOOKUP 함수는 단독으로 두 개 이상의 값을 반환할 수 없습니다.
📌 함수의 구문(syntax)을 정확히 확인하세요.
📌 수식에 데이터 범위를 신중하게 입력해야 합니다.
마치며
지금까지 엑셀에서 VLOOKUP으로 여러 값을 조회하고 반환하는 8가지 방법을 살펴보았습니다. 이 글에 첨부된 실습용 통합 문서를 내려받아 모든 방법을 직접 연습해 보시기를 권장합니다. 궁금한 점이 있다면 아래 댓글로 언제든지 질문해 주세요. 관련 질문에 최대한 빠르게 답변드리겠습니다.
관련 글
- 엑셀에서 여러 행에 VLOOKUP 수행하는 방법 (5가지 방법)