Excel에서 여러 조건으로 INDEX MATCH 사용하기

몇 년 전, 여러 지역의 판매 성과를 비교해야 했던 마케팅 캠페인 분석 작업을 한 적이 있습니다. 데이터는 여러 Excel 시트에 흩어져 있었고, 특정 제품 판매 수치를 단일 요약 보고서로 가져와야 했습니다. 처음에는 수동으로 검색하고 데이터를 복사해 보았지만, 생각만큼 쉽지 않았습니다. 한 줄이라도 잘못되면 전체 보고서가 무너질 수 있었기 때문입니다.

그때 제가 발견한 것이 INDEX MATCH였습니다. 수식을 제대로 맞추기까지 몇 번의 시도가 필요했지만, 제가 필요한 정확한 숫자를 얼마나 쉽게 찾아서 가져올 수 있는지 알게 되자 제日常工作의 일부가 되었습니다. 오랫동안 이것은 복잡한 조회를 위한 제 주된 해결책이었습니다.

오늘날 INDEX MATCH는 여전히 강력한 기술이지만, 환경은 변화하고 있습니다. AI 기반 도구는 이제 단 하나의 수식도 작성하지 않고 동일한 결과를 얻을 수 있는 방법을 제공합니다. 이 글에서는 기존 INDEX 및 MATCH 함수를 사용하여 다중 조건 조회를 숙달하는 방법을 설명하고, Excelmatic과 같은 현대적인 도구가 어떻게 훨씬 더 짧은 시간에 동일한 결과를 달성할 수 있는지 보여드리겠습니다.

INDEX MATCH 복습

INDEX MATCH는 고급 조회를 수행하기 위해 함께 작동하는 두 Excel 함수의 조합을 간략하게 말하는 방식입니다. 이를 INDEX(MATCH())라고 부를 수도 있지만, 이 글에서는 INDEX MATCH를 사용하겠습니다. 이제 각각을 차례로 살펴보겠습니다:

INDEX() 함수는 지정된 범위 내에서 셀의 위치를 기반으로 값을 검색합니다. 구문은 다음과 같습니다:

=INDEX(array, row_num, [column_num])

여기서:

• array는 값을 검색하려는 셀 범위입니다.
• row_num은 값을 반환할 배열 내의 행 번호입니다.
• column_num (선택 사항)은 값을 반환할 배열 내의 열 번호입니다.

MATCH() 함수는 범위 내에서 값의 상대적 위치를 식별합니다. 구문은 다음과 같습니다:

=MATCH(lookup_value, lookup_array, [match_type])

여기서:

• lookup_value는 찾으려는 값입니다.
• lookup_array는 함수가 값을 검색하는 범위입니다.
• match_type은 선택 사항입니다. 0은 정확히 일치하는 값을 찾으며 우리가 가장 자주 사용할 것입니다.

INDEX()와 MATCH()를 결합하는 방법

MATCH()를 INDEX() 내에 중첩하면 동적 조회를 생성할 수 있습니다. 예를 들어 이해해 보겠습니다: 데이터 세트에서 "David Wilson"의 부서를 찾고 싶다고 가정해 보세요. INDEX()에서 행 번호를 하드 코딩하는 대신 MATCH()를 사용하여 결정합니다:

=INDEX(C2:C6, MATCH("David Wilson", A2:A6, 0))

위 수식에서 MATCH("David Wilson", A2:A6, 0)는 4를 반환하며, 이것이 행 위치입니다. 그리고 INDEX(C2:C6, 4)는 범위 C2:C6의 4번째 행에서 값을 검색하며, 그것은 "Seattle"입니다.

INDEX MATCH vs. VLOOKUP()

이제 INDEX()와 MATCH()가 어떻게 작동하는지 알았으니, 왜 INDEX MATCH가 더 일반적인 VLOOKUP()보다 종종 더 나은 선택인지 알아보겠습니다.

