INDEX 및 MATCH가 Excel에서 VLOOKUP보다 나은 이유

VLOOKUP(영문 VLOOKUP, "vertical lookup function"의 약자)의 기본 기능 사용법에 대해 초보자를 위해 이전에 설명했습니다. 그리고 숙련된 사용자에게는 몇 가지 더 복잡한 공식이 표시되었습니다.

그리고 이 기사에서는 수직 검색을 사용하는 다른 방법에 대한 정보를 제공하려고 합니다.

"이것이 왜 필요한가?"라고 궁금해 할 수 있습니다. 그리고 이것은 가능한 모든 검색 방법을 표시하기 위해 필요합니다. 또한 수많은 VLOOKUP 제한으로 인해 원하는 결과를 얻지 못하는 경우가 많습니다. 이와 관련하여 INDEX( ) MATCH( )는 훨씬 더 기능적이고 다양하며 제한 사항도 적습니다.

기본 인덱스 매치

이 가이드의 목적은 이 기능이 얼마나 좋은지 보여주는 것이므로, 작동 원리에 대한 기본 정보를 살펴보겠습니다. 그리고 예제를 보여주고 왜 VLOOKUP()보다 나은지 생각해보도록 하겠습니다.

INDEX 함수 구문 및 사용법

이 기능은 컬럼 또는 라인 번호를 기반으로 지정된 검색 영역 중에서 원하는 값을 찾는 데 도움이 됩니다. 구문 :

=INDEX(배열, 행 번호, 열 번호):

• 배열 – 검색이 수행될 영역.
• 줄 번호 – 지정된 배열에서 검색할 줄 번호입니다. 행 번호를 알 수 없는 경우 열 번호를 지정해야 합니다.
• 열 번호 – 지정된 배열에서 찾을 열 번호입니다. 값을 알 수 없는 경우 행 번호가 필요합니다.

간단한 공식의 예:

= 인덱스(A1:S10,2,3)

이 기능은 A1에서 C10까지의 범위에서 검색합니다. 숫자는 원하는 값을 표시할 행(2)과 열(3)을 나타냅니다. 결과는 셀 C2가 됩니다.

아주 간단하죠? 그러나 실제 문서로 작업할 때 열 번호나 셀에 대한 정보를 얻을 가능성은 거의 없습니다. 이것이 MATCH() 함수의 목적입니다.

MATCH 함수 구문 및 사용법

MATCH() 함수는 원하는 값을 검색하고 지정된 검색 영역에 대략적인 숫자를 표시합니다.

searchpos() 구문은 다음과 같습니다.

=MATCH(검색할 값, 검색할 배열, 일치 유형)

• 검색 값 - 찾을 숫자 또는 텍스트
• 검색된 배열 - 검색이 수행될 영역.
• 일치 유형 – 정확한 값을 찾을지 아니면 가장 가까운 값을 찾을지 지정합니다.
  • 1(또는 지정된 값 없음) – 지정된 값과 같거나 작은 가장 큰 값을 반환합니다.
  • 0 – 검색된 값과 정확히 일치하는 항목을 표시합니다. INDEX() MATCH() 조합에서 거의 항상 정확한 일치가 필요하므로 0을 작성합니다.
  • -1 – 수식에 지정된 값보다 크거나 같은 가장 작은 값을 표시합니다. 정렬은 내림차순으로 수행됩니다.

예를 들어, B1:B3 범위에 New York, Paris, London이 등록됩니다. 아래 공식은 런던이 목록에서 세 번째이기 때문에 숫자 3을 표시합니다.

=EXPOSE(런던,B1:B3,0)

INDEX MATCH 기능을 사용하는 방법 

이러한 기능의 공동 작업이 구축되는 원리를 이미 이해하기 시작했을 것입니다. 한마디로 그럼 INDEX()는 지정된 행과 열 중에서 원하는 값을 검색합니다. 그리고 MATCH()는 다음 값의 수를 보여줍니다.

=INDEX(값이 반환되는 열, MATCH(검색할 값, 검색할 열, 0))

어떻게 작동하는지 아직도 이해하기 어려우신가요? 아마도 예가 더 잘 설명될 것입니다. 세계 수도와 인구 목록이 있다고 가정합니다.

일본의 수도와 같은 특정 수도의 인구 규모를 알아내기 위해 다음 공식을 사용합니다.

=INDEX(C2:C10, MATCH(일본, A2:A10,0))

설명 :

• MATCH() 함수는 A2:A10 배열에서 "Japan" 값을 찾고 숫자 3을 반환합니다. Japan은 목록의 세 번째 값이기 때문입니다. 
• 이 수치는줄 번호” INDEX() 수식에서 이 행의 값을 인쇄하도록 함수에 지시합니다.

