VLOOKUP이 왼쪽 열은 못 찾을 때, INDEX/MATCH로 해결하기

제품코드로 재고 수량을 찾고 싶은데 재고 열이 제품코드 열보다 왼쪽에 있다면 VLOOKUP으로는 바로 가져오기 어렵습니다. VLOOKUP은 조회 범위의 첫 번째 열에서 값을 찾고, 그보다 오른쪽에 있는 값을 반환하는 구조이기 때문입니다.

예를 들어 A열에 재고 수량이 있고 D열에 제품코드가 있다면, D열의 제품코드를 기준으로 A열의 재고 수량을 가져오는 방식은 일반적인 VLOOKUP으로 처리할 수 없습니다.

XLOOKUP을 사용할 수 있는 환경이라면 비교적 간단하게 해결할 수 있지만, 오래된 Excel을 사용하는 회사 PC에서는 XLOOKUP을 지원하지 않는 경우도 있습니다. 이럴 때 대표적으로 사용할 수 있는 방법이 INDEX와 MATCH를 조합하는 방식입니다.

XLOOKUP과 VLOOKUP의 차이부터 확인하고 싶다면 VLOOKUP과 XLOOKUP, 실무에서는 뭘 써야 할까도 함께 참고해보세요.

1. INDEX와 MATCH는 각각 무슨 일을 하나

INDEX/MATCH 수식은 처음 보면 함수가 두 개 들어가 있어 복잡해 보이지만, 각각의 역할을 따로 보면 구조가 단순합니다.

MATCH - 찾는 값이 지정한 범위에서 몇 번째 위치에 있는지 숫자로 반환합니다.
INDEX - 지정한 범위에서 몇 번째 위치의 값을 가져옵니다.

즉 MATCH로 먼저 위치를 찾은 다음, 그 위치 번호를 INDEX에 전달해 실제 값을 가져오는 방식입니다.

MATCH로 위치를 찾고 INDEX로 실제 값을 가져오는 INDEX MATCH 조합 구조

2. 왼쪽 열 조회가 필요한 실제 예제

아래와 같은 데이터가 있다고 가정해보겠습니다. 이번에는 제품코드를 기준으로 그보다 왼쪽에 있는 재고 수량을 찾는 것이 목적입니다.

재고수량	제품명	규격	제품코드
120	A제품	10mm	P001
80	B제품	20mm	P002
150	C제품	30mm	P003
200	D제품	40mm	P004

예를 들어 F2 셀에 P003을 입력하고 그 제품의 재고 수량인 150을 가져오려고 합니다.

문제는 찾을 기준인 제품코드가 D열에 있고, 가져올 재고 수량은 그보다 왼쪽인 A열에 있다는 점입니다.

3. 왜 VLOOKUP으로는 왼쪽 값을 가져올 수 없을까?

VLOOKUP은 지정한 조회 범위의 첫 번째 열에서 찾을 값을 검색한 뒤 그보다 오른쪽 열의 값을 반환합니다.

예를 들어 다음처럼 D열의 제품코드를 기준으로 A열의 재고를 가져오려고 해도 일반적인 VLOOKUP 구조로는 해결할 수 없습니다.

=VLOOKUP(F2,D2:A5,4,0)

Excel의 VLOOKUP 조회 범위는 왼쪽에서 오른쪽으로 구성되어야 하므로 D2:A5처럼 역방향 범위를 지정해 왼쪽 값을 반환하는 방식으로 사용할 수 없습니다.

이 때문에 찾을 값보다 왼쪽에 있는 데이터를 가져와야 한다면 VLOOKUP 대신 INDEX/MATCH나 XLOOKUP 같은 방법이 필요합니다.

4. MATCH로 먼저 제품코드의 위치를 찾기

먼저 MATCH만 사용해서 P003이 제품코드 범위에서 몇 번째에 있는지 확인해보겠습니다.

=MATCH(F2,$D$2:$D$5,0)

F2에 P003이 들어 있다면 결과는 3입니다.

범위	값	위치
D2	P001	1
D3	P002	2
D4	P003	3
D5	P004	4

MATCH는 P003 자체를 반환하는 것이 아니라 D2:D5 범위에서 세 번째 위치에 있다는 의미의 숫자 3을 반환합니다.

5. INDEX로 세 번째 위치의 재고 수량 가져오기

이제 MATCH가 찾아낸 위치 번호 3을 INDEX에 전달합니다.

=INDEX($A$2:$A$5,3)

A2:A5 범위의 세 번째 값은 150이므로 INDEX의 결과는 150이 됩니다.

이 과정을 하나의 수식으로 합치면 다음과 같습니다.

=INDEX($A$2:$A$5,MATCH(F2,$D$2:$D$5,0))

수식을 안쪽부터 보면 동작 과정이 쉽게 보입니다.

