INDEX와 MATCH 결합하기

이제 조회에 필요한 두 가지 기능을 모두 알게 되었습니다. MATCH는 값이 어디에 있는지 찾고, INDEX는 특정 위치에 있는 값을 반환합니다.

이 둘을 결합하면 완전한 조회가 됩니다. MATCH가 행을 찾고, INDEX가 선택한 열에서 해당 행의 데이터를 가져옵니다.

패턴은 간단합니다. 행 번호가 들어가는 위치에 MATCH를 INDEX 안에 넣으면 됩니다.

앞으로 계속 사용하게 될 형식은 다음과 같습니다:

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

안쪽부터 읽어 보세요. MATCH가 먼저 실행되어 위치 번호를 반환합니다. 그 번호가 INDEX의 행 번호가 되고, INDEX는 반환 범위에서 해당 위치의 값을 가져옵니다.

반환 범위와 조회 범위는 대개 행 수가 같으므로 한 범위의 위치가 다른 범위의 위치와 맞아떨어집니다.

A열에 제품 이름이 있고 C열에 가격이 있는 표를 생각해 보세요. “체리”의 가격을 찾으려고 합니다.

먼저 MATCH가 체리를 찾습니다: =MATCH("Cherry", A2:A20, 0)은 예를 들어 3을 반환합니다.

그다음 INDEX가 이 3을 사용합니다: =INDEX(C2:C20, 3)은 C열의 3번째 행에 있는 가격을 반환합니다.

둘을 중첩하면 한 번에 결과를 얻을 수 있습니다: =INDEX(C2:C20, MATCH("Cherry", A2:A20, 0)).

학습할 때는 “체리”를 직접 지정해도 괜찮지만, 실제 수식에서는 대신 셀을 참조합니다. E1에 검색어를 입력하고 그 셀을 참조하세요.

=INDEX(C2:C20, MATCH(E1, A2:A20, 0))

이제 E1에 어떤 제품을 입력하든 해당 제품의 가격이 즉시 반환됩니다. 바나나를 입력하면 바나나의 가격이 표시되고, 대추야자를 입력하면 결과가 업데이트됩니다.

수식 하나가 입력 셀에 의해 완전히 제어되는 재사용 가능한 조회 도구가 됩니다.

여기에 INDEX-MATCH를 특별하게 만드는 요령이 있습니다. 조회 열과 반환 열은 서로 독립적이므로 반환하는 값이 검색하는 값의 왼쪽에 있을 수 있습니다.

가격이 A열에 있고 제품 이름이 C열에 있다고 가정해 보겠습니다. 이름으로 제품의 가격을 찾으려면 =INDEX(A2:A20, MATCH(E1, C2:C20, 0))을 작성합니다.

C열을 검색했지만 A열에서 값을 반환했습니다. VLOOKUP은 도움 없이는 이렇게 할 수 없습니다.

반환 범위에 따라 무엇을 가져올지 결정됩니다. 같은 기준값으로 검색하면서 INDEX 범위만 바꾸면 원하는 열의 값을 가져올 수 있습니다.

고객의 이메일을 찾습니다: =INDEX(D2:D50, MATCH(E1, A2:A50, 0)).

같은 고객의 도시를 찾으려면 다음과 같이 합니다: =INDEX(F2:F50, MATCH(E1, A2:A50, 0)).

MATCH 부분은 그대로 유지하고, 다른 결과를 선택하기 위해 INDEX 범위만 바꿉니다.

두 번째 MATCH로 찾은 열 번호도 INDEX에 제공할 수 있습니다. 그러면 행과 열이 교차하는 위치의 값을 정확히 지정할 수 있습니다.

=INDEX(B2:E10, MATCH(G1, A2:A10, 0), MATCH(G2, B1:E1, 0))

첫 번째 MATCH는 A열의 표시 이름에서 행을 찾고, 두 번째 MATCH는 1행의 머리글에서 열을 찾습니다. INDEX는 두 위치가 교차하는 셀을 반환합니다. 이 고급 패턴은 뒤에서 자세히 다룹니다.

위치가 서로 일치하려면 조회 범위와 반환 범위가 같은 행에서 시작하고 높이도 같아야 합니다.

MATCH가 A2:A20(19개 행)을 검색하지만 INDEX가 C2:C19(18개 행)에서 반환하면 위치가 어긋나 잘못된 결과를 얻게 됩니다.

신뢰할 수 있는 방법은 두 범위에 정확히 같은 행 범위를 사용하는 것입니다. 예를 들어 A2:A20과 C2:C20처럼 사용합니다. A:A와 C:C 같은 전체 열 참조도 자동으로 정렬을 유지합니다.

MATCH가 조회 값을 찾지 못하면 #N/A를 반환하고 전체 INDEX-MATCH에도 해당 오류가 표시됩니다. 깔끔한 대체 결과를 표시하려면 IFNA로 감쌉니다.

=IFNA(INDEX(C2:C20, MATCH(E1, A2:A20, 0)), "Not found")

이제 없는 제품은 불안해 보이는 오류 대신 "찾을 수 없음"이라는 텍스트로 표시됩니다. IFERROR도 사용할 수 있지만, IFNA는 찾을 수 없는 경우만 대상으로 하므로 다른 오류는 그대로 드러납니다.

지금까지 배운 내용을 모두 합쳐 보겠습니다. 직원 표에 A열에는 ID, B열에는 이름, C열에는 부서, D열에는 급여가 있습니다. 사용자가 G1에 ID를 입력합니다.

해당 직원의 부서를 반환하려면 =INDEX(C2:C200, MATCH(G1, A2:A200, 0))을 사용합니다.

대신 급여를 반환하려면 INDEX 범위를 D2:D200으로 바꿉니다. 조회 방식은 전혀 바뀌지 않고, 값을 읽어 오는 열만 바뀝니다. 이는 동적 조회에서 일상적으로 사용하는 핵심 도구입니다.

수식이 복잡해 보일 때는 스프레드시트가 계산하는 방식대로 가장 안쪽의 함수부터 바깥쪽으로 평가해 보세요.

=INDEX(C2:C20, MATCH(E1, A2:A20, 0))의 경우 먼저 MATCH(E1, A2:A20, 0)을 읽고, 이것이 5와 같은 숫자를 반환한다고 생각한 다음 =INDEX(C2:C20, 5)로 바꾸어 생각합니다.

그러면 수식은 단순히 "5번째 가격을 반환한다"는 뜻이 됩니다. 이 습관을 들이면 중첩된 모든 조회를 쉽게 디버깅할 수 있습니다.

두 함수가 어떻게 결합되는지 이해했는지 확인해 보세요.

두 함수를 유연한 조회 방식으로 결합했습니다.

• 패턴: =INDEX(return_range, MATCH(lookup_value, lookup_range, 0))
• MATCH는 행 위치를 찾고, INDEX는 해당 위치의 값을 반환합니다
• 조회 열과 반환 열은 서로 독립적이므로 오른쪽을 조회하는 것만큼 쉽게 왼쪽을 조회할 수 있습니다
• 두 범위의 높이를 같게 유지하고, 오류를 깔끔하게 처리하려면 IFNA로 감쌉니다

다음으로 이 접근 방식이 왜 VLOOKUP보다 자주 더 나은지 정확히 살펴봅니다.