• 유연성: 조회 열이 왼쪽 첫 번째 열이어야 하는 VLOOKUP()과 달리, INDEX MATCH는 위치에 관계없이 모든 열에서 데이터를 검색할 수 있습니다.
• 안정성: VLOOKUP()을 사용하는 수식은 정적인 열 인덱스 번호에 의존하기 때문에 열이 삽입되거나 삭제되면 깨질 수 있습니다. INDEX MATCH는 반환 열을 직접 참조하기 때문에 더 강력합니다.
• 성능: INDEX MATCH는 대규모 데이터 세트에서 더 빠를 수 있습니다. 왜냐하면 조회 및 반환 열만 처리하는 반면, VLOOKUP()은 더 큰 데이터 배열을 선택할 수 있기 때문입니다.
• 명확성: INDEX MATCH를 사용하면 열 번호를 수동으로 셀 필요가 없습니다. 조회 열과 반환 열을 지정하면 수식을 더 쉽게 읽을 수 있습니다.

다중 조건을 가진 INDEX MATCH: 기존 방식

저는 종종 중복 항목을 포함하는 데이터 세트를 작업해야 하며, 하나 이상의 조건을 기반으로 값을 찾는 것은 일반적인 작업입니다. INDEX MATCH는 이러한 시나리오를 처리할 수 있지만, 더 고급 수식 구조가 필요합니다. 단계별로 설명해 드리겠습니다.

다중 조건을 위한 데이터 설정

먼저 데이터 세트를 만들고 각 열에 대한 명확한 헤더가 있는 표로 잘 구성되어 있는지 확인하세요. 각 행은 고유한 레코드를 나타내고 각 열은 특정 데이터 속성을 포함해야 합니다.

예를 들어, 다음은 샘플 데이터 세트입니다:

다중 조건을 위한 수식 작성

표준 INDEX MATCH 수식은 하나의 기준으로만 작동합니다. 여러 기준을 처리하려면 이를 배열 수식으로 적응해야 합니다.

기본 구문은 다음과 같습니다:

{=INDEX(return_range, MATCH(1, (criteria1=range1) * (criteria2=range2), 0))}

여기서:

• return_range는 값이 반환될 범위입니다.
• criteria1, criteria2는 충족되어야 할 조건입니다.
• range1, range2는 기준을 확인할 열 범위입니다.
• * 연산자는 AND 조건 역할을 하며, 1과 0의 배열을 생성합니다. MATCH(1, ...)는 그런 다음 모든 조건이 참(결과적으로 1)인 첫 번째 행을 찾습니다.

이를 적용하는 두 가지 방법을 살펴보겠습니다.

방법 1: 도우미 열 사용

배열 수식에 익숙하지 않다면, 도우미 열을 사용하면 프로세스를 단순화할 수 있습니다. 이것은 여러 기준을 단일 열로 결합한 다음 표준 INDEX MATCH 조회에 사용함으로써 작동합니다. 예를 들어, 직원의 이름과 부서를 기반으로 역할을 찾으려면 "이름"과 "부서"를 연결하는 도우미 열을 만들 수 있습니다:

=A2&B2

이 도우미 열은 제 INDEX MATCH 수식을 단순화합니다. 복잡한 배열 수식 대신 훨씬 더 간단한 접근 방식으로 도우미 열을 참조할 수 있습니다:

=INDEX(D2:D11, MATCH("AliceHR", E2:E11, 0))

방법 2: 배열 수식 사용

시트에 추가 열을 넣고 싶지 않다면, 배열 수식을 사용하여 여러 기준을 직접 처리할 수 있습니다. 예를 들어, 도우미 열 없이 HR 부서의 Alice 급여를 찾는 방법은 다음과 같습니다.

다음 수식을 사용합니다:

=INDEX(D2:D11, MATCH(1, (F4=A2:A11) * (F5=B2:B11), 0))

• (F4=A2:A11)는 이름 열에서 "Alice"를 확인합니다.
• (F5=B2:B11)는 부서 열에서 "HR"을 확인합니다.
• * 연산자는 MATCH가 두 조건이 모두 TRUE인 행만 찾도록 합니다.

중요: 이전 버전의 Excel에서는 Ctrl+Shift+Enter를 눌러 이를 배열 수식으로 확인해야 합니다. Excel은 자동으로 주위에 중괄호 {}를 추가합니다. 동적 배열이 있는 최신 버전에서는 Enter만 누르면 됩니다.