MATCH(F2,$D$2:$D$5,0)으로 P003의 위치를 찾습니다.
MATCH의 결과로 3이 반환됩니다.
INDEX($A$2:$A$5,3)이 A열의 세 번째 값을 가져옵니다.
최종 결과로 재고 수량 150이 표시됩니다.

6. INDEX와 MATCH의 수식 구성 요소

MATCH의 기본 문법은 다음과 같습니다.

=MATCH(찾을값,찾을범위,일치방식)

인수	역할
찾을값	찾으려는 제품코드 등의 값
찾을범위	찾을 값이 들어 있는 범위
0	정확히 일치하는 값의 위치를 찾음

INDEX의 기본 문법은 다음과 같습니다.

=INDEX(반환범위,위치번호)

INDEX/MATCH에서는 MATCH가 계산한 위치 번호가 INDEX의 두 번째 인수로 들어갑니다.

따라서 다음 수식을 말로 풀어보면 “D열에서 F2의 위치를 찾고, 같은 위치에 있는 A열의 값을 가져온다”는 의미가 됩니다.

=INDEX($A$2:$A$5,MATCH(F2,$D$2:$D$5,0))

7. VLOOKUP과 비교하면 무엇이 다른가?

비교 항목	VLOOKUP	INDEX/MATCH
왼쪽 열 조회	일반적인 구조에서는 불가능	가능
반환값 지정	열 번호 사용	반환 범위를 직접 지정
표 구조 변경	조회 범위와 열 번호 확인 필요	찾을 범위와 반환 범위를 따로 관리
호환성	구버전에서도 폭넓게 사용	구버전에서도 폭넓게 사용
수식 길이	비교적 짧음	함수 두 개라 조금 길어짐

INDEX/MATCH의 가장 큰 장점은 찾을 범위와 반환할 범위를 서로 독립적으로 지정할 수 있다는 것입니다.

VLOOKUP처럼 반환 열을 숫자로 세지 않아도 되기 때문에 수식만 봐도 어느 범위에서 찾고 어느 범위의 값을 가져오는지 비교적 쉽게 확인할 수 있습니다.

8. XLOOKUP을 사용할 수 있다면 더 간단합니다

현재 사용하는 Excel에서 XLOOKUP을 지원한다면 같은 왼쪽 조회를 더 간단한 형태로 작성할 수 있습니다.

=XLOOKUP(F2,$D$2:$D$5,$A$2:$A$5,"없음")

이 수식은 D2:D5에서 F2의 제품코드를 찾고, 같은 위치에 있는 A2:A5의 재고 수량을 반환합니다.

INDEX/MATCH와 동작 원리는 비슷하지만 XLOOKUP은 찾을 범위와 반환 범위를 한 함수 안에서 지정할 수 있어 수식이 더 짧습니다.

다만 XLOOKUP을 지원하지 않는 Excel과 파일을 주고받아야 한다면 INDEX/MATCH가 여전히 유용한 대안입니다.

9. 행과 열을 동시에 찾고 싶을 때

INDEX/MATCH는 왼쪽 조회뿐 아니라 행과 열 조건을 동시에 찾아야 하는 표에서도 활용할 수 있습니다.

예를 들어 지점이 세로 방향에 있고 월이 가로 방향에 있는 매출표에서 서울지점의 3월 매출을 찾는 상황입니다.

이때 MATCH를 두 번 사용해 행의 위치와 열의 위치를 각각 찾을 수 있습니다.

=INDEX(B2:E4,MATCH("서울",A2:A4,0),MATCH("3월",B1:E1,0))

첫 번째 MATCH는 서울이 몇 번째 행에 있는지 찾고, 두 번째 MATCH는 3월이 몇 번째 열에 있는지 찾습니다. INDEX는 두 위치가 만나는 셀의 값을 반환합니다.

따라서 INDEX/MATCH는 단순한 VLOOKUP 대체뿐 아니라 행과 열을 동시에 조회하는 2차원 검색에도 활용할 수 있습니다.

자주 막히는 지점 세 가지

1) MATCH를 입력했더니 값 대신 숫자가 나옵니다

원인: MATCH는 실제 값을 반환하는 함수가 아니라 지정한 범위에서 몇 번째에 있는지를 숫자로 반환합니다.

해결: MATCH의 결과를 INDEX의 위치 인수로 넣어야 실제 값을 가져올 수 있습니다.

2) INDEX/MATCH 결과가 한 행씩 어긋납니다

원인: INDEX의 반환 범위와 MATCH의 조회 범위가 서로 다른 행에서 시작했을 가능성이 있습니다.

예를 들어 반환 범위가 A2:A5인데 조회 범위가 D3:D6이라면 두 범위의 첫 번째 위치가 서로 다른 실제 행을 가리키게 됩니다.

