여러 기준으로 Excel 조회 수식을 만드는 방법

알아야 할 사항

• 먼저 INDEX 함수를 만든 다음 Lookup_value 인수를 입력하여 중첩된 MATCH 함수를 시작합니다.
• 그런 다음 Lookup_array 인수와 Match_type 인수를 추가한 다음 열 범위를 지정합니다.
• 그런 다음 Ctrl + Shift + Enter 를 눌러 중첩 함수를 배열 수식으로 바꿉니다. 마지막으로 워크시트에 검색어를 추가합니다.

이 문서에서는 배열 수식을 사용하여 데이터베이스 또는 데이터 테이블에서 정보를 찾기 위해 Excel에서 여러 조건을 사용하는 조회 수식을 만드는 방법에 대해 설명합니다. 배열 수식에는 INDEX 함수 내에 MATCH 함수를 중첩하는 작업이 포함됩니다. 정보는 Microsoft 365용 Excel, Excel 2019, Excel 2016, Excel 2013, Excel 2010 및 Mac용 Excel을 다룹니다.

튜토리얼 따라하기

이 자습서의 단계를 따르려면 아래 이미지에 표시된 대로 다음 셀에 샘플 데이터를 입력합니다. 행 3과 4는 이 자습서에서 만든 배열 수식을 수용하기 위해 비워 둡니다. (이 자습서에는 이미지에 표시된 서식이 포함되어 있지 않습니다.)

• 데이터의 상위 범위를 셀 D1~F2에 입력합니다.
• 셀 D5에서 F11까지 두 번째 범위를 입력합니다.

Excel에서 INDEX 함수 만들기

INDEX 함수는 여러 형식이 있는 Excel의 몇 가지 함수 중 하나입니다. 함수에는 배열 형식과 참조 형식이 있습니다. 배열 형식은 데이터베이스 또는 데이터 테이블에서 데이터를 반환합니다. 참조 양식은 테이블에서 데이터의 셀 참조 또는 위치를 제공합니다.

이 자습서에서는 배열 형식을 사용하여 데이터베이스에서 이 공급업체에 대한 셀 참조가 아니라 티타늄 부품의 공급업체 이름을 찾습니다.

INDEX 함수를 생성하려면 다음 단계를 따르십시오.

1. 셀 F3 을 선택하여 활성 셀로 만듭니다. 이 셀은 중첩 함수가 입력되는 곳입니다.
2. 수식 으로 이동합니다.

   

3. 조회 및 참조 를 선택 하여 함수 드롭다운 목록을 엽니다.
4. INDEX 를 선택 하여 인수 선택 대화 상자 를 엽니다.
5. array,row_num,column_num을 선택합니다.
6. 확인 을 선택 하여 함수 인수 대화 상자를 엽니다. Mac용 Excel에서 수식 작성기가 열립니다.
7. 배열 텍스트 상자 에 커서를 놓습니다.
8. 워크시트에서 D6 ~ F11 셀을 강조 표시 하여 대화 상자에 범위를 입력합니다.
   함수 인수 대화 상자를 열어 둡니다. 공식이 완성되지 않았습니다. 아래 지침에 따라 수식을 완성합니다.
   

중첩 MATCH 함수 시작

한 함수를 다른 함수 안에 중첩할 때 두 번째 또는 중첩된 함수의 수식 작성기를 열어 필요한 인수를 입력할 수 없습니다. 중첩 함수는 첫 번째 함수의 인수 중 하나로 입력해야 합니다.

함수를 수동으로 입력할 때 함수의 인수는 쉼표로 서로 구분됩니다.

중첩된 MATCH 함수를 입력하기 위한 첫 번째 단계는 Lookup_value 인수를 입력하는 것입니다. Lookup_value는 데이터베이스에서 일치시킬 검색어의 위치 또는 셀 참조입니다.

Lookup_value는 하나의 검색 기준 또는 용어만 허용합니다. 여러 기준을 검색하려면 앰퍼샌드 기호(&)를 사용하여 두 개 이상의 셀 참조를 연결하거나 조인하여 Lookup_value를 확장합니다.

