엑셀 INDEX와 MATCH 함수 사용법

INDEX와 MATCH는 작은 함수 두 개지만, 함께 쓰면 엑셀에서 가장 유연한 조회 수식이 돼요. MATCH는 "어디에 있지?"에 위치 번호로 답하고, INDEX는 "그 위치에 뭐가 있지?"에 원하는 열의 값으로 답해요. 이 조합은 왼쪽 열도 찾을 수 있고, 행과 열을 한 번에 찾을 수 있고, 누가 표 중간에 열을 삽입해도 계속 제대로 동작해요. 게다가 모든 버전의 엑셀에서 쓸 수 있어요.

함수 구조

=INDEX(array, row_num, [column_num])

array는 값을 가져올 범위예요. 열 하나, 행 하나, 또는 표 전체를 넣을 수 있어요.
row_num은 그 범위에서 몇 번째 행인지예요. 1부터 세요. 범위가 행 하나뿐이면 이 숫자는 가로 방향으로 세요.
column_num은 넣지 않아도 돼요. 범위에서 몇 번째 열인지예요. array가 두 열 이상일 때만 필요해요.

여섯 행짜리 범위의 7번째 행처럼 범위를 벗어난 위치를 지정하면 #REF!가 나와요.

=MATCH(lookup_value, lookup_array, [match_type])

lookup_value는 찾을 값이에요. 텍스트, 숫자, 셀 참조를 넣을 수 있어요.
lookup_array는 값을 찾을 열 하나 또는 행 하나예요.
match_type은 넣지 않아도 되지만, 무엇을 일치로 볼지 정하는 중요한 인수예요.
- 0은 lookup_value와 정확히 같은 첫 번째 값을 찾아요. 목록 순서는 상관없어요.
- 1은 lookup_value보다 작거나 같은 값 중 가장 큰 값을 찾아요. 목록이 오름차순으로 정렬되어 있어야 해요. 인수를 생략하면 이 값이 기본값이에요.
- -1은 lookup_value보다 크거나 같은 값 중 가장 작은 값을 찾아요. 목록이 내림차순으로 정렬되어 있어야 해요.

MATCH가 돌려주는 건 시트의 행 번호가 아니라 lookup_array 안에서의 위치예요. B2:B7에서 B5의 값은 4번째예요. 대소문자는 구분하지 않아서 "stapler"도 "Stapler"와 일치해요.

두 함수를 합치면 MATCH가 INDEX에 행 번호를 넘겨줘요.

INDEX와 MATCH 수식 읽는 법

=INDEX(C2:C7¹,MATCH(G2²,B2:B7³,0⁴))

C2:C7답이 들어 있는 열(가격)
G2찾을 값(스테이플러)
B2:B7그 값을 찾을 열
0정확히 일치하는 값만, 목록 순서는 상관없음

INDEX와 MATCH로 가격 찾기

아래 시트에는 제품 여섯 개가 있어요. G2에 있는 제품, 스테이플러의 가격을 찾는 게 목표예요. 함수마다 하는 일을 볼 수 있게 수식을 두 단계로 만들어 볼게요.

MATCH로 위치를 찾으세요

G3을 클릭하고 다음 수식을 입력한 다음 Enter를 누르세요.

=MATCH(G2,B2:B7,0)

	A	B	C	D	F	G
1	코드	제품	가격	재고	조회	결과
2	P-101	공책	4.5	120	제품	스테이플러
3	P-102	볼펜	1.2	300	위치	4
4	P-103	형광펜	1.8	85	가격
5	P-104	스테이플러	12	15	코드
6	P-105	가위	6.5	40	열 제목	재고
7	P-106	테이프	2.5	60	값

MATCH가 G2(파란색)의 제품을 B2:B7(분홍색)에서 찾아요. 스테이플러는 4번째라서 G3에 4가 표시돼요.

결과는 4예요. 스테이플러가 B2:B7에서 네 번째 제품이라는 뜻이에요. 위치 번호만으로는 쓸모가 별로 없지만, INDEX에 필요한 게 바로 이 번호예요.

위치를 INDEX에 넘겨주세요
G4를 클릭하고 전체 수식을 입력하세요. MATCH 부분은 그대로이고, INDEX가 그 바깥을 감싸면서 값을 가져올 열을 지정해요.
=INDEX(C2:C7,MATCH(G2,B2:B7,0))

Enter 키를 누르고 결과를 확인하세요

G4에 스테이플러의 가격 12가 표시돼요. 엑셀은 안쪽부터 계산해요. MATCH가 4를 돌려주니 수식은 =INDEX(C2:C7,4)가 되고, 가격 열의 네 번째 값을 가져와요.

	A	B	C	D	F	G
