VLOOKUP 함수로는 구할 수 없는 조건에 해당하는 여러 개의 값을 찾아 나타내는 실무 예제

안녕하세요.

 

 

실습 파일 다운로드

방과후강사활동확인서_수정.xlsx

0.04MB

 

완성 파일 다운로드

방과후강사활동확인서_완성.xlsx

0.04MB

 

INDEX 함수를 이용해서 구현하는 방법을 소개했었습니다.

 

2023.10.20 - VLOOKUP 함수로는 구할 수 없는 조건에 해당하는 여러 개의 값을 찾아 나타내는 방법

VLOOKUP 함수로는 구할 수 없는 조건에 해당하는 여러 개의 값을 찾아 나타내는 방법

 

지난 2021년 11월 출강에서 질문 받았던 윤**님의 업무에서 사용하는 파일입니다.

윤**님이 직접 만들어서 사용하던 중 저에게 질문을 해서 조금 수정했었습니다.

 

INDEX 함수를 이용해서 만들어졌고, 새로 추가된 FILTER 함수를 이용해서 수정하려고 했는데 손 놓고 있다가 이번에 다시 살펴봅니다.

 

데이터 시트에 데이터가 있습니다.

출력 시트는 INDEX 함수를 이용해서 구현되어 있습니다.

 

이번에 소개하려는 부분은 출력2 시트입니다.

 

이해하기 쉽도록 먼저 출력 시트에 만들어져 있는 내용을 소개합니다.

 

[K6] 셀에 이름, [K7] 셀에 생년월일을 입력하면 [K8] 셀에 이름과 생년월일을 더해서 검색조건을 만들고 수식으로 데이터 시트에서 찾아 [C11:G20] 셀 범위에 결과를 나타냅니다.

사원번호나 주민등록번호 같은 특정인을 구분할 수 있는 필드가 없을 때 필드 몇 개를 합쳐 사용하는 방법으로 많이 사용됩니다.

 

[G11:H20] 셀 범위는 좀 특이한 서식이 설정되어 있는데, 두 셀을 하나로 합치는 셀 병합을 한 것이 아니라, [셀 서식]-[맞춤]에서 '선택 영역의 가운데로'를 설정해서 데이터가 셀 중간에 걸쳐져 있는 것으로 보입니다.

 

[C11] 셀 수식입니다.

=IFERROR(INDEX(시작일,SMALL(IF((출력!$K$8=검색조건),MATCH(ROW(시작일),ROW(시작일),0),""),ROWS($A$2:A2))),"")

 

[D11] 셀 수식은 좀 다릅니다.

=IFERROR(IF(INDEX(종료일,SMALL(IF((출력!$K$8=검색조건),MATCH(ROW(종료일),ROW(종료일),0),""),ROWS($A$2:A2)))<TODAY(),INDEX(종료일,SMALL(IF((출력!$K$8=검색조건),MATCH(ROW(종료일),ROW(종료일),0),""),ROWS($A$2:A2))),TODAY()),"")

 

IF 함수를 추가해서 종료일이 활동 확인서를 발급하는 날보다 뒤의 날짜인 경우엔 활동 확인서를 발급하는 오늘 날짜를 나타냅니다.

 

수식을 좀 더 쉽게 이해할 수 있도록 참조 범위는 [이름 정의]를 했습니다.

 

작성된 수식은 배열 수식이기 때문에 수식을 마무리할 때 <Ctrl + Shift + Enter>를 눌러야 합니다.

 

 

 

이제 출력2 시트를 소개합니다.

 

서식은 출력 시트와 같습니다.

 

[C11] 셀 수식입니다.

=FILTER(출력2데이터,K8=검색조건,"")

 

엑셀 2019 이상에서 쓸 수 있는 FILTER 함수를 써서 수식이 단순합니다.

 

그런데 안타깝게도 그대로 쓸 수 없습니다.

위에서 소개한 종료일 문제를 해결해야 합니다.

 

수식을 [C11] 셀에 입력하지 않고 [C32] 셀에 입력한 뒤 나타난 결과를 [C11] 셀에 가져오면서 필요한 처리를 해서 나타내는 것으로 문제를 해결합니다.

 

서식이 적용되어 있지 않기 때문에 [C32:D32] 셀 날짜가 숫자 형식으로 표시됩니다.

결과를 나타내는 것에는 문제가 없어서 따로 수정하지는 않습니다.

 

[C11] 셀의 기존 수식은 지우고, 새로 수식을 작성합니다.

=IF(C32<>"",C32,"")

 

[C11] 셀을 [G11] 셀까지 채우기하고 [채우기 옵션]에서 '서식 없이 채우기'를 선택합니다.

 

[D11] 셀 수식은 수정합니다.

=IF(D32<>"",IF(D32>TODAY(),TODAY(),D32),"")

 

[C11:G11] 셀 범위 선택한 뒤 [G20] 셀까지 채우기 합니다.

 

여기까지 수식을 작성해서 출력 시트에서 구현한 것과 똑같아 졌습니다.

 

 

그런데 출력 시트와 출력2 시트 둘 다 아직 해결되지 않은 문제가 있습니다.

 

경력사항을 나타내는 영역이 10개 행인데, 경력사항이 10개 이상인 경우에는 최근 경력을 표시하지 않는 문제가 있습니다.

활동 확인서 서식을 바꿔서 10개 이상인 경우에 모두 표시하도록 할 순 있지만 1장의 종이에 인쇄를 해야 하기 때문에 경력 사항을 나타내는 칸을 무조건 늘릴 수 없습니다.

 

이 문제를 해결하기 위해서 출력(수정시-값붙이기) 시트가 준비되어 있습니다.

자동으로 처리되도록 하는 것보다 문제가 발생하는 경우가 많지 않기 때문에, 문제가 생기면 수작업을 하는 것이 더 효율적입니다.

 

INDEX 함수를 이용한 경우에는 경력사항이 10개 이상인지 확인할 방법이 없습니다.

FILTER 함수를 이용하면 IF 문을 하나 추가해서 10개 이상인지 확인할 수 있습니다.

 

[C32] 셀 수식을 수정합니다.

=IF(ROWS(FILTER(출력2데이터,K8=검색조건,""))>10,"오류",FILTER(출력2데이터,K8=검색조건,""))

 

[K6] 셀에 박땡숙, [K7] 셀에 561120을 입력합니다.

이 값은 현재 데이터 중 경력사항이 10건 이상인 유일한 데이터입니다.

 

출력(수정시-값붙이기) 시트에서 사용할 수 있도록 전체 경력사항을 나타내는 수식을 추가합니다.

 

[C43] 셀에 수식을 작성합니다.

=FILTER(출력2데이터,K8=검색조건,"")

 

필요하신 분께 도움이 되길 바랍니다.