해결: INDEX와 MATCH에서 사용하는 범위의 시작 행과 끝 행을 맞춥니다. 원본 데이터를 표(Table)로 변환(Ctrl+T)해 관리하는 것도 범위를 정리하는 데 도움이 됩니다.

3) 값이 분명히 있는데 MATCH에서 #N/A가 나옵니다

먼저 MATCH의 마지막 인수가 0인지 확인합니다.

=MATCH(F2,$D$2:$D$5,0)

그래도 #N/A가 나온다면 숫자와 텍스트의 형식 차이 또는 셀 앞뒤의 보이지 않는 공백을 확인해야 합니다.

VLOOKUP에서도 같은 이유로 #N/A가 발생할 수 있으므로 자세한 점검 순서는 VLOOKUP이 안 될 때 확인할 5가지에서 함께 확인할 수 있습니다.

자주 묻는 질문

INDEX/MATCH가 VLOOKUP보다 항상 더 좋은 선택인가요?

그렇지는 않습니다. 단순히 왼쪽에서 오른쪽으로 값을 조회하고 기존 파일에 VLOOKUP이 정상적으로 사용되고 있다면 굳이 수식을 바꿀 필요는 없습니다.

왼쪽 조회가 필요하거나 반환 범위를 직접 지정하고 싶은 경우 INDEX/MATCH의 장점이 더 뚜렷합니다.

XLOOKUP을 쓸 수 있으면 INDEX/MATCH는 안 배워도 되나요?

새 파일만 사용하고 모든 작업 환경에서 XLOOKUP을 지원한다면 XLOOKUP으로 대부분의 조회 작업을 처리할 수 있습니다.

하지만 오래된 Excel 파일이나 다른 사람이 만든 업무 문서에서는 INDEX/MATCH를 여전히 자주 볼 수 있기 때문에 수식을 읽고 수정할 수 있을 정도로 구조를 이해해두면 유용합니다.

INDEX/MATCH도 IFERROR로 감싸야 하나요?

찾는 값이 없으면 MATCH에서 #N/A가 발생하고 결과적으로 INDEX/MATCH 전체에도 오류가 표시될 수 있습니다.

필요하다면 다음처럼 IFERROR를 사용할 수 있습니다.

=IFERROR(INDEX($A$2:$A$5,MATCH(F2,$D$2:$D$5,0)),"확인 필요")

다만 오류를 무조건 숨기기 전에 왜 값을 찾지 못했는지 원인을 먼저 확인하는 것이 좋습니다. 자세한 내용은 엑셀 수식 에러(#N/A, #REF!) 뜰 때 IFERROR로 정리하는 법에서 확인할 수 있습니다.

MATCH의 세 번째 인수를 1이나 -1로 쓰면 어떻게 되나요?

0은 정확히 일치하는 값을 찾습니다. 일반적인 제품코드, 사번, 거래처명 조회에서는 대부분 0을 사용하는 것이 이해하기 쉽고 안전합니다.

1이나 -1은 정렬 상태와 조건을 고려해야 하는 근사 일치 방식이므로 목적을 정확히 알고 있을 때 사용하는 것이 좋습니다.

구글 스프레드시트에서도 INDEX/MATCH를 사용할 수 있나요?

네. Google 스프레드시트에서도 INDEX와 MATCH를 사용할 수 있습니다. 다만 Excel과 Google 스프레드시트 사이에서 파일을 변환해 사용한다면 실제 업무 파일에서 수식 결과가 정상적으로 유지되는지 한 번 확인하는 것이 좋습니다.

정리

MATCH는 찾는 값의 위치를 반환합니다.
INDEX는 지정한 위치의 실제 값을 반환합니다.
두 함수를 조합하면 VLOOKUP으로 어려운 왼쪽 열 조회를 처리할 수 있습니다.
INDEX의 반환 범위와 MATCH의 조회 범위는 시작 행과 끝 행을 맞추는 것이 중요합니다.
XLOOKUP을 사용할 수 있다면 같은 조회를 더 짧은 수식으로 작성할 수 있습니다.
XLOOKUP을 지원하지 않는 환경에서는 INDEX/MATCH가 대표적인 대안입니다.
MATCH를 두 번 사용하면 행과 열을 동시에 찾는 2차원 조회도 가능합니다.

INDEX/MATCH는 처음에는 VLOOKUP보다 복잡해 보이지만 핵심은 단순합니다. MATCH로 위치를 찾고 INDEX로 그 위치의 값을 가져온다고 이해하면 됩니다.

특히 다른 사람이 만든 오래된 Excel 파일을 관리하거나, 찾을 기준보다 왼쪽에 있는 값을 조회해야 할 때 알아두면 활용도가 높은 조합입니다.