INDEX-MATCH-MATCH로 양방향 조회하기

왼쪽 아래로 지역이 나열되고 위쪽 가로 방향으로 월이 나열된 월별 판매 격자를 생각해 보십시오. 선택한 지역과 선택한 월이 만나는 위치의 숫자를 찾으려고 합니다.

일반 조회는 한 방향으로만 검색하여 값을 찾습니다. 양방향 조회는 두 방향을 동시에 검색합니다. 올바른 행과 열을 찾은 다음 두 위치가 교차하는 곳의 값을 반환합니다.

이 작업의 대표적인 도구는 INDEX를 두 개의 MATCH 호출과 결합하는 방식이며, 흔히 INDEX-MATCH-MATCH라고 씁니다.

INDEX는 위치를 기준으로 범위에서 값을 반환합니다. 전체 형식은 INDEX(array, row_num, column_num)입니다.

셀 영역, 행 번호, 열 번호를 지정하면 해당 위치의 값을 반환합니다. 예를 들어 B2에서 시작하는 격자에서 행 3, 열 2를 요청하면 그 영역 안에서 아래로 3번째 행, 오른쪽으로 2번째 열에 있는 값을 반환합니다.

핵심은 INDEX에 레이블이 아닌 위치가 필요하다는 점입니다. MATCH가 바로 이 위치를 제공합니다.

MATCH는 하나의 행이나 열 안에서 값의 위치를 찾습니다. 형식은 MATCH(lookup_value, lookup_array, match_type)입니다.

일치 유형에는 정확히 일치하도록 0을 사용합니다. 결과는 1부터 세었을 때 값이 있는 위치를 나타내는 숫자입니다.

범위 A2:A5에서 "East"가 두 번째 항목이라면 MATCH는 2를 반환합니다. 이 2는 INDEX의 행 번호가 될 수 있습니다.

양방향 조회에서는 MATCH를 두 번 실행합니다.

• 한 MATCH는 해당 지역이 어느 행에 있는지 찾습니다.
• 다른 MATCH는 해당 월이 어느 열에 있는지 찾습니다.

그런 다음 두 숫자를 INDEX에 입력합니다. 행 MATCH는 레이블의 세로 범위를 검색하고, 열 MATCH는 머리글의 가로 범위를 검색합니다.

결과는 해당 행과 열이 교차하는 하나의 셀입니다.

다음과 같은 레이아웃을 생각해 보십시오. 지역 레이블은 A2:A5(East, West, North, South)에 있고, 월 머리글은 B1:D1(Jan, Feb, Mar)에 있습니다. 실제 판매 숫자는 B2:D5를 채웁니다.

조회에 사용할 두 입력 셀도 있습니다. G1에는 원하는 지역이 있고, G2에는 원하는 월이 있습니다.

목표는 G1과 G2를 읽어 B2:D5에서 일치하는 판매액을 반환하는 하나의 수식을 만드는 것입니다.

먼저 지역을 찾습니다. MATCH는 세로 레이블 목록인 A2:A5에서 G1에 입력한 값을 검색합니다.

G1에 "North"가 있고 North가 세 번째 레이블이라면 이 MATCH는 3을 반환합니다.

이 숫자는 INDEX에 데이터 영역의 어느 행을 읽어야 하는지 알려 줍니다. 데이터를 검색하는 것이 아니라 레이블만 있는 A2:A5를 검색하므로 위치 3이 데이터의 세 번째 행과 맞춰진다는 점에 주의하십시오.

다음으로 월을 찾습니다. 이 MATCH는 가로 머리글 행인 B1:D1에서 G2의 값을 검색합니다.

G2에 "Feb"가 있고 Feb가 두 번째 머리글이라면 MATCH는 2를 반환합니다.

이 숫자는 INDEX의 열 위치가 됩니다. 행과 마찬가지로 머리글만 있는 B1:D1을 검색하므로 위치가 B2:D5의 데이터 열과 맞춰집니다.

이제 두 MATCH 호출을 INDEX 안에 넣습니다. 데이터 영역 B2:D5가 배열이고, 행 MATCH가 행 번호를 제공하며, 열 MATCH가 열 번호를 제공합니다.

G1이 "North"이고 G2가 "Feb"이면 행 MATCH는 3, 열 MATCH는 2를 반환하므로 INDEX는 B2:D5의 3행 2열에 있는 값을 반환합니다.

이 하나의 수식이 완전한 양방향 조회입니다.

B2:D5에 다음과 같은 값이 있다고 가정해 보겠습니다. North 행에는 Jan 50, Feb 80, Mar 65가 있습니다.

• MATCH("North", A2:A5, 0)은 3을 반환합니다.
• MATCH("Feb", B1:D1, 0)은 2를 반환합니다.
• INDEX(B2:D5, 3, 2)는 3행 2열을 읽어 80을 반환합니다.

G1을 "East"로 변경하거나 G2를 "Mar"로 변경하면 전체 수식이 즉시 다시 계산됩니다. 두 MATCH 조회로 INDEX를 구동하는 방식의 강점입니다.

VLOOKUP은 첫 번째 열만 검색하고, 고정된 수의 열만큼 오른쪽에 있는 값을 반환합니다. 월을 바꾸려면 열 인덱스를 직접 하드코딩하거나 계산해야 합니다.

INDEX-MATCH-MATCH를 사용하면 레이블을 기준으로 행과 열 모두를 동적으로 선택할 수 있습니다. 열 순서를 바꾸거나 새 월을 추가해도 고정된 수가 아니라 머리글 텍스트를 기준으로 일치시키므로 수식이 계속 작동합니다.

가장 흔한 실수는 범위가 서로 맞지 않는 것입니다. 행을 찾는 MATCH 범위는 INDEX 데이터 블록과 높이가 같아야 하고, 열을 찾는 MATCH 범위는 너비가 같아야 합니다.

여기서 A2:A5는 높이가 4행이고 B2:D5도 높이가 4행이므로 MATCH 결과 3은 실제로 세 번째 데이터 행을 의미합니다. 실수로 머리글이 포함된 A1:A5를 검색하면 위치가 한 행씩 밀려 잘못된 셀을 얻게 됩니다.

양방향 조회 패턴을 배웠습니다.

• INDEX는 블록 안의 행 및 열 위치에 있는 값을 반환합니다.
• 첫 번째 MATCH는 세로 레이블을 검색하여 행을 찾습니다.
• 두 번째 MATCH는 가로 머리글을 검색하여 열을 찾습니다.

결합된 수식 =INDEX(data, MATCH(row), MATCH(col))은 두 입력값을 읽고 두 입력값이 교차하는 위치의 값을 반환합니다. 위치가 어긋나지 않도록 MATCH 범위의 크기를 데이터 블록과 같게 유지하세요.