더 간단하고 빠른 방법: AI를 사용한 다중 조건 조회

배열 수식은 강력하지만 작성하기 복잡하고 디버깅하기 어려울 수 있습니다. 바로 여기서 Excelmatic과 같은 AI 도구가 판도를 바꿉니다.

Excelmatic은 일반 언어를 이해하는 Excel AI 에이전트입니다. 수식을 구축하는 대신, 그냥 질문하기만 하면 됩니다.

동일한 문제—HR 부서의 Alice 급여 찾기—를 해결하기 위한 Excelmatic의 프로세스는 다음과 같습니다:

1. Excel 파일을 업로드하여 Excelmatic 플랫폼으로 가져옵니다.
2. 채팅 인터페이스에 질문을 합니다. 마치 동료에게 물어보듯이:

   Alice라는 이름의 직원이 HR 부서에서 받는 급여는 얼마인가요?

Excelmatic은 즉시 데이터를 스캔하고, 여러 기준("Alice" 및 "HR")을 이해하고, 조회를 수행하며, 답변을 제공합니다.

대비는 분명합니다:

• INDEX MATCH 배열 수식: =INDEX(D2:D11, MATCH(1, ("Alice"=A2:A11) * ("HR"=B2:B11), 0))
  • INDEX, MATCH, 배열 구문 및 논리 연산자에 대한 지식이 필요합니다.
• 범위가 올바르지 않거나 Ctrl+Shift+Enter를 잊어버리면 오류가 발생하기 쉽습니다.
• Excelmatic: "Alice가 HR에서 받는 급여는 얼마인가요?"
  • 수식 지식이 필요하지 않습니다.
  • 직관적이고 빠르며 오류가 없습니다.

이 AI 기반 접근 방식은 수식을 구축하는 방법에 대한 기술적 세부 사항이 아니라, 답변을 원하는 질문에 집중할 수 있게 합니다.

다중 조건을 가진 INDEX MATCH의 고급 사용

INDEX MATCH로 훨씬 더 많은 작업을 수행할 수 있지만, 이러한 복잡한 시나리오도 AI 어시스턴트를 사용하면 놀랍도록 간단해집니다.

복잡한 조회를 위한 중첩 INDEX MATCH

때로는 행 과 열 모두에 대한 기준을 일치시켜야 합니다. 예를 들어, 다양한 지역에 걸친 제품 카테고리별 판매를 보여주는 이 데이터 세트에서 표준 조회는 작동하지 않습니다.

저는 동부 지역의 가구 판매량을 찾고 싶습니다. 이를 위해서는 행에서 "Furniture"를, 열에서 "East"를 일치시켜야 합니다. 이것은 중첩 INDEX MATCH 수식이 필요합니다:

=INDEX(B2:D4, MATCH(D6, A2:A4, 0), MATCH(D7, B1:D1, 0))

작동 방식은 다음과 같습니다:

• 주요 INDEX() 함수는 데이터 범위 B2:D4에서 찾습니다.
• 첫 번째 MATCH(D6, A2:A4, 0)는 "Furniture"에 대한 행 번호를 찾습니다.
• 두 번째 MATCH(D7, B1:D1, 0)는 "East"에 대한 열 번호를 찾습니다.

INDEX는 그런 다음 해당 행과 열의 교차점에 있는 값을 반환합니다: 450.

효과적이지만, 이 수식은 더욱 복잡합니다. Excelmatic을 사용하면 요청은 다시 단순한 질문입니다:

동부 지역의 가구 판매량은 얼마였나요?

AI는 2차원 조회를 자동으로 처리하여 중첩 수식을 구축하고 디버깅하는 것을 방지합니다.

일반적인 과제 및 문제 해결

제가 INDEX MATCH 사용을 시작했을 때, 몇 가지 오류를 겪었습니다. 다음은 그 오류들과 해결 방법, 그리고 AI 접근 방식이 어떻게 그것들을 완전히 피하도록 도와주는지 살펴보겠습니다.

INDEX MATCH 수식에서 오류 처리

#N/A 및 #VALUE!와 같은 오류는 흔합니다.

