엑셀에서 여러 조건으로 표의 데이터 추출하기: 단일 값부터 다중 값까지 완벽 정리

필요한 순간에 원하는 데이터를 정확히 꺼내 쓰는 것은 스프레드시트 활용의 핵심입니다. 엑셀 역시 예외가 아닙니다. 우리는 데이터를 표 형태로 저장하고, 필요할 때 그중 일부를 추출해 사용하죠. 이번 글에서는 여러 개의 조건(다중 기준)을 만족하는 데이터를 엑셀 표에서 추출하는 다양한 방법을 단계별로 살펴보겠습니다.

먼저 예제의 기반이 되는 데이터셋부터 확인해 보겠습니다.

여기에는 영화 목록이 장르(Genre), 주연 배우(Lead Actor), 개봉 연도(Release Year)와 함께 정리된 표가 있습니다. 이 데이터셋을 활용해 다중 조건에 맞는 데이터를 조회하는 방법을 익혀볼 것입니다.

설명을 단순하게 유지하기 위해 기본적인 크기의 표를 사용했지만, 실무에서는 훨씬 방대하고 복잡한 데이터를 다루게 됩니다. 그래도 핵심 원리는 동일하니 걱정하지 않으셔도 됩니다.

연습용 워크북

아래 링크에서 직접 따라 해볼 수 있는 연습용 워크북을 내려받으실 수 있습니다.

여러 조건으로 표에서 데이터 추출하기

예시로 장르와 배우 이름을 조건으로 제공하고, 이 두 조건에 모두 해당하는 영화 제목을 추출해 보겠습니다.

1. 단일 값 반환하기

먼저 조건에 맞는 값이 하나만 존재하는 경우, 즉 단일 값을 반환하는 방법부터 알아보겠습니다.

I. INDEX-MATCH 배열 수식

INDEX 함수와 MATCH 함수를 조합해 사용할 수 있습니다. INDEX는 지정된 범위에서 주어진 위치의 값을 반환하고, MATCH는 범위 안에서 찾고자 하는 값의 위치를 알려줍니다.

두 함수에 대한 자세한 내용은 관련 문서(INDEX, MATCH)를 참고하세요.

조건값을 먼저 설정합니다. 우선 장르는 Thriller(스릴러), 배우는 Hugh Jackman으로 지정해 보겠습니다.

사용할 수식은 다음과 같습니다.

B4:B19는 값을 추출할 배열이며, MATCH 함수가 가져올 행 번호를 결정합니다.

MATCH 안에 lookup_value로 1을 넣은 점에 주목하세요. lookup_array는 두 조건 비교 논리식을 곱한 결과로 생성됩니다.

$H$4=$C$4:$C$19로 장르를 확인하고, $H$5=$D$4:$D$19로 배우 이름을 확인합니다.

곱셈 결과 배열 중에서 1을 찾으면 해당 행 번호가 반환되고, INDEX 함수가 그 행에 해당하는 영화 제목을 돌려줍니다.

이것은 배열 수식이므로 실행할 때 CTRL + SHIFT + ENTER를 눌러야 합니다.

조건값을 바꾸면 결과값도 자동으로 갱신됩니다.

더 읽어보기: 엑셀에서 셀에서 특정 데이터 추출하는 방법 (예제 3가지)

II. INDEX-MATCH 비배열 수식

배열 수식 없이 INDEX와 MATCH만으로도 같은 작업을 할 수 있습니다.

먼저 수식을 살펴보겠습니다.

여기서는 INDEX 함수가 두 번 사용되었습니다. 바깥쪽 INDEX가 실제 값을 추출하는 역할을 하고, 안쪽 INDEX가 행 번호를 찾는 데 도움을 줍니다.

안쪽 INDEX 안에서 두 조건의 논리식이 곱해져 배열 참조처럼 동작합니다.

이번에는 ENTER 키만 누르면 수식이 실행됩니다. CTRL+SHIFT+ENTER가 필요 없다는 점이 편리합니다.

조건값을 자유롭게 수정해 보세요. 결과값이 즉시 갱신됩니다.

더 읽어보기: 엑셀에서 조건에 따라 데이터 추출하는 방법 (5가지)

III. INDEX-MATCH-IF 조합

앞선 방법들은 조건들을 곱해서 하나의 논리로 묶었습니다. 하지만 IF 함수를 사용하면 곱셈 없이도 조건을 결합할 수 있습니다.

IF 함수는 논리 검사를 수행하고 그 결과로 TRUE 또는 FALSE를 반환합니다. 자세한 내용은 IF 관련 문서를 참고하세요.

수식은 다음과 같습니다.

