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

안녕하세요.

 

완성 파일 다운로드

조건에 해당하는 여러 개의 값을 찾아 나타내기.xlsx

0.01MB

 

질문을 받았습니다.

 

질문의 요지는 여러 개의 근무조가 있는데, 1개의 근무조에는 최소 3명, 최대 4명으로 이루어져 있어 근무조를 입력하면 그 근무조에 해당하는 조원을 나타내는 수식을 구하는 것이었습니다.

 

질문자는 VLOOKUP 함수를 이용해서 구현해 봤는데, 작성한 수식은 오류가 난다고 했습니다.

 

VLOOKUP 함수가 대단한 함수이긴 하지만 모든 일에 다 사용할 수 있는 것이 아닙니다.

VLOOKUP 함수는 조건에 해당하는 1개의 값만 찾아올 수 있습니다.

찾아오는 1개의 값은 위에서부터 아래로 나열되어 있는 값 중 조건에 해당하는 위쪽에서 첫번째 있는 값만 가져올 수 있습니다.

그래서 이 문제에서는 사용할 수 없습니다.

 

엑셀 기능 중 조건에 해당하는 여러 개의 값을 찾아오는 기능은 [고급 필터] 기능입니다.

[고급 필터] 기능을 함수로 구현한 것이 FILTER 함수입니다.

 

[B4] 셀에 수식을 작성했습니다.

=FILTER(Group!B3:C16,C1=Group!B3:B16,"")

FILTER 함수는 배열 함수여서 [B4] 셀에 수식을 작성하면 조건에 해당하는 값을 가져와서 [B4] 셀부터 표시합니다.

한 근무조에 속해 있는 근무자의 최대 인원수가 4명이므로 4명의 정보를 나타낼 수 있도록 셀 범위를 비워야 합니다.

 

FILTER 함수는 엑셀 2019 이상 버전에서 사용할 수 있는 함수입니다.

 

FILTER 함수를 쓸 수 없는 경우에는 INDEX 함수를 이용해서 원하는 값을 구할 수 있습니다.

INDEX 함수도 VLOOKUP 함수처럼 조건에 해당하는 1개의 값을 찾는 함수인데, 인수에 값을 입력해서 첫번째, 두번째, 세번째, ... 값을 선택할 수 있습니다.

 

결과로 나타날 수 있는 값이 최대 4개여서 [E4:E7] 셀 범위에 찾는 근무조가 나타나도록 수식을 만들었고, [F4] 셀에 수식을 작성한 뒤 [F7] 셀까지 채우기를 했습니다.

=IFERROR(INDEX(Group!$C$3:$C$16,SMALL(IF($C$1=Group!$B$3:$B$16,MATCH(ROW(Group!$B$3:$B$16),ROW(Group!$B$3:$B$16),0),""),ROWS($A$1:A1))),"")

 

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

 

한 줄로 작성된 수식은 알아보기 힘들기 때문에 사용한 함수를 기준으로 인수별로 한 줄에 나타나도록 바꿨습니다.

=IFERROR(
    INDEX(
        Group!$C$3:$C$16,
        SMALL(
            IF(
                $C$1=Group!$B$3:$B$16,
                MATCH(
                    ROW(Group!$B$3:$B$16),
                    ROW(Group!$B$3:$B$16),
                    0
                    ),
                ""
            ),
            ROWS($A$1:A1)
            )
        ),
    "")

IFERROR 함수는 선택한 근무조의 인원이 3명인 경우 마지막 셀에 오류 메시지가 나타나지 않도록 씁니다.

 

INDEX 함수는 조건에 해당하는 값을 가져오는 실제 동작을 합니다.

SMALL 함수는 두번째 인수에 값을 지정해서 첫번째, 두번째, 세번째, ...를 지정합니다.

ROWS 함수는 행 개수를 나타내는데, 이 수식을 아래로 채우기 하면 1, 2, 3, ...으로 번호를 나타냅니다.

 

IF 함수는 조건에 일치하는지 여부를 확인합니다.

IF 함수의 첫번째 인수에서 비교여부를 확인해서 비교 값과 일치하는 경우 IF 함수의 두번째 인수에 입력된 MATCH 함수로 몇 번째 위치에 있는지 찾습니다.

 

MATCH 함수 대신 아래 수식을 써도 됩니다.

ROW(Group!$B$3:$B$16)-2

실제 데이터가 3번째 행부터 입력되어 있기 때문에 행 번호에서 2를 뺍니다.

MATCH 함수를 쓴 이유는 데이터가 어떤 행에서부터 입력되어 있던 상관없이 똑같이 쓸 수 있기 때문입니다.

 

[함수 인수] 대화상자를 이용하면 수식의 결과를 단계별로 확인할 수 있습니다.

 

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