[Excel] 엑셀 INDEX · MATCH · XLOOKUP 함수 심화 완전 정복

INDEX · MATCH · XLOOKUP — 엑셀 조회 함수 심화 완전 정복

 

📋 목차 1. VLOOKUP의 한계 — 왜 더 나은 함수가 필요한가 2. INDEX 함수 — 위치로 값 가져오기 3. MATCH 함수 — 값의 위치 찾기 4. INDEX + MATCH 조합 — VLOOKUP을 대체하는 최강 조합 5. 다중 조건 조회 — 두 개 이상 조건으로 찾기 6. XLOOKUP — 가장 현대적인 조회 함수 7. 실전 비교 — VLOOKUP vs INDEX·MATCH vs XLOOKUP

 

VLOOKUP은 강력하지만 치명적인 단점이 있다. 검색 기준 열이 반드시 범위의 첫 번째 열이어야 하고, 왼쪽 방향 조회가 불가능하며, 열을 삽입·삭제하면 수식이 깨진다.   INDEX·MATCH는 이 모든 단점을 해결한다. 처음엔 복잡해 보이지만 원리를 이해하면 어떤 방향으로도, 어떤 조건으로도 자유롭게 데이터를 찾을 수 있다. Excel 365 사용자라면 더 간단한 XLOOKUP을 쓰면 된다.

 

1	VLOOKUP의 한계

 

VLOOKUP을 쓰다 보면 반드시 한계에 부딪힌다. 아래 상황 중 하나라도 경험했다면 INDEX·MATCH가 필요하다.

 

VLOOKUP 한계	상황	해결책
왼쪽 조회 불가	기준 열 오른쪽에 없는 값 조회	INDEX·MATCH
첫 열만 기준	두 번째·세 번째 열 기준 불가	INDEX·MATCH
열 삽입 시 오류	열 번호(3)가 하드코딩되어 깨짐	INDEX·MATCH 또는 XLOOKUP
다중 조건 불가	이름+날짜 동시 조건 불가	INDEX·MATCH 배열 수식
여러 결과 반환 불가	첫 번째 일치 값만 반환	FILTER 또는 XLOOKUP

 

💡 언제 뭘 써야 하나? Excel 365/2021: XLOOKUP이 가장 간단하고 강력하다. 구버전 호환 필요: INDEX+MATCH 조합을 익혀두면 어떤 버전에서도 쓸 수 있다.

 

 

2	INDEX 함수 — 위치로 값 가져오기

 

INDEX는 범위에서 행·열 번호로 값을 가져오는 함수다. GPS 좌표처럼 '이 범위의 3행 2열 값 가져와'라고 지정한다.

 

=INDEX(범위, 행번호, [열번호])   =INDEX(A1:D10, 3, 2)   → A1:D10 범위의 3행 2열 값 =INDEX(A:A, 5)         → A열의 5번째 값 =INDEX(A1:D1, 1, 3)    → 첫 행의 3번째 값

 

수식	의미	결과
=INDEX(B2:B10, 3)	B2:B10의 3번째 값	B4 셀의 값
=INDEX(A1:D10, 2, 3)	범위의 2행 3열	C2 셀의 값
=INDEX(A:D, 5, 2)	전체 열의 5행 2열	B5 셀의 값

 

 

3	MATCH 함수 — 값의 위치 찾기

 

MATCH는 범위에서 특정 값이 몇 번째 위치에 있는지 숫자로 반환한다. INDEX가 '몇 번째 값을 가져와'라면, MATCH는 '이 값이 몇 번째에 있어?'를 담당한다.

 

=MATCH(찾을값, 찾을범위, [일치유형])   일치유형:   0  → 정확히 일치 (실무에서 항상 0 사용)   1  → 작거나 같은 값 중 최대 (오름차순 정렬 필요)  -1  → 크거나 같은 값 중 최소 (내림차순 정렬 필요)   =MATCH("서울", A1:A10, 0)   → "서울"이 A1:A10의 몇 번째인지 =MATCH(100, B1:B10, 0)     → 100이 B1:B10의 몇 번째인지

 

 

 

4	INDEX + MATCH — VLOOKUP 대체 최강 조합

 

INDEX와 MATCH를 조합하면 VLOOKUP보다 훨씬 유연한 조회가 가능하다. MATCH로 위치를 찾고, 그 위치를 INDEX에 넘기는 구조다.

 

▸ 기본 패턴

=INDEX(반환범위, MATCH(찾을값, 검색범위, 0))   예: 직원 이름으로 급여 찾기 =INDEX(C2:C100, MATCH(E2, A2:A100, 0))    ↑ C열(급여)에서    ↑ E2(이름)가 A열의 몇 번째인지

 

▸ VLOOKUP vs INDEX·MATCH 비교