조건이 두 개이므로 IF도 두 번 사용되었습니다. 이들은 중첩 IF 구조(하나가 다른 하나 안에 포함)로 동작합니다. 바깥쪽 IF가 첫 번째 조건을 먼저 검사하고, 안쪽 IF(두 번째 조건)는 첫 번째 IF의 if_true_value 역할을 합니다.

이 수식을 실행하려면 CTRL+SHIFT+ENTER를 눌러야 합니다.

조건값을 변경하면 결과값도 함께 업데이트됩니다.

더 읽어보기: 엑셀 시트에서 데이터 추출하는 효과적인 방법 6가지

IV. LOOKUP 함수

LOOKUP 함수로도 조건에 맞는 데이터를 추출할 수 있습니다.

LOOKUP 함수는 범위에서 일치하는 값을 찾아 대응되는 값을 반환합니다. 자세한 정보는 Microsoft Support 사이트를 참고하세요.

그럼 수식을 살펴보겠습니다.

여기서는 lookup_value로 2를 지정했습니다. 그리고 1을 두 논리식으로 나눈 형태가 lookup_vector가 됩니다.

구체적으로는 TRUE/FALSE 배열($C$4:$C$19=$H$4)로 1을 나눈 뒤, 또 다른 TRUE/FALSE 배열($D$4:$D$19=$H$5)로 한 번 더 나눕니다. 그 결과는 1 또는 #DIV/0! 오류가 됩니다.

lookup_value는 범위 내 숫자값과 일치하도록 지시하며, 일치하는 값이 발견되면 B4:B19 배열에서 최종 값이 가져와집니다.

이 방법은 CTRL + SHIFT + ENTER 없이 ENTER만으로 실행됩니다.

조건값을 바꿔가며 다른 값에서도 수식이 올바르게 작동하는지 확인해 보세요.

참고로 lookup_value를 2로 사용했는데, 이는 1 이상의 어떤 숫자든 가능합니다.

더 읽어보기: 엑셀에서 이미지 속 데이터를 추출하는 방법 (빠른 단계 포함)

함께 읽으면 좋은 글

• 텍스트 파일을 엑셀로 변환하는 VBA 코드 (7가지 방법)
• 엑셀 VBA: 웹사이트에서 자동으로 데이터 가져오기 (2가지 방법)
• 보안 웹사이트에서 엑셀로 데이터 가져오는 방법 (빠른 단계 포함)
• VLOOKUP으로 한 엑셀 워크시트에서 다른 워크시트로 데이터 자동 전송하기
• 파이프(|) 구분자로 엑셀을 텍스트 파일로 변환하는 방법 (2가지)

2. 다중 값 반환하기

I. INDEX-SMALL 조합

조건에 맞는 여러 개의 데이터를 추출할 때도 다양한 함수 조합을 활용할 수 있습니다. 대표적인 것이 INDEX – SMALL 조합입니다.

SMALL 함수는 값 순위 목록에서 위치에 해당하는 값을 반환합니다. 자세한 내용은 SMALL 관련 문서를 참고하세요.

이 두 함수 외에도 IF, ROW, IFERROR 같은 보조 함수가 필요합니다. 각 함수에 대한 설명은 관련 문서(IF, ROW, IFERROR)를 확인하세요.

수식은 다음과 같습니다.

각 함수마다 고유한 역할이 있습니다. INDEX 함수는 B2:B17 배열에서 값을 반환하고, 큰 SMALL 부분이 가져올 행 번호를 계산합니다.

SMALL 안의 IF는 조건 일치 여부를 판단합니다. 조건이 두 개이므로 두 논리식을 곱해 함께 검사하며, ROW 함수가 열의 셀들을 순회합니다.

바깥쪽 ROW는 SMALL 함수의 k번째 값을 지정합니다. 이 함수들이 함께 행 번호를 계산하면 INDEX가 최종 결과를 반환합니다.

IFERROR는 수식에서 발생할 수 있는 오류를 처리합니다. 오류 발생 시 빈 셀이 출력되도록 설정했습니다.

수식을 아래로 드래그하면 조건에 맞는 모든 값을 얻을 수 있습니다.

더 읽어보기: 엑셀 수식으로 목록에서 데이터 추출하는 방법 (5가지)

II. INDEX-AGGREGATE 조합

엑셀의 AGGREGATE 함수는 하나의 함수로 여러 작업을 수행할 수 있게 해주는 강력한 도구입니다. 이 함수로도 다중 조건에 맞는 여러 값을 반환할 수 있습니다.

AGGREGATE 함수는 AVERAGE, COUNT, MAX 등과 같은 집계 계산 결과를 반환합니다.

AGGREGATE 함수의 구문은 다음과 같습니다.

