다른 워크시트나 같은 워크시트에서 값을 가져와야 할 때마다 VLOOKUP 함수가 생각나지 않을 수 없습니다. 엑셀에서. 그러나 VLOOKUP의 주요 문제는 기능은 단일 값을 한 번만 반환하도록 설계되었다는 것입니다. 따라서 이 기사에서 설명할 이 함수를 사용하여 여러 값을 vlookup하려면 상자에서 조금 벗어나 생각해야 합니다.
INDEX와 같은 다른 Excel 기능도 사용할 수 있습니다. , 작은 , 매치 , 행 , 열 등을 사용하여 vlookup에 적절하게 결합하고 여러 값을 반환합니다. 고급 필터와 같은 여러 Excel 기능을 사용할 수도 있습니다. , 자동 필터 및 표 형식 도구. 따라서 이 튜토리얼에서는 총 1 VLOOKUP 함수를 사용하는 방법 , 4 다른 Excel 기능을 사용하는 방법 및 3 다양한 Excel 도구 및 옵션을 사용하여 Excel에서 여러 값을 쉽게 검색하고 반환하는 메서드입니다.
엑셀 파일을 다운받아 함께 연습하시기를 권장합니다.
엑셀에서 VLOOKUP 및 여러 값을 반환하는 8가지 방법
이 자습서에서는 직원 데이터베이스를 사용합니다. 기사 전체에 걸쳐 8가지 방법을 모두 보여줍니다.
그럼 더 이상의 논의 없이 8가지 방법을 하나하나 살펴보도록 하겠습니다.
1. Excel에서 VLOOKUP 함수를 사용하여 여러 값 반환
VLOOKUP 함수는 한 번에 하나의 값만 반환할 수 있습니다. 하지만 여러 값을 반환해야 합니다. 예, 그렇게 할 수 있는 다른 옵션이 있습니다. 그러나 특히 VLOOKUP을 사용하려는 경우 기능, 희망을 잃지 마십시오. 탈출구가 있습니다. 이 섹션에서는 VLOOKUP을 사용하는 방법을 배웁니다. 데이터 세트를 약간 조정하여 여러 값을 반환하는 함수입니다. 이 방법에서 사용할 함수는 COUNTIF 입니다. 및 VLOOKUP . 이제 아래 단계를 따르십시오.
🔗 단계:
먼저 모든 부서 이름을 고유하게 만들어야 합니다. 그렇게 하려면
❶ 선택 셀 E5 .
❷ 이제 입력하세요. 공식
=B5&COUNTIF(B5:B$13,B5) 세포 내.
❸ 그 후 ENTER를 누르십시오. 버튼.
❹ 채우기 핸들 드래그 Dept._Unique 아래로 아이콘 칼럼.
❺ 이제 C16 셀을 선택합니다. 및 입력 공식
=VLOOKUP(B16,E5:F13,2,0) 그것으로.
❻ 그런 다음 ENTER 키를 누릅니다. 버튼.
❼ 마지막으로 채우기 핸들 직원 끝에 있는 아이콘 칼럼.
그게 다야.
2. Excel에서 Vlookup 및 세로로 여러 값 가져오기
엔지니어링 아래에서 일하는 직원 수를 알고 싶다고 가정해 보겠습니다. 부서 및 해당 이름을 세로로 세로로 구성합니다. 그렇다면 아래 단계에 따라 수행 방법을 알아보세요. 이 방법에서 사용할 함수는 INDEX입니다. , 작은 , 매치 , 및 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를 누릅니다. 버튼.
❹ 이제 채우기 핸들을 드래그합니다. 직원에서 아래로 아이콘 칼럼.
␥ 수식 분석
- 행($B$5:$B$13) ▶ 배열 형식으로 행 번호를 반환합니다. {5;6;7;8;9;10;11;12;13}
- 매치(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)), "") ▶ 조건과 일치하는 행 번호를 반환하고, 그렇지 않으면 null을 반환합니다. {"";2;"";4;"";"";"";"";9}
- SMALL(IF(($E$5=$B$5:$B$13), MATCH(ROW($B$5:$B$13), ROW($B$5:$B$13)), " ”) ▶ 배열 내의 첫 번째 작은 숫자를 반환합니다. {“”;2;””;4;””;””;””;””;9}
- INDEX($C$5:$C$13, SMALL(IF(($E$5=$B$5:$B$13)),MATCH(ROW($B$5:$B$13), ROW($ B$5:$B$13)), "") ▶ SMALL.에서 반환된 행 인덱스 번호를 기반으로 직원 이름을 반환합니다.
3. Excel에서 Vlookup 및 여러 값을 가로로 반환
예를 들어 이제 엔지니어링 부서 및 해당 이름을 가로로 행으로 구성합니다. 이 방법에서 사용할 함수는 INDEX입니다. , 작은 , 최소 , 및 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를 누릅니다. 버튼.
❹ 이제 채우기 핸들을 드래그합니다. 직원 오른쪽에 있는 아이콘 행.
␥ 수식 분석
- 행($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, " ”) ▶ 배열 내의 첫 번째 작은 숫자를 반환합니다.
- 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, “”) ▶ SMALL.에서 반환된 행 인덱스 번호를 기반으로 직원 이름을 반환합니다.
4. 기준을 사용하여 Excel에서 Vlookup 및 여러 값 가져오기
이 섹션에서는 Employee의 모든 직원 이름을 반환합니다. 엔지니어링 아래에서 일하는 칼럼 부서 및 오전에도 근무 옮기다. 이 방법에서 사용할 함수는 INDEX입니다. , 작은 , 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를 누릅니다. 버튼.
❹ 이제 채우기 핸들을 드래그합니다. 직원에서 아래로 아이콘 칼럼.
␥ 수식 분석
이 공식은 앞의 두 공식과 매우 유사합니다. 다음은 예외입니다:
- 행($C$5:$C$13)-4 ▶ 배열 내의 행 번호를 반환합니다:{5;6;7;8;9;10;11;12;13}. 여기에서 숫자 4는 열 헤더 Employee 가 있는 행 번호를 나타냅니다. 거주.
- ROW()-4 ▶ 여기서 숫자 4는 출력 행이 시작되기 전의 이전 행 번호를 나타냅니다.
- IFERROR ▶ 여기 IFERROR 함수는 오류가 발생할 경우 사용자 정의 출력을 합성하는 데 사용됩니다.
5. Excel에서 Vlookup 및 여러 값 추출(하나의 셀에 모두 포함)
이제 우리는 엔지니어링 부서에서 일하는 동일한 셀 내에서 모든 직원 이름을 모을 것입니다. 이 방법에서 사용할 함수는 TEXTJOIN 입니다. 및 IF .
🔗 단계:
❶ 우선 선택 셀 H5 ▶ 수식 결과를 저장합니다.
❷ 그 후 입력 공식
=TEXTJOIN(",",TRUE,IF($B$5:$B$13=$C$15,$C$5:$C$13,"")) 세포 내.
❸ 그런 다음 ENTER를 누릅니다. 버튼.
그게 다야.
␥ 수식 분석
여기에서 IF 함수는 조건 및 TEXTJOIN 과 일치하는 모든 직원 이름을 반환합니다. 함수는 IF 에 의해 반환된 모든 직원 이름을 결합합니다. 기능.
자세히 알아보기: Excel의 한 셀에 있는 여러 값을 VLOOKUP하는 방법(2가지 쉬운 방법)
6. 자동 필터를 사용하여 Excel에서 여러 값을 반환하는 Vlookup
Excel 수식을 사용하지 않으려면 데이터 테이블을 통해 vlookup하고 자동 필터 기능을 사용하여 여러 값을 가져올 수 있습니다. 여기에서는 엔지니어링 부서에서 일하는 모든 직원 이름을 정렬하려고 합니다.
🔗 단계:
❶ 우선 선택 전체 데이터 테이블.
❷ 그런 다음 데이터 리본.
❸ 그런 다음 필터를 클릭합니다. 옵션.
❹ 이제 부서 바로 아래에 있는 필터 아이콘을 클릭하십시오. 열 머리글.
❺ 이제 체크하세요. 엔지니어링에 표시 팝업 메뉴에서 옵션을 선택하십시오.
❻ 그런 다음 확인을 누르세요. 버튼.
브라보! 당신은 그것으로 끝났습니다. 이제 다음과 같은 결과가 나타납니다.
7. 고급 필터를 사용하여 Excel에서 Vlookup 및 여러 값 추출
Excel에는 고급 필터라는 기능이 있어 데이터 테이블을 세로로 조회하고 동시에 여러 값을 추출하는 데 사용할 수 있습니다. 다음 예에서는 Department 열 머리글 아래에서 Engineering 부서를 선택했습니다. 엔지니어링 부서에서 일하는 직원을 찾기 위해 데이터 테이블 전체를 검색하는 데 사용할 것입니다. 이제 따라야 할 단계는 다음과 같습니다.
🔗 단계:
❶ 우선 선택 전체 데이터 테이블.
❷ 그런 다음 데이터 리본.
❸ 그런 다음 고급 옵션.
❹ 마지막 단계가 끝나면 고급 필터라는 대화 상자가 나타납니다. 화면에 나타납니다.
❺ 이제
를 입력하세요.$B$4:$C$13 목록 범위: 바와
'Advanced Filter'!$E$4:$E$5 기준 범위: 내 술집.
❻ 그런 다음 확인을 누를 수 있습니다. 버튼.
이전 단계를 모두 마치면 다음과 같이 출력됩니다.
8. 형식을 표로 사용하여 Excel에서 여러 값을 가져오는 Vlookup
이 방법을 자동 필터 대신 사용할 수 있습니다. 및 고급 필터 옵션. 이 방법에서는 표 형식을 사용합니다. 엔지니어링 아래의 모든 직원 이름을 정렬하는 옵션 범주. 따라야 할 단계는 다음과 같습니다.
🔗 단계:
❶ 우선 선택 전체 데이터 테이블.
❷ 그런 다음 홈으로 이동합니다. 리본.
❸ 그런 다음 표 형식을 선택합니다. 옵션.
❹ 그런 다음 선택 원하는 대로 테이블 스타일을 지정할 수 있습니다.
이제 데이터 테이블이 다음 이미지와 같이 보일 것입니다.
❺ 이 단계에서 필터 아이콘을 클릭합니다. 부서 아래 열 머리글.
❻ 팝업 메뉴에서 체크 엔지니어링에만 표시 옵션.
❼ 그런 다음 확인 버튼.
이전 단계를 모두 마치면 결과가 다음과 같아야 합니다.
기억해야 할 사항
📌 VLOOKUP 함수는 하나 이상의 값을 가져올 수 없습니다.
📌 함수 구문에 주의하세요.
📌 데이터 범위를 수식에 신중하게 삽입하세요.
결론
마무리하기 위해 Excel에서 여러 값을 조회하고 반환하는 8가지 고유한 방법에 대해 논의했습니다. 이 글과 함께 첨부된 연습문제집을 다운받아 모든 방법을 연습하시길 권장합니다. 그리고 아래의 댓글 섹션에서 주저하지 말고 질문하십시오. 모든 관련 문의에 최대한 빨리 답변해 드리겠습니다.
관련 기사
- Excel에서 여러 행으로 VLOOKUP을 수행하는 방법(5가지 방법)