핵심 요약
- 먼저 INDEX 함수를 생성한 후, Lookup_value 인수를 입력하여 중첩 MATCH 함수를 시작합니다.
- 다음으로 Lookup_array 인수와 Match_type 인수를 차례로 추가하고, 열 범위를 지정합니다.
- Ctrl+Shift+Enter 키를 눌러 중첩 함수를 배열 수식으로 변환한 뒤, 워크시트에 검색어를 입력하면 완성됩니다.
이 글에서는 엑셀에서 여러 조건을 동시에 적용하는 조회 수식을 만들어, 데이터베이스나 데이터 테이블에서 원하는 정보를 찾는 방법을 소개합니다. 핵심은 배열 수식(Array Formula)이며, MATCH 함수를 INDEX 함수 안에 중첩하는 방식으로 구현합니다. Microsoft 365용 Excel, Excel 2019, 2016, 2013, 2010 및 Mac용 Excel에서 모두 사용할 수 있습니다.
튜토리얼 따라 하기: 샘플 데이터 입력
이 튜토리얼의 단계를 따라 하려면 아래 이미지와 같이 샘플 데이터를 해당 셀에 입력하세요. 3행과 4행은 이 튜토리얼에서 만들 배열 수식을 위해 비워 둡니다. (참고: 이 튜토리얼에는 이미지에 표시된 서식 작업은 포함되지 않습니다.)
- 첫 번째 데이터 범위는 D1~F2 셀에 입력합니다.
- 두 번째 데이터 범위는 D5~F11 셀에 입력합니다.
1단계: INDEX 함수 만들기
INDEX 함수는 엑셀에서 여러 형태를 가진 몇 안 되는 함수 중 하나입니다. 이 함수에는 배열 형식(Array Form)과 참조 형식(Reference Form)이 있습니다. 배열 형식은 데이터베이스나 데이터 테이블에서 실제 데이터를 반환하고, 참조 형식은 테이블에서 해당 데이터의 셀 참조나 위치를 반환합니다.
이 튜토리얼에서는 데이터베이스상의 셀 위치가 아니라 '티타늄 위젯(Titanium Widgets)'을 판매하는 공급업체의 이름을 찾아야 하므로, 배열 형식을 사용합니다.
다음 순서대로 INDEX 함수를 만들어 보세요.
F3 셀을 선택하여 활성 셀로 만듭니다. 이 셀에 중첩 함수가 입력됩니다.
리본 메뉴에서 수식(Formulas) 탭으로 이동합니다.
조회/참조(Lookup & Reference)를 선택하여 함수 드롭다운 목록을 엽니다.
INDEX를 선택하면 인수 선택(Select Arguments) 대화 상자가 열립니다.
array,row_num,column_num을 선택합니다.
확인(OK)을 클릭하면 함수 인수(Function Arguments) 대화 상자가 열립니다. Mac용 Excel에서는 수식 작성기(Formula Builder)가 열립니다.
커서를 Array 텍스트 상자에 놓습니다.
워크시트에서 D6~F11 셀을 드래그하여 대화 상자에 범위를 입력합니다.
알아두세요: 함수 인수 대화 상자를 닫지 말고 그대로 열어 두세요. 수식이 아직 완성되지 않았으며, 아래 단계에서 계속 작성하게 됩니다.
2단계: 중첩 MATCH 함수 시작하기
한 함수를 다른 함수 안에 중첩할 때는 두 번째(중첩된) 함수의 수식 작성기를 별도로 열 수 없습니다. 중첩 함수는 반드시 첫 번째 함수의 인수 중 하나로 직접 입력해야 합니다.
알아두세요: 함수를 수동으로 입력할 때는 각 인수를 쉼표(,)로 구분합니다.
중첩 MATCH 함수 입력의 첫 단계는 Lookup_value 인수를 입력하는 것입니다. Lookup_value는 데이터베이스에서 일치시킬 검색어의 위치 또는 셀 참조를 의미합니다.
Lookup_value는 기본적으로 하나의 검색 조건만 받습니다. 여러 조건을 검색하려면 앰퍼샌드(&) 기호를 사용해 두 개 이상의 셀 참조를 연결(결합)하여 Lookup_value를 확장해야 합니다.
함수 인수 대화 상자에서 Row_num 텍스트 상자에 커서를 놓습니다.
MATCH( 를 입력합니다.
D3 셀을 클릭하여 해당 셀 참조를 대화 상자에 입력합니다.
셀 참조 D3 뒤에 &(앰퍼샌드)를 입력하여 두 번째 셀 참조를 추가합니다.
E3 셀을 클릭하여 두 번째 셀 참조를 입력합니다.
셀 참조 E3 뒤에 ,(쉼표)를 입력하여 MATCH 함수의 Lookup_value 인수 입력을 완료합니다.
튜토리얼 마지막 단계에서 실제 검색어(Lookup_values)가 워크시트의 D3와 E3 셀에 입력됩니다.
3단계: 중첩 MATCH 함수 완성하기
이 단계에서는 중첩된 MATCH 함수의 Lookup_array 인수를 추가합니다. Lookup_array는 MATCH 함수가 앞 단계에서 지정한 Lookup_value를 찾기 위해 탐색하는 셀 범위입니다.
Lookup_value에서 두 개의 검색 필드를 지정했으므로, Lookup_array에서도 마찬가지로 두 개의 범위를 지정해야 합니다. MATCH 함수는 지정된 각 값에 대해 하나의 배열만 검색하므로, 여러 배열을 사용하려면 앰퍼샌드(&)로 배열들을 연결해야 합니다.
Row_num 텍스트 상자에서 입력된 데이터의 맨 끝에 커서를 놓습니다. 커서는 현재 입력 끝의 쉼표 뒤에 위치합니다.
워크시트에서 D6~D11 셀을 드래그하여 범위를 입력합니다. 이 범위는 함수가 검색하는 첫 번째 배열입니다.
셀 참조 D6:D11 뒤에 &(앰퍼샌드)를 입력합니다. 이 기호 덕분에 함수가 두 개의 배열을 동시에 검색하게 됩니다.
워크시트에서 E6~E11 셀을 드래그하여 범위를 입력합니다. 이 범위는 함수가 검색하는 두 번째 배열입니다.
셀 참조 E6:E11 뒤에 ,(쉼표)를 입력하여 MATCH 함수의 Lookup_array 인수 입력을 완료합니다.
다음 단계를 위해 대화 상자를 계속 열어 둡니다.
4단계: Match_type 인수 추가하기
MATCH 함수의 세 번째이자 마지막 인수는 Match_type입니다. 이 인수는 Lookup_value를 Lookup_array의 값과 어떤 방식으로 일치시킬지 엑셀에 알려주며, 1, 0, -1 중 하나를 지정할 수 있습니다.
알아두세요: 이 인수는 선택 사항이며, 생략하면 기본값 1이 적용됩니다.
- Match_type = 1 또는 생략: Lookup_value보다 작거나 같은 값 중 가장 큰 값을 찾습니다. Lookup_array 데이터는 오름차순으로 정렬되어 있어야 합니다.
- Match_type = 0: Lookup_value와 정확히 일치하는 첫 번째 값을 찾습니다. Lookup_array 데이터는 정렬 순서와 무관합니다.
- Match_type = -1: Lookup_value보다 크거나 같은 값 중 가장 작은 값을 찾습니다. Lookup_array 데이터는 내림차순으로 정렬되어 있어야 합니다.
INDEX 함수의 Row_num 입력란에서 앞 단계에서 입력한 쉼표 뒤에 이어서 다음을 입력합니다.
Row_num 텍스트 상자의 쉼표 뒤에 0(영)을 입력합니다. 이 숫자는 중첩 함수가 D3와 E3 셀에 입력된 검색어와 정확히 일치하는 값을 반환하도록 설정합니다.
)(닫는 괄호)를 입력하여 MATCH 함수를 완성합니다.
다음 단계를 위해 대화 상자를 계속 열어 둡니다.
5단계: INDEX 함수 마무리하기
MATCH 함수가 완성되었습니다. 이제 대화 상자의 Column_num 텍스트 상자로 이동해 INDEX 함수의 마지막 인수를 입력합니다. 이 인수는 D6~F11 범위 내에서 결과를 가져올 열 번호를 엑셀에 알려줍니다. 즉, 함수가 반환할 정보(이 예제에서는 티타늄 위젯의 공급업체명)가 있는 열을 지정하는 것입니다.
Column_num 텍스트 상자에 커서를 놓습니다.
3을 입력합니다. 이 숫자는 수식이 D6~F11 범위의 세 번째 열에서 데이터를 찾도록 지시합니다.
다음 단계를 위해 대화 상자를 계속 열어 둡니다.
6단계: 배열 수식으로 변환하기
대화 상자를 닫기 전에 중첩 함수를 배열 수식으로 변환해야 합니다. 배열 수식으로 만들어야 함수가 데이터 테이블에서 여러 조건을 동시에 검색할 수 있습니다. 이 튜토리얼에서는 두 가지 조건, 즉 1열의 'Widgets'와 2열의 'Titanium'을 매칭합니다.
엑셀에서 배열 수식을 만들려면 CTRL, SHIFT, ENTER 키를 동시에 누릅니다. 그러면 수식이 중괄호 { } 로 감싸지며, 이는 해당 수식이 배열로 처리되고 있음을 나타냅니다.
확인(OK)을 클릭해 대화 상자를 닫습니다. Mac용 Excel에서는 완료(Done)를 클릭합니다.
F3 셀을 선택해 수식을 확인한 후, 수식 입력줄에서 수식 맨 끝에 커서를 놓습니다.
CTRL+SHIFT+ENTER를 눌러 수식을 배열 수식으로 변환합니다.
F3 셀에 #N/A 오류가 표시됩니다. 이 셀이 함수가 입력된 셀입니다.
#N/A 오류가 나타나는 이유는 D3와 E3 셀이 비어 있기 때문입니다. D3와 E3는 함수가 Lookup_value를 읽어 오는 셀입니다. 이 두 셀에 데이터가 입력되면 오류는 자동으로 데이터베이스의 정보로 대체됩니다.
7단계: 검색 조건 입력하기
마지막 단계는 워크시트에 실제 검색어를 입력하는 것입니다. 이 단계에서 1열의 'Widgets'와 2열의 'Titanium'이 매칭됩니다.
수식이 데이터베이스의 해당 열에서 두 조건 모두와 일치하는 행을 찾으면, 세 번째 열의 값을 결과로 반환합니다.
D3 셀을 선택합니다.
Widgets를 입력합니다.
E3 셀을 선택합니다.
Titanium을 입력하고 Enter 키를 누릅니다.
F3 셀에 공급업체 이름인 Widgets Inc.가 표시됩니다. 이 회사가 데이터베이스에 등록된 유일한 티타늄 위젯 판매 업체입니다.
F3 셀을 선택하면 워크시트 위쪽의 수식 입력줄에 완성된 함수가 표시됩니다.
{=INDEX(D6:F11,MATCH(D3&E3,D6:D11&E6:E11,0),3)}
참고: 이 예제에서 티타늄 위젯의 공급업체는 하나뿐입니다. 만약 공급업체가 두 곳 이상이라면, 함수는 데이터베이스에 가장 먼저 나열된 공급업체를 반환합니다.