따라서 위의 공식은 표준 공식이됩니다. 색인(C2:C10,3). 수식은 셀 C2에서 C10까지 검색하고 이 범위의 세 번째 셀(C4)에서 데이터를 반환합니다. 카운트다운은 두 번째 행에서 시작되기 때문입니다.

공식에 도시 이름을 처방하고 싶지 않으세요? 그런 다음 F1과 같이 임의의 셀에 작성하고 MATCH() 수식에서 참조로 사용합니다. 그리고 동적 검색 공식으로 끝납니다.

=ИНДЕКС(С2:С10, ПОИСКПОЗ( )(F1,A2:A10,0))

중요! 줄 수 정렬 INDEX()는 행의 수와 같아야 합니다. 고려된 배열 MATCH()에서 그렇지 않으면 잘못된 결과를 얻게 됩니다.

잠시만 VLOOKUP() 수식을 사용하지 않는 이유는 무엇입니까?

=VLOOKUP(F1, A2:C10, 3, 거짓)

 INDEX MATCH의 이러한 모든 복잡성을 파악하는 데 시간을 낭비하는 이유가 무엇입니까?

이 경우 어떤 기능을 사용해도 상관 없습니다. 이것은 INDEX() 및 MATCH() 함수가 함께 작동하는 방식을 이해하기 위한 예일 뿐입니다. 다른 예는 VLOOKUP이 무력한 상황에서 이러한 기능이 무엇을 할 수 있는지 보여줍니다. 

인덱스 매치 또는 VLOOKUP

사용할 검색 공식을 결정할 때 많은 사람들이 INDEX() 및 MATCH()가 VLOOKUP보다 훨씬 우수하다는 데 동의합니다. 그러나 많은 사람들이 여전히 VLOOKUP()을 사용합니다. 첫째, VLOOKUP()이 더 간단하고 둘째, 사용자가 INDEX() 및 MATCH() 작업의 모든 이점을 완전히 이해하지 못합니다. 이 지식이 없으면 아무도 복잡한 시스템을 연구하는 데 시간을 할애하는 데 동의하지 않을 것입니다.

VLOOKUP()에 비해 INDEX() 및 MATCH()의 주요 이점은 다음과 같습니다.

• 오른쪽에서 왼쪽으로 검색합니다. VLOOKUP()은 오른쪽에서 왼쪽으로 검색할 수 없으므로 찾고 있는 값은 항상 테이블의 가장 왼쪽 열에 있어야 합니다. 그러나 INDEX() 및 MATCH()는 문제 없이 이를 처리할 수 있습니다. 이 기사에서는 실제로 어떻게 보이는지 알려줄 것입니다. 왼쪽에서 원하는 값을 찾는 방법입니다.

1. 컬럼의 안전한 추가 또는 제거. VLOOKUP()이 성공하려면 정확한 열 번호가 필요하기 때문에 VLOOKUP() 수식은 열을 제거하거나 추가할 때 잘못된 결과를 표시합니다. 당연히 열이 추가되거나 제거되면 숫자도 변경됩니다. 

그리고 INDEX() 및 MATCH() 수식에서는 개별 열이 아닌 열 범위가 지정됩니다. 따라서 매번 수식을 업데이트하지 않고도 열을 안전하게 추가 및 제거할 수 있습니다.

1. 검색량 제한 없음. VLOOKUP()을 사용할 때 검색 기준의 총 수는 255자를 초과해서는 안 됩니다. 그렇지 않으면 #VALUE! 따라서 데이터에 많은 수의 문자가 포함된 경우 INDEX() 및 MATCH()가 가장 좋은 옵션입니다.
2. 높은 처리 속도. 테이블이 상대적으로 작으면 차이를 느끼지 못할 것입니다. 그러나 테이블에 수백 또는 수천 개의 행이 포함되어 있고 그에 따라 수백 수천 개의 수식이 있는 경우 INDEX() 및 MATCH()가 VLOOKUP()보다 훨씬 빠르게 대처합니다. 사실 Excel은 전체 테이블을 처리하는 대신 수식에 지정된 열만 처리합니다. 

VLOOKUP()의 성능 영향은 워크시트에 VLOOKUP() 및 SUM()과 같은 수식이 많이 포함되어 있는 경우 특히 두드러집니다. 배열의 각 값을 구문 분석하려면 VLOOKUP() 함수를 별도로 확인해야 합니다. 따라서 Excel은 엄청난 양의 정보를 처리해야 하며 이로 인해 작업 속도가 크게 느려집니다.

공식 예 

우리는 이미 이러한 기능의 유용성을 파악했으므로 가장 흥미로운 부분인 지식의 실제 적용으로 넘어갈 수 있습니다.

오른쪽에서 왼쪽으로 검색하는 공식

