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

알아야 할 사항

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

이 문서에서는 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의 몇 안되는 함수 중 하나입니다. 함수에는 배열 형식과 참조 형식이 있습니다. 배열 양식은 데이터베이스 또는 데이터 테이블에서 데이터를 반환합니다. 참조 양식은 표에 있는 데이터의 위치 또는 셀 참조를 제공합니다.

이 튜토리얼에서 Array Form은 데이터베이스에서 이 공급자에 대한 셀 참조가 아니라 티타늄 위젯에 대한 공급자 이름을 찾는 데 사용됩니다.

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

1. F3 셀 선택 활성 셀로 만듭니다. 중첩된 함수가 입력될 셀입니다.

2. 수식으로 이동 .

   

3. 조회 및 참조 선택 기능 드롭다운 목록을 엽니다.

4. 색인 선택 인수 선택 열기 대화 상자.

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. MATCH( .

3. D3 셀 선택 대화 상자에 해당 셀 참조를 입력합니다.

4. & 입력 (앰퍼샌드) 셀 참조 D3 뒤 두 번째 셀 참조를 추가합니다.

5. E3 셀 선택 두 번째 셀 참조를 입력합니다.

6. , 입력 (쉼표) 셀 참조 E3 다음에 MATCH 함수의 Lookup_value 인수 입력을 완료합니다.

   

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

중첩된 MATCH 기능 완료

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

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 입니다. 논쟁. 이 인수는 Lookup_value를 Lookup_array의 값과 일치시키는 방법을 Excel에 알려줍니다. 사용 가능한 선택 사항은 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. 0 입력 (0) Row_num의 쉼표 뒤 텍스트 상자. 이 숫자를 사용하면 중첩 함수가 D3 및 E3 셀에 입력한 용어와 정확히 일치하는 항목을 반환합니다.

2. ) 입력 (닫는 둥근 괄호) MATCH 기능을 완료합니다.

   

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

INDEX 기능 종료

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

1. Column_num에 커서를 놓습니다. 텍스트 상자.

2. 3 입력 (숫자 3). 이 숫자는 D6에서 F11까지 범위의 세 번째 열에서 데이터를 찾도록 공식에 지시합니다.

   

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

배열 수식 생성

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

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

1. 확인 을 선택하십시오. 대화 상자를 닫습니다. Mac용 Excel에서 완료를 선택합니다. .

2. F3 셀 선택 수식을 보려면 수식 입력줄의 수식 끝에 커서를 놓습니다.

3. 수식을 배열로 변환하려면 CTRL을 누릅니다. +쉬프트 +엔터 .

4. A #N/A F3 셀에 오류가 나타납니다. 함수가 입력된 셀입니다.

5. 셀 D3과 E3이 비어 있기 때문에 #N/A 오류가 F3 셀에 나타납니다. D3 및 E3은 함수가 Lookup_value를 찾기 위해 찾는 셀입니다. 이 두 셀에 데이터가 추가되면 오류가 데이터베이스의 정보로 대체됩니다.

   

검색 기준 추가

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

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

1. D3 셀 선택 .

2. 위젯 입력 .

3. E3 셀 선택 .

4. 티타늄 유형 , Enter 키를 누릅니다. .

5. 공급자 이름인 Widgets Inc.가 F3 셀에 나타납니다. 이것은 티타늄 위젯을 판매하는 목록에 있는 유일한 공급업체입니다.

6. F3 셀 선택 . 함수는 워크시트 위의 수식 입력줄에 나타납니다.

   {=INDEX(D6:F11,MATCH(D3&E3,D6:D11&E6:E11,0),3)}
   
   

   이 예에서는 티타늄 위젯에 대한 공급자가 하나뿐입니다. 공급자가 두 명 이상인 경우 데이터베이스에 처음 나열된 공급자가 함수에 의해 반환됩니다.