1	코드	제품	가격	재고	조회	결과
2	P-101	공책	4.5	120	제품	스테이플러
3	P-102	볼펜	1.2	300	위치	4
4	P-103	형광펜	1.8	85	가격	12
5	P-104	스테이플러	12	15	코드
6	P-105	가위	6.5	40	열 제목	재고
7	P-106	테이프	2.5	60	값

INDEX가 C2:C7(파란색)에서 4번째 값을 돌려줘요. 4는 MATCH가 B2:B7에서 G2를 찾은 위치예요.

왼쪽 열의 값도 가져오세요

제품 코드를 가져오려면 첫 번째 범위만 바꾸면 돼요. 코드는 제품 이름보다 왼쪽인 A열에 있어서 VLOOKUP으로는 가져올 수 없어요.

=INDEX(A2:A7,MATCH(G2,B2:B7,0))

결과는 P-104예요.

	A	B	C	D	F	G
1	코드	제품	가격	재고	조회	결과
2	P-101	공책	4.5	120	제품	스테이플러
3	P-102	볼펜	1.2	300	위치	4
4	P-103	형광펜	1.8	85	가격	12
5	P-104	스테이플러	12	15	코드	P-104
6	P-105	가위	6.5	40	열 제목	재고
7	P-106	테이프	2.5	60	값

찾을 열(B)과 가져올 열(A)이 따로 지정되기 때문에 어느 쪽 열에서든 값을 가져올 수 있어요.

양방향 조회: 행과 열을 함께 찾기

INDEX에 표 전체와 위치 두 개를 주면, 그 행과 열이 만나는 셀의 값을 돌려줘요. MATCH 하나로 행을 찾고, 머리글 행에서 찾는 MATCH 하나로 열을 찾으면 돼요. G6에는 원하는 열의 제목인 재고가 들어 있어요.

=INDEX(A2:D7,MATCH(G2,B2:B7,0),MATCH(G6,A1:D1,0))

첫 번째 MATCH는 4(표에서 스테이플러의 행)를, 두 번째 MATCH는 4(A1:D1에서 재고는 네 번째 머리글)를 돌려주고, INDEX(A2:D7,4,4)는 15를 돌려줘요. G6에 가격을 입력하면 같은 수식이 12를 돌려줘요.

	A	B	C	D	F	G
1	코드	제품	가격	재고	조회	결과
2	P-101	공책	4.5	120	제품	스테이플러
3	P-102	볼펜	1.2	300	위치	4
4	P-103	형광펜	1.8	85	가격	12
5	P-104	스테이플러	12	15	코드	P-104
6	P-105	가위	6.5	40	열 제목	재고
7	P-106	테이프	2.5	60	값	15

행은 B2:B7에서 G2를 찾아서, 열은 머리글 A1:D1에서 G6을 찾아서 정해요.

표와 두 검색 범위의 시작 위치를 맞춰 주세요. 표가 2행에서 시작하니 행을 찾는 범위도 2행에서 시작해야 하고, 표가 A열에서 시작하니 머리글을 찾는 범위도 A열에서 시작해야 해요.

실제 활용 사례

영업

매출 구간별 수수료율

영업 담당자의 수수료율은 매출 5,000 미만이면 2%, 5,000 이상이면 3%, 10,000 이상이면 4%, 20,000 이상이면 5%예요. 구간표는 작은 값부터 정렬되어 있고, 이수진 담당자의 이번 달 매출은 13,500이에요.

	A	B	D	E
1	매출 기준	수수료율	담당자	이수진
2	0	2%	매출	13,500
3	5,000	3%	수수료율	4%
4	10,000	4%
5	20,000	5%

A2:A5에 13,500은 없어서, MATCH 유형 1이 매출을 넘지 않는 가장 큰 구간인 10,000에서 멈춰요.

=INDEX(B2:B5,MATCH(E2,A2:A5,1))

결과는 4%예요. match_type이 1이면 MATCH는 13,500보다 작거나 같은 값 중 가장 큰 값인 10,000의 위치, 3을 돌려줘요. 유형 1이 꼭 필요한 대표적인 경우이고, 구간이 오름차순으로 정렬되어 있어서 제대로 동작해요.

학교

성적표에서 점수 찾기

선생님이 학생마다 한 행, 과목마다 한 열로 성적을 정리해 두었어요. 스크롤하지 않고 아무 학생의 아무 과목 점수나 바로 보고 싶어요.

	A	B	C	D	F	G