이미 언급했듯이 VLOOKUP은 이러한 형식의 검색을 수행할 수 없습니다. 따라서 원하는 값이 가장 왼쪽 열에 없으면 VLOOKUP()은 결과를 생성하지 않습니다. INDEX() 및 MATCH() 함수는 더 다양하며 값의 위치가 작동하는 데 큰 역할을 하지 않습니다.

예를 들어, 우리는 테이블의 왼쪽에 순위 열을 추가하고 우리 나라의 수도가 차지하는 인구 순위를 알아 내려고 노력할 것입니다.

셀 G1에 찾을 값을 작성한 다음 다음 수식을 사용하여 C1:C10 범위에서 검색하고 A2:A10에서 해당 값을 반환합니다.

=ИНДЕКС(А2:А10, ПОИСКПОЗ(G1,C1:C10,0))

즉각적인. 여러 셀에 이 수식을 사용하려는 경우 절대 주소 지정을 사용하여 범위를 수정해야 합니다(예: $A$2: $A$10 및 $C$2: 4C$10).

인덱스 더 많이 노출 더 많이 노출  열과 행에서 검색하려면

위의 예에서 VLOOKUP() 대신 이러한 함수를 사용하여 미리 정의된 행 범위에서 값을 반환했습니다. 그러나 행렬 또는 양면 검색을 수행해야 하는 경우에는 어떻게 해야 합니까?

복잡하게 들리지만 이러한 계산의 공식은 표준 INDEX() MATCH() 공식과 유사하지만 한 가지 차이점이 있습니다. MATCH() 수식은 두 번 사용해야 합니다. 처음으로 행 번호를 가져오고 두 번째로 열 번호를 가져옵니다.

=INDEX(배열, MATCH(수직 검색값, 검색 열, 0), MATCH(가로 검색 값, 검색 행, 0))

아래 표를 보고 공식을 만들어 봅시다. 인덱스() 익스프레스() 익스프레스() 선택한 연도에 대한 특정 국가의 인구 통계를 표시하기 위해.

대상 국가는 셀 G1(수직 조회)에 있고 대상 연도는 셀 G2(수평 조회)에 있습니다. 공식은 다음과 같습니다.

=ИНДЕКС(B2:D11, ПОИСКПОЗ(G1,A2:A11,0), ПОИСКПОЗ(G2,B1:D1,0))

이 공식의 작동 원리

다른 복잡한 공식과 마찬가지로 개별 방정식으로 분해하여 이해하기 쉽습니다. 그러면 각 개별 기능이 수행하는 작업을 이해할 수 있습니다.

• 매치(G1,A2:A11,0) – A1:A2 범위에서 값(G11)을 찾고 이 값의 번호를 표시합니다. 이 경우에는 2입니다.
• 검색(G2,B1:D1,0) – B2:D1 범위에서 값(G1)을 찾습니다. 이 경우 결과는 3입니다.

찾은 행 및 열 번호는 INDEX() 수식의 해당 값으로 전송됩니다.

= 인덱스(B2:D11,2,3)

결과적으로 B2:D3 범위에서 2행과 11열이 교차하는 셀에 값이 있습니다. 그리고 수식은 셀 D3에 있는 원하는 값을 보여줍니다.

INDEX 및 MATCH를 사용하여 여러 조건으로 검색

VLOOKUP()에 대한 가이드를 읽었다면 아마도 여러 검색 공식을 시도했을 것입니다. 그러나 이 검색 방법에는 보조 열을 추가해야 하는 한 가지 중요한 제한이 있습니다.

하지만 좋은 소식은 INDEX() 및 MATCH()를 사용하면 워크시트를 편집하거나 변경할 필요 없이 여러 조건을 검색할 수 있습니다.

다음은 INDEX() MATCH()에 대한 일반적인 다중 조건 검색 공식입니다.

{=ИНДЕКС(диапазон поиска, ПОИСКПОЗ(1,условие1=диапазон1)*(условвие2=диапазон2),0))}

참고: 이 수식은 키보드 단축키와 함께 사용해야 합니다. Ctrl+Shift+Enter.

다음 2가지 조건을 기반으로 원하는 값을 찾아야 한다고 가정합니다. 사는 사람 и 생성물.

이를 위해서는 다음 공식이 필요합니다.

=ИНДЕКС(С2:С10, ПОИСКПОЗ(1,(F1=A2:A10)*(F2=B1:B10),0))

이 공식에서 C2:C10은 검색이 수행될 범위이고, F1 – 이 조건, A2:A10 — 조건을 비교할 범위, F2 – 조건 2, V2 : V10 – 조건 2의 비교 범위.