• #N/A 오류는 MATCH() 함수가 조회 값을 찾을 수 없을 때 발생합니다. 이것은 오타, 추가 공간(TRIM() 함수로 수정 가능), 또는 잘못된 범위 참조 때문일 수 있습니다.
• #VALUE! 오류는 다중 기준 배열 수식에서 Ctrl+Shift+Enter를 누르는 것을 잊어버리면(이전 Excel 버전에서) 자주 나타납니다.

문제 해결에서의 AI 이점

Excelmatic과 같은 도구를 사용하면 이러한 수식별 오류가 사라집니다. #N/A, #VALUE! 또는 #REF! 오류를 디버깅할 필요가 없습니다. 왜냐하면 수식을 작성하는 것이 아니기 때문입니다. 도구가 데이터를 찾을 수 없으면 암호 같은 오류 코드를 표시하는 대신 일반 언어로 알려줄 것입니다(예: "데이터 세트에서 'Alice'를 찾을 수 없습니다"). 이렇게 하면 데이터 분석의 전체 프로세스가 더 용서하기 쉽고 접근하기 쉬워집니다.

마무리

INDEX MATCH 숙달은 진지한 Excel 사용자에게 필수적인 과정입니다. 그것은 VLOOKUP을 훨씬 넘어서는 유연하고 강력한 조회를 수행할 수 있는 힘을 줍니다. 배열 수식과 중첩 조회를 구축하는 방법을 이해하는 것은 Excel이 어떻게 작동하는지에 대한 이해를 깊게 하는 가치 있는 기술입니다.

그러나 데이터 분석의 목표는 통찰력을 얻는 것이지, 수식을 작성하고 디버깅하는 데 시간을 보내는 것이 아닙니다. Excelmatic과 같은 현대적인 AI 도구는 스프레드시트 생산성에서 다음 진화를 나타냅니다. 그것들은 단순히 질문함으로써 바로 답변을 얻을 수 있게 합니다.

Excel 데이터 작업 방식을 변화시킬 준비가 되셨나요? 지금 Excelmatic을 사용해 보세요 그리고 AI 기반 다중 조건 조회의 힘을 경험해 보세요. 배울 수식 없이, 디버깅할 오류 없이, 단지 비즈니스 결정을 앞으로 나아가게 하는 즉각적인 통찰력 - 모든 기술적 복잡성을 Excelmatic이 처리하도록 하세요.

제 조언은 다음과 같습니다: 논리를 이해하기 위해 INDEX MATCH를 배우세요. 그러나 일상 업무를 위해서는 AI의 속도와 단순성을 받아들이세요. 데이터가 당신에게 말해주는 것에 집중할 수 있도록 기계가 구문을 처리하도록 하십시오.

INDEX MATCH FAQ

INDEX MATCH 대소문자 구분을 어떻게 처리하나요?

기본적으로 INDEX MATCH는 대소문자를 구분하지 않습니다. 대소문자를 구분하게 만들려면 배열 수식에서 EXACT 함수와 결합해야 합니다:

=INDEX(return_range, MATCH(TRUE, EXACT(lookup_value, lookup_range), 0))

Ctrl+Shift+Enter를 누르는 것을 기억하세요.

INDEX MATCH 수식에서 오류를 어떻게 처리하나요?

수식을 IFERROR 함수로 감싸서 오류 대신 사용자 지정 메시지를 반환하세요:

=IFERROR(INDEX(..., MATCH(...)), "찾을 수 없음")

다중 조건에 대해 INDEX MATCH와 XLOOKUP() 사용의 차이점은 무엇인가요?

XLOOKUP()은 Excel에서 INDEX MATCH를 많은 조회에서 대체할 수 있는 더 새롭고 사용자 친화적인 함수입니다. 다중 조건의 경우 XLOOKUP은 일반적으로 INDEX MATCH 배열 수식보다 더 간단합니다. 그러나 둘 모두 여전히 특정 수식 구문을 배우고 작성해야 합니다. Excelmatic과 같은 AI 도구는 둘 모두를 넘어서는 단계로, 수식을 전혀 작성할 필요를 없애고 대신 자연어에 의존합니다.