상황	VLOOKUP	INDEX·MATCH
오른쪽 조회	=VLOOKUP(E2,A:C,3,0)	=INDEX(C:C,MATCH(E2,A:A,0))
왼쪽 조회	❌ 불가	=INDEX(A:A,MATCH(E2,C:C,0)) ✅
열 추가해도 안전	❌ 열 번호 고정	✅ 범위 참조라 안전
수식 가독성	비교적 간단	조금 복잡하지만 유연

 

▸ 왼쪽 방향 조회 (VLOOKUP 불가 상황)

데이터: A열=급여, B열=직급, C열=이름   목표: 이름(C열)으로 급여(A열) 찾기 → VLOOKUP 불가!   =INDEX(A2:A100, MATCH(E2, C2:C100, 0))    ↑ A열(급여)에서    ↑ E2(이름)가 C열의 몇 번째인지

 

💡 실무 팁 INDEX·MATCH 외울 때: 'INDEX에서 MATCH로 찾아온다'고 기억하자. INDEX(반환하고 싶은 열, MATCH(찾을 값, 검색 열, 0)) 이 패턴만 기억하면 90%는 해결된다.

 

 

5	다중 조건 조회 — 두 개 이상 조건으로 찾기

 

이름+날짜, 부서+등급처럼 여러 조건을 동시에 만족하는 행을 찾아야 할 때 INDEX·MATCH를 배열 수식으로 확장한다.

 

▸ 두 조건 동시 만족

=INDEX(반환범위, MATCH(1, (조건1범위=조건1)*(조건2범위=조건2), 0))   예: 이름이 '김민준'이고 월이 '5월'인 매출 =INDEX(D2:D100, MATCH(1, (A2:A100="김민준")*(B2:B100=5), 0))   ⚠️ Ctrl+Shift+Enter로 입력 (배열 수식)    Excel 365에서는 그냥 Enter

 

작동 원리: (A열="김민준")은 TRUE/FALSE 배열, (B열=5)도 TRUE/FALSE 배열. 두 배열을 곱하면 둘 다 TRUE인 자리만 1이 된다. MATCH는 그 1의 위치를 찾는다.

 

▸ SUMPRODUCT로 더 간단하게

=SUMPRODUCT((A2:A100="김민준")*(B2:B100=5)*D2:D100) → 배열 수식 없이 다중 조건 합계도 가능

 

 

 

6	XLOOKUP — 가장 현대적인 조회 함수

 

XLOOKUP은 Excel 365/2021에서만 사용 가능하다. VLOOKUP과 INDEX·MATCH의 장점을 모두 합친 함수로, 문법이 직관적이고 기능이 강력하다.

 

=XLOOKUP(찾을값, 검색범위, 반환범위, [못찾을때], [일치유형], [검색방향])   기본 사용: =XLOOKUP(E2, A2:A100, C2:C100)         → E2를 A열에서 찾아 C열 반환 =XLOOKUP(E2, A2:A100, C2:C100, "없음") → 못 찾으면 "없음" 표시   왼쪽 조회: =XLOOKUP(E2, C2:C100, A2:A100)         → C열에서 찾아 왼쪽 A열 반환   여러 열 동시 반환: =XLOOKUP(E2, A2:A100, B2:D100)         → B·C·D열 동시 반환

 

XLOOKUP 인수	설명	기본값
찾을값	검색할 값	필수
검색범위	찾을 열/행	필수
반환범위	결과를 가져올 열/행	필수
못찾을때	없을 때 표시할 값	#N/A
일치유형	0=정확, -1=작은값, 1=큰값, 2=와일드카드	0
검색방향	1=처음부터, -1=끝부터, 2=이진검색	1

 

▸ XLOOKUP 실전 패턴

마지막 일치값 찾기 (검색방향 -1): =XLOOKUP(E2, A2:A100, C2:C100, "없음", 0, -1)   와일드카드 검색 (일치유형 2): =XLOOKUP("김*", A2:A100, C2:C100, "없음", 2) → '김'으로 시작하는 이름 찾기   범위 조회 (근사값, 일치유형 -1): =XLOOKUP(85, A2:A10, B2:B10, , -1) → 85점 이하 최댓값의 학점 반환

 

 

 

7	실전 비교 — VLOOKUP vs INDEX·MATCH vs XLOOKUP

 

기능	VLOOKUP	INDEX·MATCH	XLOOKUP
기본 조회	✅	✅	✅
왼쪽 조회	❌	✅	✅
다중 조건	❌	✅ (배열)	△ (FILTER 권장)
여러 열 반환	❌	△	✅
열 삽입 안전	❌	✅	✅
못찾을때 처리	IFERROR 필요	IFERROR 필요	✅ 내장
버전 호환	모든 버전	모든 버전	365/2021만
복잡도	쉬움	보통	쉬움

 

결론: Excel 365/2021이면 XLOOKUP이 최선이다. 구버전 호환이나 다중 조건 조회가 필요하면 INDEX·MATCH를 쓰고, 단순한 조회는 VLOOKUP도 충분하다.