1. 함수 인수 대화 상자에서 Row_num 텍스트 상자에 커서 를 놓습니다.
2. 매치 (.
3. 셀 D3 을 선택하여 해당 셀 참조를 대화 상자에 입력합니다.
4. 셀 참조 D3 뒤에 & (앰퍼샌드)를 입력 하여 두 번째 셀 참조를 추가합니다.
5. 셀 E3 을 선택하여 두 번째 셀 참조를 입력합니다.
6. 셀 참조 E3 뒤에, (쉼표)를 입력 하여 MATCH 함수의 Lookup_value 인수 항목을 완료합니다.

   
   자습서의 마지막 단계에서 Lookup_values는 워크시트의 D3 및 E3 셀에 입력됩니다.

중첩된 MATCH 함수 완성

이 단계에서는 중첩된 MATCH 함수에 대한 Lookup_array 인수를 추가하는 방법을 다룹니다. Lookup_array는 자습서의 이전 단계에서 추가된 Lookup_value 인수를 찾기 위해 MATCH 함수가 검색하는 셀 범위입니다.

Lookup_array 인수에서 두 개의 검색 필드가 식별되었으므로 Lookup_array에 대해서도 동일한 작업을 수행해야 합니다. MATCH 함수는 지정된 각 용어에 대해 하나의 배열만 검색합니다. 여러 배열을 입력하려면 앰퍼샌드를 사용하여 배열을 함께 연결합니다.

1. Row_num 텍스트 상자 의 데이터 끝에 커서를 놓습니다. 커서는 현재 항목의 끝에서 쉼표 뒤에 나타납니다.
2. 범위를 입력하려면 워크시트에서 D6 ~ D11 셀을 강조 표시 합니다. 이 범위는 함수가 검색하는 첫 번째 배열입니다.
3. 셀 참조 D6:D11 뒤에 & (앰퍼샌드)를 입력 합니다. 이 기호는 함수가 두 개의 배열을 검색하도록 합니다.
4. 범위를 입력하려면 워크시트에서 셀 E6 에서 E11 까지를 강조 표시합니다. 이 범위는 함수가 검색하는 두 번째 배열입니다.
5. 셀 참조 E3 뒤에 , (쉼표)를 입력 하여 MATCH 함수의 Lookup_array 인수 항목을 완료합니다.

   

6. 자습서의 다음 단계를 위해 대화 상자를 열어 둡니다.

MATCH 유형 인수 추가

MATCH 함수 의 세 번째이자 마지막 인수는 Match_type 인수입니다. 이 인수는 Excel에 Lookup_value를 Lookup_array의 값과 일치시키는 방법을 알려줍니다. 사용 가능한 선택 사항은 1, 0 또는 -1입니다.

이 인수는 선택적입니다. 생략하면 함수는 기본값 1을 사용합니다.

• Match_type = 1이거나 생략된 경우 MATCH는 Lookup_value보다 작거나 같은 가장 큰 값을 찾습니다. Lookup_array 데이터는 오름차순으로 정렬되어야 합니다.
• Match_type = 0이면 MATCH는 Lookup_value와 동일한 첫 번째 값을 찾습니다. Lookup_array 데이터는 임의의 순서로 정렬할 수 있습니다.
• Match_type = -1이면 MATCH는 Lookup_value보다 크거나 같은 가장 작은 값을 찾습니다. Lookup_array 데이터는 내림차순으로 정렬되어야 합니다.

INDEX 함수의 Row_num 줄에 이전 단계에서 입력한 쉼표 뒤에 다음 단계를 입력합니다.

1. Row_num 텍스트 상자 에서 쉼표 뒤에 0 (영)을 입력 합니다. 이 숫자로 인해 중첩 함수는 D3 및 E3 셀에 입력된 용어와 정확히 일치하는 항목을 반환합니다.
2. ) (닫는 대괄호)를 입력 하여 MATCH 함수를 완성합니다.

   

3. 자습서의 다음 단계를 위해 대화 상자를 열어 둡니다.

INDEX 기능 종료

MATCH 기능이 완료되었습니다. 대화 상자의 Column_num 텍스트 상자로 이동하여 INDEX 함수의 마지막 인수를 입력할 차례입니다. 이 인수는 열 번호가 D6에서 F11 범위에 있음을 Excel에 알려줍니다. 이 범위는 함수가 반환하는 정보를 찾는 곳입니다. 이 경우 티타늄 위젯 공급업체입니다.

1. Column_num 텍스트 상자 에 커서를 놓습니다.
2. 3 (숫자 3)을 입력 합니다. 이 숫자는 D6에서 F11까지 범위의 세 번째 열에서 데이터를 찾도록 수식에 지시합니다.

   

3. 자습서의 다음 단계를 위해 대화 상자를 열어 둡니다.

배열 수식 만들기

대화 상자를 닫기 전에 중첩 함수를 배열 수식으로 바꿉니다. 이 배열을 사용하면 함수가 데이터 테이블에서 여러 용어를 검색할 수 있습니다. 이 자습서에서는 1열의 위젯과 2열의 티타늄이라는 두 용어가 일치합니다.

Excel에서 배열 수식을 만들려면 CTRL, SHIFT 및 ENTER 키를 동시에 누르십시오. 누르면 함수가 중괄호로 둘러싸여 함수가 이제 배열임을 나타냅니다.

1. 확인 을 선택 하여 대화 상자를 닫습니다. Mac용 Excel에서 완료 를 선택 합니다.
2. F3 셀을 선택 하여 수식을 본 다음 수식 입력줄의 수식 끝에 커서를 놓습니다.
3. 수식을 배열로 변환하려면 CTRL + SHIFT + ENTER 를 누릅니다.
4. F3 셀에 #N/A 오류가 나타납니다. 함수가 입력된 셀입니다.
5. 셀 D3과 E3이 비어 있기 때문에 셀 F3에 #N/A 오류가 나타납니다. D3 및 E3은 함수가 Lookup_value를 찾는 셀입니다. 이 두 셀에 데이터가 추가되면 오류가 데이터베이스의 정보로 대체됩니다.

   

검색 기준 추가

마지막 단계는 검색어를 워크시트에 추가하는 것입니다. 이 단계는 열 1의 위젯과 열 2의 티타늄이라는 용어를 일치시킵니다.

수식이 데이터베이스의 해당 열에서 두 용어에 대해 일치하는 항목을 찾으면 세 번째 열의 값을 반환합니다.

1. D3 셀을 선택 합니다.
2. 위젯 을 입력 합니다.
3. E3 셀을 선택 합니다.
4. Titanium 을 입력 하고 Enter 키 를 누릅니다.
5. 공급업체 이름인 Widgets Inc.가 셀 F3에 나타납니다. 이것은 Titanium Widgets를 판매하는 유일한 공급업체입니다.
6. F3 셀을 선택 합니다. 워크시트 위의 수식 입력줄에 함수가 나타납니다. 이 예에서는 티타늄 부품 공급업체가 한 곳뿐입니다. 공급업체가 둘 이상인 경우 데이터베이스의 첫 번째로 나열된 공급업체가 함수에 의해 반환됩니다.
   
{=INDEX(D6:F11,MATCH(D3&E3,D6:D11&E6:E11,0),3)}