수식으로 작업이 끝날 때 조합을 누르는 것을 잊지 마십시오. Ctrl + Shift + Enter – Excel은 다음 예와 같이 중괄호로 수식을 자동으로 닫습니다.

작업에 배열 수식을 사용하지 않으려면 수식에 다른 INDEX()를 추가하고 Enter 키를 누르면 예와 같이 표시됩니다.

이 공식의 작동 방식

이 공식은 표준 INDEX() MATCH() 공식과 같은 방식으로 작동합니다. 여러 조건을 검색하려면 정확하고 잘못된 개별 조건을 나타내는 False 및 True 조건을 여러 개 생성하기만 하면 됩니다. 그런 다음 이러한 조건은 배열의 모든 해당 요소에 적용됩니다. 수식은 False 및 True 인수를 각각 0 및 1로 변환하고 문자열에서 찾은 일치하는 값이 1인 배열을 출력합니다. MATCH()는 1과 일치하는 첫 번째 값을 찾아 INDEX() 수식에 전달합니다. 그리고 차례로 원하는 열의 지정된 줄에 이미 원하는 값을 반환합니다.

배열이 없는 수식은 자체적으로 처리하는 INDEX() 기능에 따라 다릅니다. 수식의 두 번째 INDEX()는 거짓(0)과 일치하므로 해당 값이 있는 전체 배열을 MATCH() 수식에 전달합니다. 

이것은 이 공식 뒤에 있는 논리에 대한 다소 긴 설명입니다. 자세한 내용은 "여러 조건의 INDEX MATCH".

INDEX 및 MATCH의 AVERAGE, MAX 및 MIN

Excel에는 평균, 최대값 및 최소값을 찾기 위한 고유한 특수 기능이 있습니다. 그러나 해당 값과 연결된 셀에서 데이터를 가져오려면 어떻게 해야 합니까? 이 경우 AVERAGE, MAX 및 MIN은 INDEX 및 MATCH와 함께 사용해야 합니다.

인덱스 매치와 MAX

열 D에서 가장 큰 값을 찾아 열 C에 표시하려면 다음 공식을 사용하십시오. 

=ИНДЕКС(С2:С10, ПОИСКПОЗ(МАКС(D2:D10),D2:D10,0))

인덱스 매치와 MIN

열 D에서 가장 작은 값을 찾아 열 C에 표시하려면 다음 수식을 사용합니다.

=ИНДЕКС(С2:С10,ПОИСКПОЗ(МИН(D2:D10),D2:D10,0))

검색 색인 및 뱀

D 열에서 평균 값을 찾고 이 값을 C에 표시하려면 다음을 수행합니다.

=ИНДЕКС(С2:С10,ПОИСКПОЗ(СРЗНАЧ(D2:D10),D2:D10,-1))

데이터 작성 방법에 따라 MATCH()의 세 번째 인수는 1, 0 또는 -1입니다.

• 열이 오름차순으로 정렬된 경우 1을 설정합니다(수식은 평균값보다 작거나 같은 최대값을 계산합니다).
• 정렬이 내림차순이면 -1입니다(수식은 평균보다 크거나 같은 최소값을 출력합니다).
• 조회 배열에 평균과 정확히 같은 값이 포함되어 있으면 0으로 설정합니다. 

 이 예에서 인구는 내림차순으로 정렬되므로 -1을 넣습니다. 그리고 결과는 인구 값(13,189)이 평균 값(000)에 가장 가깝기 때문에 도쿄입니다.

VLOOKUP()도 이러한 계산을 수행할 수 있지만 배열 수식으로만 수행할 수 있습니다. AVERAGE, MIN 및 MAX가 있는 VLOOKUP.

인덱스 일치 및 ESND/IFERROR

수식이 원하는 값을 찾지 못하면 오류가 발생한다는 사실을 이미 눈치채셨을 것입니다. # N / A. 표준 오류 메시지를 보다 유익한 것으로 바꿀 수 있습니다. 예를 들어 수식에 인수를 설정합니다. XNUMX일에는:

=ЕСНД(ИНДЕКС(С2:С10,ПОИСКПОЗ(F1,A2:A10,0)),значение не найдено)

이 수식을 사용하여 테이블에 없는 데이터를 입력하면 양식에 지정된 메시지가 표시됩니다.

모든 오류를 포착하려면 다음을 제외하고 XNUMX일에는 사용할 수 있습니다 오류:

=IFERROR(INDEX(C2:C10,MATCH(F1,A2:A10,0)), "문제가 발생했습니다!")

그러나 이러한 방식으로 오류를 마스킹하는 것은 좋은 생각이 아님을 기억하십시오. 표준 오류는 공식의 위반을 보고하기 때문입니다.

INDEX MATCH() 함수 사용에 대한 가이드가 도움이 되었기를 바랍니다.