VLOOKUP만으로 찾기 힘든 엑셀 데이터 찾는 방법

 

가끔은 VLOOKUP으로 찾기 힘든 데이터가 있습니다.

VLOOKUP은 항상 왼쪽 기준 열에서 오른쪽으로만 찾을 수 있어서 찾아야 하는 자료가 기준대로 있지 않으면 

사용할 수 없습니다. 그래서 찾고 찾은 함수입니다.

직원명단이 부서 / 이름 / 직급 순으로 되어있는데,  3번째 행에 있는 사람의 부서명을 찾아야 할 경우.

아래와 같이 볼수 있죠.

 

 

INDEX 함수 - 위치로 값 꺼내오기

 

=INDEX(범위, 행번호, [열번호])

실무 예시 - 직원 명단에서 3번째 직원의 부서명 가져오기

=INDEX(A2:D50, 3, 2)

A2:D50 범위에서 3번째 행, 2번째 열에 있는 값을 반환합니다. 부서가 2열에 있다면 3번째 직원의 부서명이 나옵니다.

 

엑셀에서 찾을 때 vlookup으로 안되는 경우 사용하는 함수

 

 

INDEX는 좌표를 지정해서 "꺼내오는" 방식이라 훨씬 유연합니다.

저는 INDEX를 사용하고 파일 로딩이 빨라진 걸 체감했습니다. 느낌만 그럴수도 있죠.

 

 

 INDEX + MATCH  =  VLOOKUP의 한계를 넘는 조합

 

 

INDEX 혼자서는 "몇 번째"를 직접 입력해야 하지만,

MATCH와 결합하면 "어떤 값을 찾아서" 그 위치를 자동으로 계산할 수 있습니다.

이 조합이 실무에서 가장 많이 쓰이는 조회 패턴입니다.

 

=INDEX(반환할범위, MATCH(찾을값, 기준범위, 0))

 

실무 예시 - 사번으로 이름이 아닌 부서명 찾기 (왼쪽 열 조회)

=INDEX(B2:B50, MATCH(F2, C2:C50, 0))

 

F2에 입력한 값을 C2:C50(사번 열)에서 찾아 그 위치를 구하고, 같은 위치의

B2:B50(부서명 열, 사번보다 왼쪽에 위치) 값을 반환합니다. VLOOKUP이라면 사번 열을

기준으로 오른쪽 열만 찾을 수 있어 불가능한 상황입니다.

 

실무 예시 - 열 제목으로 값 찾기 (양방향 조회)

=INDEX(B2:F50, MATCH(G2, A2:A50, 0), MATCH(H2, B1:F1, 0))

 

G2의 행 조건과 H2의 열 조건을 동시에 만족하는 교차 지점의 값을 찾습니다.

월별×부서별 매트릭스 표에서 특정 셀 값을 조회할 때 유용합니다.

핵심 MATCH의 세 번째 인수는 반드시 0(정확히 일치)으로 입력하세요.

생략하면 근사값을 찾아 엉뚱한 결과가 나올 수 있습니다.

 

CHOOSE 함수 - 번호로 값 선택하기

 

CHOOSE는 순번을 지정하면 나열된 값들 중 해당 순번의 값을 반환하는 함수입니다.

조건에 따라 몇 가지 정해진 값 중 하나를 고를 때 IF 중첩보다 간결하게 쓸 수 있습니다.

 

=CHOOSE(순번, 값1, 값2, 값3, ...)

 

실무 예시 - 분기 번호로 분기명 표시

=CHOOSE(ROUNDUP(MONTH(B2)/3,0), "1분기", "2분기", "3분기", "4분기")

 

월(月)에서 분기 번호를 계산한 뒤, 그 번호에 해당하는 분기명을 바로 가져옵니다.

C2가 2월이면 분기 번호는 1이 되어 "1분기"가 반환됩니다.

 

결과: 1분기

엑셀에서 번호로 값 선택하기

 

 

실무 예시 - 평가 등급 번호를 등급명으로 변환

 

=CHOOSE(C2, "S등급", "A등급", "B등급", "C등급")

C2에 1~4 사이의 숫자 등급이 입력되어 있으면, 해당 순번의 등급명 텍스트로 바꿔줍니다.

 

엑셀에서 숫자를 다른 등급으로 변환할 때 사용법

 

주의 CHOOSE의 순번이 1보다 작거나 나열된 값 개수보다 크면 #VALUE! 오류가 발생합니다.

순번 계산식에 오류 가능성이 있다면 IFERROR로 감싸는 것이 안전합니다.

4 OFFSET 함수 - 기준점에서 이동한 범위 참조

OFFSET은 기준 셀에서 지정한 행·열만큼 이동한 위치의 셀(또는 범위)을 참조하는 함수입니다.

동적으로 크기가 변하는 범위를 다룰 때 강력하지만, 다루기 까다로운 함수이기도 합니다.

 

=OFFSET(기준셀, 이동행수, 이동열수, [높이], [너비])

 