1	학생	수학	과학	영어	학생	박지우
2	김하준	88	92	79	과목	과학
3	이서연	75	68	90	점수	85
4	박지우	94	85	88
5	최도윤	62	77	71
6	정민서	81	96	84

박지우는 3번째 학생이고 과학은 2번째 과목이라서, INDEX가 B2:D6의 3행 2열 값을 돌려줘요.

=INDEX(B2:D6,MATCH(G1,A2:A6,0),MATCH(G2,B1:D1,0))

결과는 85예요. 여기서는 표 B2:D6에 점수만 들어 있으니, 머리글도 B1:D1에서 찾아야 열이 하나씩 맞아요. G1이나 G2를 바꾸면 점수도 바로 바뀌어요.

재고

물건이 들어가는 가장 작은 상자 고르기

물류 창고에서 주문을 상자 네 종류에 나눠 담아요. 상자는 큰 것부터 작은 것 순서로 정리되어 있어요. 주문 A-2207은 상품이 18개인데, 이걸 담을 수 있는 가장 작은 상자를 찾으려고 해요.

	A	B	D	E
1	상자	최대 수량	주문	A-2207
2	대형	50	상품 수	18
3	중형	30	상자	소형
4	소형	20
5	미니	10

최대 수량이 내림차순이라서, MATCH 유형 -1이 18 이상인 값 중 가장 작은 20을 찾아요.

=INDEX(A2:A5,MATCH(E2,B2:B5,-1))

결과는 소형이에요. B2:B5에서 18보다 크거나 같은 값 중 가장 작은 값이 20이라서 MATCH가 3을 돌려줘요. 미니 상자(10)는 너무 작고, 중형(30)은 공간이 남아요.

가계부

지출이 가장 많았던 달

여섯 달 동안의 지출을 기록해 두었고, 금액이 아니라 지출이 가장 많았던 달의 이름을 알고 싶어요. 금액은 MAX로 찾고, INDEX와 MATCH로 그 금액을 달 이름으로 바꿔요.

	A	B	D	E
1	월	지출	요약	결과
2	1월	420	가장 많이 쓴 달	3월
3	2월	385
4	3월	510
5	4월	460
6	5월	395
7	6월	470

MAX(B2:B7)는 510이고, MATCH가 그 위치 3을 찾고, INDEX가 A2:A7의 3번째 달을 돌려줘요.

=INDEX(A2:A7,MATCH(MAX(B2:B7),B2:B7,0))

결과는 3월이에요. MAX를 MIN으로 바꾸면 지출이 가장 적었던 2월이 나와요. 최고 금액이 같은 달이 둘 이상이면 MATCH는 첫 번째 달을 돌려줘요.

알맞은 match_type 고르기

일치 유형은 가장 많이 틀리는 인수예요. 생략하면 "정확히 일치"가 아니라 1이 되기 때문이에요.

match_type	찾는 값	목록 조건	이럴 때 쓰세요
`0`	정확히 일치하는 첫 번째 값	순서 상관없음	이름, 코드, ID 등 거의 모든 조회
`1`(기본값)	찾을 값보다 작거나 같은 값 중 가장 큰 값	오름차순 정렬	수수료율표 같은 구간 조회
`-1`	찾을 값보다 크거나 같은 값 중 가장 작은 값	내림차순 정렬	상자처럼 "들어가는 것 중 가장 작은 것"

match_type이 0이고 찾을 값이 텍스트면 와일드카드도 쓸 수 있어요. *는 글자 수에 상관없이 아무 글자를, ?는 정확히 한 글자를 뜻해요. =MATCH("형광*",B2:B7,0)은 형광펜의 위치인 3을 돌려줘요.

INDEX/MATCH, VLOOKUP, XLOOKUP 중 무엇을 쓸까요?

	VLOOKUP	INDEX와 MATCH	XLOOKUP
왼쪽 열 값 가져오기	불가능	가능	가능
옵션을 생략했을 때 일치 방식	유사 일치	유사 일치(유형 1)	정확히 일치
표 안에 열을 삽입해도 안전한가	아니요	예	예
양방향 조회	열 번호를 MATCH로 구해서	MATCH 두 개로	XLOOKUP 두 개를 중첩해서
쓸 수 있는 버전	모든 버전	모든 버전	Excel 2021 이후, Microsoft 365, 웹용 Excel