function_number: 어떤 계산을 수행할지 지정하는 번호입니다.

behavior_options: 함수의 동작 방식을 숫자로 지정합니다.

range: 집계하려는 범위입니다.

AGGREGATE 함수는 여러 작업을 수행하므로 미리 정의된 함수 번호들이 있습니다. 자주 사용되는 번호 몇 가지를 정리했습니다.

함수에 대한 자세한 내용은 Microsoft Support 사이트를 참고하세요.

이제 INDEX와 AGGREGATE를 조합한 수식을 살펴보겠습니다.

여기서는 AGGREGATE의 function_number로 15를 사용했습니다. 위 표에서 볼 수 있듯이 15는 SMALL 함수 연산을 호출합니다. AGGREGATE 부분(함수 번호와 동작 옵션 번호)을 제외하면 앞서 본 INDEX–SMALL 수식과 완전히 동일합니다.

동작 원리도 같습니다. INDEX는 배열을 담고 있으며, 수식의 AGGREGATE 부분에서 찾은 일치 항목을 기반으로 값을 반환합니다.

동작 옵션으로 6을 지정했는데, 이는 오류 값 무시(ignore error values)를 의미합니다.

수식을 아래로 드래그하면 조건에 맞는 모든 값을 얻을 수 있습니다.

수식을 실행할 때 CTRL+SHIFT+ENTER를 사용해야 한다는 점을 잊지 마세요.

더 읽어보기: 엑셀에서 단일 조건으로 여러 값 반환하기 (3가지 옵션)

III. INDEX-MATCH-COUNTIF 조합

다중 조건에 맞는 여러 값을 반환할 때는 INDEX, MATCH, COUNTIF의 조합도 유용합니다.

COUNTIF 함수는 하나의 조건을 만족하는 범위 내 셀의 개수를 세어줍니다. 자세한 내용은 COUNTIF 관련 문서를 참고하세요.

수식은 다음과 같습니다.

MATCH 함수 안에서 lookup_array로 0을 지정했고, lookup_range에는 COUNTIF가 포함된 IF 부분을 사용했습니다.

여기서 COUNTIF는 이미 추출된 값을 제외하는 역할을 하고, 두 개의 IF 함수가 각각 두 조건을 검사합니다. 이 함수들이 합쳐져 lookup_range를 구성합니다.

MATCH 부분은 0이 발견되는 동안 값을 반환하며, 이 값이 INDEX의 행 번호로 작동합니다.

수식을 아래로 드래그하면 조건에 맞는 모든 값을 얻을 수 있습니다.

더 읽어보기: 엑셀에서 셀에서 데이터 추출하는 방법 (5가지)

IV. FILTER 함수

Excel 365를 사용 중이라면 FILTER라는 단일 내장 함수만으로 이 작업을 처리할 수 있습니다.

FILTER 함수는 지정된 조건에 따라 데이터 범위를 필터링하고 일치하는 레코드를 추출합니다. 자세한 내용은 FILTER 관련 문서를 참고하세요.

수식을 살펴보겠습니다.

B4:B19는 필터링할 배열입니다. 그다음 값을 추출할 기준이 되는 조건을 지정했으며, 두 조건을 모두 확인해야 하므로 서로 곱했습니다.

이 방법은 수식을 아래로 드래그할 필요가 없습니다. 한 번의 입력으로 조건에 맞는 모든 값이 한꺼번에 채워집니다.

더 읽어보기: 엑셀에서 필터링된 데이터를 다른 시트로 추출하는 방법 (4가지)

마치며

오늘은 여기까지입니다. 이번 글에서는 엑셀 표에서 다중 조건에 따라 데이터를 추출하는 여러 가지 방법을 정리했습니다. 단일 값 추출부터 다중 값 추출까지 상황에 맞는 방법을 선택해 활용해 보시기 바랍니다. 이해하기 어려운 부분이 있다면 언제든지 댓글로 질문해 주세요. 여기서 다루지 않은 다른 방법이 있다면 알려주시면 감사하겠습니다.

추천 추가 학습 자료

• 엑셀에서 Word로 데이터 추출하는 방법 (4가지)
• 엑셀 VBA로 한 시트에서 다른 시트로 데이터 추출하기 (3가지 방법)
• 엑셀 VBA에서 여러 워크시트에서 데이터 가져오는 방법
• 한 엑셀 워크시트에서 다른 워크시트로 데이터 자동 전송하기
• 엑셀에서 조건에 따라 다른 시트에서 데이터 가져오는 방법
• 엑셀 매크로: 여러 엑셀 파일에서 데이터 추출하기 (4가지 방법)
• 엑셀에서 날짜에서 연도만 추출하는 방법 (3가지)