실무 예시 - 기준 셀에서 두 칸 아래 값 가져오기

=OFFSET(A1, 2, 0)

 

A1을 기준으로 아래로 2행, 오른쪽으로 0열 이동한 A3 셀의 값을 반환합니다.

 

실무 예시 - 최근 N개월 매출만 동적으로 합산

=SUM(OFFSET(B2, 0, 0, 1, F2))

 

F2에 입력한 개월 수만큼 B2부터 오른쪽으로 범위를 자동으로 늘려가며 합산합니다.

F2 값을 3에서 6으로 바꾸면 합산 범위도 자동으로 넓어집니다.

 

※ 주의

 

OFFSET은 휘발성 함수입니다. 시트의 어떤 셀이라도 바뀌면 전체가 재계산되어,

데이터가 많은 파일에서는 속도가 눈에 띄게 느려질 수 있습니다. 가능하면 INDEX로 대체하는 것을 권장합니다.

OFFSET을 INDEX로 대체하기

위의 동적 합산 예시는 OFFSET 없이 INDEX로도 동일하게 구현할 수 있습니다.

=SUM(B2:INDEX(B2:Z2, F2))

INDEX는 휘발성 함수가 아니라서 불필요한 재계산이 없고, 대용량 시트에서 속도 차이가 크게 납니다.

 

 

실무 케이스 모음

 

① 거래처명으로 담당자·연락처 동시 조회

=INDEX(C:C, MATCH(F2, A:A, 0))

 

② 최고 매출 담당자 이름 찾기 (INDEX+MATCH+MAX)

=INDEX(B2:B50, MATCH(MAX(C2:C50), C2:C50, 0))

 

③ 요일 번호로 요일명 표시

=CHOOSE(WEEKDAY(A2), "일", "월", "화", "수", "목", "금", "토")

 

④ 등급별 할인율을 CHOOSE로 매핑

=CHOOSE(D2, 0.1, 0.05, 0.02, 0)

 

⑤ 드롭다운 선택값에 따라 다른 시트 참조 (CHOOSE+VLOOKUP)

=VLOOKUP(F2, CHOOSE(G2, 1월!A:C, 2월!A:C, 3월!A:C), 3, FALSE)

 

⑥ 최근 입력된 마지막 값 가져오기 (OFFSET+COUNTA)

=OFFSET(A1, COUNTA(A:A)-1, 0)

 

⑦ 이름과 부서 두 조건으로 급여 조회 (INDEX+MATCH 배열)

=INDEX(D2:D50, MATCH(F2&G2, B2:B50&C2:C50, 0))

위 수식은 배열수식이라 Microsoft 365 최신 버전이 아니라면 Ctrl+Shift+Enter로 입력해야 합니다.

 

 

업무 상황	추천 함수	이유
기준 열보다 왼쪽 열 조회	INDEX+MATCH	VLOOKUP은 왼쪽 열 조회 불가
정해진 몇 개 값 중 순번으로 선택	CHOOSE	IF 중첩보다 간결
동적으로 늘어나는 범위 합산·참조	OFFSET (또는 INDEX)	범위 크기가 매번 바뀔 때
대용량 시트에서 속도 중요	INDEX 우선 고려	OFFSET은 휘발성 함수라 느려짐

 

 

INDEX

• 좌표(행·열 번호)로 값을 꺼냄
• MATCH와 결합해 양방향 조회 가능
• 휘발성 아님, 속도 안정적

CHOOSE

• 순번으로 정해진 값 중 선택
• 값 목록이 고정적일 때 적합
• 순번 범위를 벗어나면 오류

OFFSET

• 기준점에서 이동한 위치·범위 참조
• 동적 범위에 강함
• 휘발성 함수 - 대용량엔 비권장

 

공통 주의사항

• INDEX 두 번째·세 번째 인수 순서 혼동 — 행번호가 먼저, 열번호가 나중입니다. 반대로 넣으면 엉뚱한 값이 나오거나 오류가 납니다.
• MATCH 세 번째 인수 생략 — 생략 시 기본값 1(근사값 일치)로 동작해, 데이터가 정렬되어 있지 않으면 완전히 틀린 결과를 반환합니다. 항상 0을 명시하세요.
• CHOOSE 순번 계산식의 소수점·범위 오류 — ROUNDUP 등으로 순번을 계산할 때 결과가 정수 범위를 벗어나지 않는지 미리 확인해야 합니다.
• OFFSET 남용으로 인한 파일 속도 저하 — OFFSET을 여러 셀에 반복해서 쓰면 시트 전체가 느려질 수 있습니다. 가능한 범위에서는 INDEX 기반 수식으로 대체하는 것이 좋습니다.
• 범위 참조가 셀 삽입·삭제로 깨짐 — INDEX/OFFSET 모두 참조 범위 안에 행이나 열을 삽입·삭제하면 수식이 예상과 다르게 동작할 수 있어, 수정 후 결과를 반드시 재확인해야 합니다.