열 삽입이 왜 중요할까요? =VLOOKUP(G2,B2:D7,2,FALSE)도 12를 돌려주지만, 여기서 2는 고정된 열 개수예요. 제품과 가격 열 사이에 열을 하나 삽입하면 엑셀이 표 범위를 B2:E7로 넓혀 주지만 2는 그대로 2라서, 이제 가격 대신 새로 생긴 빈 열의 값(0)을 돌려줘요. INDEX와 MATCH는 가격 열 자체를 가리키기 때문에, 엑셀이 C2:C7을 D2:D7로 고쳐 주고 결과는 여전히 12예요.

파일을 여는 사람이 모두 Excel 2021 이후 버전을 쓴다면 XLOOKUP 하나로 같은 일을 할 수 있어요. 이 버전에는 기본값이 정확히 일치인 XMATCH도 있어서, MATCH 대신 INDEX 안에 똑같이 넣어 쓸 수 있어요. 이전 버전 사용자와 파일을 주고받는다면 INDEX와 MATCH가 가장 안전하고, 기존 통합 문서에서도 자주 보게 될 거예요.

자주 하는 실수

match_type을 생략하는 경우. 그러면 MATCH가 정렬된 목록이 필요한 유형 1로 동작해요. 정렬되지 않은 데이터에서는 틀린 값이나 #N/A가 나와요. 세 번째 인수로 0을 넣으세요.
범위의 시작 행이 서로 다른 경우. =INDEX(C2:C7,MATCH(G2,B1:B7,0))은 1행부터 찾고 2행부터 가져와요. MATCH는 스테이플러를 5번째에서 찾고, INDEX는 5번째 가격인 6.5(가위)를 오류 없이 돌려줘요. 두 범위를 같은 행에서 시작하고 같은 행에서 끝내세요.
위치를 행 번호로 착각하는 경우. MATCH는 시트의 행 번호(5)가 아니라 범위 안에서의 위치(4)를 돌려줘요. 두 범위가 같은 행에서 시작한다면 INDEX에 필요한 값이 바로 이 위치예요.
#N/A가 나오는 경우. MATCH가 값을 찾지 못한 거예요. 값이 없거나, 공백이 하나 더 있거나, 한쪽은 숫자이고 다른 쪽은 텍스트예요. 데이터를 고치거나 메시지를 대신 표시하세요: =IFNA(INDEX(C2:C7,MATCH(G2,B2:B7,0)),"없음").
#REF!가 나오는 경우. =INDEX(C2:C7,7)처럼 위치가 INDEX의 범위를 벗어났거나, 열 번호가 표의 열 수보다 커요. 보통 INDEX 범위가 MATCH 범위보다 짧을 때 생겨요. 두 범위의 크기를 같게 맞추세요.
복사할 때 범위가 밀리는 경우. 수식을 아래로 채우면 모든 범위가 함께 움직여요. F4로 고정하세요: =INDEX($C$2:$C$7,MATCH(H2,$B$2:$B$7,0)).

연습

위치, 가격, 코드 찾기

아래 시트를 사용하세요. 강조된 셀을 클릭하고, 시트 위의 입력줄에 수식을 입력한 다음 Enter를 누르세요. 그다음 엑셀에서 직접 해 보세요.

연락처 다섯 개를 A열에 도시, B열에 이름, C열에 전화번호로 적어 보세요.
셀 하나에 이름을 입력하고, INDEX와 MATCH로 그 사람의 도시를 가져와 보세요. 도시는 이름보다 왼쪽에 있어요.
머리글 행과 열 제목을 입력할 셀을 하나 더 만들고, 수식을 도시나 전화번호 중 원하는 값을 돌려주는 양방향 조회로 바꿔 보세요.

직접 해 보기4개 중 0개 완료

G3에 MATCH로 G2에 있는 제품이 B2:B7에서 몇 번째에 있는지 구해 보세요.
G4에 INDEX와 MATCH로 G2에 있는 제품의 가격을 가져와 보세요.
G5에 G2에 있는 제품의 코드를 가져와 보세요. 코드는 제품 이름보다 왼쪽에 있어요.
G7에 MATCH 두 개를 써서, G2의 제품에 대해 G6에 적힌 열의 값을 가져와 보세요.

점선으로 표시된 셀을 클릭하고, 입력줄에 수식을 쓴 다음 Enter를 누르세요.

	A	B	C	D	F	G
1	코드	제품	가격	재고	조회	결과
2	P-101	공책	4.5	120	제품	스테이플러
3	P-102	볼펜	1.2	300	위치
4	P-103	형광펜	1.8	85	가격
5	P-104	스테이플러	12	15	코드
6	P-105	가위	6.5	40	열 제목	재고
7	P-106	테이프	2.5	60	값

도움이 되었나요? 틀린 부분을 발견하셨나요? 피드백 보내기 · admin@arumsoft.com