VLOOKUP 함수 조합 완전 정리: 조회 문제 20가지 패턴 한 번에 끝내기
엑셀에서 조회 문제 대부분은 VLOOKUP 함수 조합으로 상당 부분 해결할 수 있습니다. 하지만 VLOOKUP 하나만으로는 한계가 분명하고, 오류·다중 조건·코드 형식 불일치 같은 문제로 자주 막히죠.
이 글에서는 VLOOKUP과 궁합이 좋은 함수들을 묶어서, 오류 처리, 열 번호 자동화, 텍스트/코드 전처리, 다중 조건 조회, 구간별 요율·합계 계산까지 한 번에 정리합니다. 글을 다 읽고 나면 “아, 이 상황엔 이 조합 쓰면 되겠구나”가 머릿속에 딱 정리될 거예요.
VLOOKUP 함수 조합, 왜 꼭 알아야 할까?
VLOOKUP은 여전히 가장 많이 쓰이는 조회 함수입니다. 공식은 간단하지만, 실제 업무에서 부딪히는 문제는 간단하지 않습니다.
- 같은 코드인데 한쪽은
00123, 다른 쪽은123이라 조회가 안 되는 경우 - 상품코드 + 색상 + 사이즈 같이 복합 조건으로 찾아야 하는데, VLOOKUP은 조건을 하나만 받는 경우
- 코드가 없으면
#N/A오류가 떠서 보고서가 엉망으로 보이는 경우 - 열이 추가·삭제될 때마다
col_index_num을 바꾸느라 수식이 깨지는 경우
이럴 때 필요한 것이 바로 다양한 함수와 결합한 VLOOKUP 패턴입니다. 이 글은 “어떤 함수와 어떻게 조합하면 어떤 문제가 해결되는지” 관점으로 정리합니다.
Quick Fix: 지금 당장 써먹는 VLOOKUP 필수 조합 5가지
먼저 “이 다섯 개만 알아도 당장 업무 체감이 달라지는” 조합부터 빠르게 보겠습니다. 각 조합은 3단계로 익히면 됩니다.
1) IFERROR + VLOOKUP: 오류 대신 깔끔한 메시지
문제: VLOOKUP에서 값이 없으면 #N/A 오류가 떠서 보고서가 지저분해 보입니다.
=IFERROR(
VLOOKUP(A2, $F$2:$H$100, 3, FALSE),
"미등록 코드"
)수식이 긴 경우도 IFERROR는 똑같이 감싸주기만 하면 됩니다.
2) IF + VLOOKUP: 가져온 값으로 등급 나누기
문제: VLOOKUP으로 매출/점수 등을 가져온 뒤 라벨(등급)을 붙이고 싶을 때.
=IF(
VLOOKUP(A2, $F$2:$G$100, 2, FALSE) >= 1000000,
"VIP",
"일반"
)3) MATCH + VLOOKUP: 열 번호를 자동으로 바꾸기
=VLOOKUP(
$A2,
$F$1:$M$100,
MATCH($B$1, $F$1:$M$1, 0),
FALSE
)B1 셀에 “2023매출”, “2024매출”, “재고수량” 같은 헤더를 선택하면, 해당 열 번호를 MATCH가 찾아서 VLOOKUP이 사용합니다.
4) TEXT + VLOOKUP: 0으로 시작하는 코드 맞추기
=VLOOKUP(
TEXT(A2, "000000"),
$F$2:$G$100,
2,
FALSE
)숫자를 고정 자릿수 텍스트로 변환해 코드 형식을 통일합니다.
5) & 연산자 + VLOOKUP: 다중 조건 조회 키 만들기
=VLOOKUP(
A2 & "|" & B2,
$F$2:$H$100,
3,
FALSE
)코드표 시트에는 =F2 & "|" & G2 같은 보조열을 만들어 동일한 규칙으로 키를 맞춰 줍니다.
오류를 다루는 VLOOKUP 조합: IFERROR / IFNA / ISNA / ISERROR
VLOOKUP은 값만 못 찾아도 바로 #N/A를 띄웁니다. 그 자체로는 친절하지만, 보고서에는 좋지 않습니다. 중요한 것은 오류를 숨기는 것이 아니라 오류를 관리하는 것입니다.
IFERROR + VLOOKUP (기본형)
=IFERROR(
VLOOKUP(A2, $F$2:$H$100, 3, FALSE),
""
)IFNA + VLOOKUP: #N/A만 잡고 싶을 때
=IFNA(
VLOOKUP(A2, $F$2:$H$100, 3, FALSE),
"코드 없음"
)ISNA / ISERROR + IF: 오류 여부에 따라 분기
=IF(
ISNA(VLOOKUP(A2, $F$2:$H$100, 3, FALSE)),
"미등록",
"정상"
)열·범위 자동화 VLOOKUP 조합: MATCH / COLUMN / CHOOSE / OFFSET
MATCH + VLOOKUP: 헤더 기반 열 선택
=VLOOKUP(
$A2,
$F$1:$M$100,
MATCH($B$1, $F$1:$M$1, 0),
FALSE
)COLUMN + VLOOKUP: 복사 방향에 따라 열 번호 자동 증가
=VLOOKUP(
$A2,
$F$2:$M$100,
COLUMN(G:G) - COLUMN($F:$F) + 1,
FALSE
)CHOOSE + VLOOKUP: 오른쪽→왼쪽 역방향 조회
=VLOOKUP(
A2,
CHOOSE({1,2}, $H$2:$H$100, $F$2:$F$100),
2,
FALSE
)OFFSET + VLOOKUP: 기간/구간에 따라 범위 슬라이드
=VLOOKUP(
A2,
OFFSET($F$2, 0, $B$1*3, 100, 3),
3,
FALSE
)텍스트·코드 전처리 VLOOKUP 조합: LEFT / RIGHT / MID / TRIM / SUBSTITUTE / UPPER
LEFT / RIGHT / MID + VLOOKUP: 복합코드를 분해해서 쓰기
=VLOOKUP(
LEFT(A2, 3),
$P$2:$Q$50,
2,
FALSE
)TRIM + VLOOKUP: 공백 때문에 조회 안 될 때
=VLOOKUP(
TRIM(A2),
$F$2:$H$100,
3,
FALSE
)SUBSTITUTE + UPPER + VLOOKUP: 구분자·대소문자 통일
=VLOOKUP(
UPPER(SUBSTITUTE(A2, "-", "")),
$F$2:$G$100,
2,
FALSE
)합계·중복·구간별 요율과 함께 쓰는 VLOOKUP: SUMIFS / COUNTIFS / MAX / MIN
단가를 VLOOKUP으로 가져와서 매출액 계산 (SUMPRODUCT 패턴)
=SUMPRODUCT(
VLOOKUP($A$2:$A$100, $F$2:$G$50, 2, FALSE),
$B$2:$B$100
)COUNTIFS + VLOOKUP: 중복된 코드 중 첫 번째만 조회
=IF(
COUNTIFS($A$2:$A2, A2)=1,
VLOOKUP(A2, $F$2:$G$100, 2, FALSE),
""
)MAX / MIN + VLOOKUP: 구간별 요율표 조회
=VLOOKUP(
MAX(IF($F$2:$F$5 <= B2, $F$2:$F$5)),
$F$2:$G$5,
2,
TRUE
)날짜·기간 테이블과 VLOOKUP: DATE / YEAR / MONTH / EOMONTH
월말 기준 환율·요율 테이블 조회
=VLOOKUP(
EOMONTH(A2, 0),
$F$2:$G$100,
2,
FALSE
)연월 텍스트 키로 VLOOKUP
=VLOOKUP(
YEAR(A2) & "-" & TEXT(MONTH(A2), "00"),
$J$2:$K$100,
2,
FALSE
)동적 배열·시트 전환과 VLOOKUP: UNIQUE / FILTER / INDIRECT
UNIQUE + VLOOKUP: 고유 PLU 리스트 + 속성 붙이기
=LET(
uPLU, UNIQUE(A2:A100),
HSTACK(
uPLU,
VLOOKUP(uPLU, $F$2:$G$100, 2, FALSE)
)
)FILTER + VLOOKUP: 특정 조건의 행만 추출
=FILTER(
A2:D100,
VLOOKUP(B2:B100, $J$2:$K$50, 2, FALSE)="VIP"
)INDIRECT + VLOOKUP: 시트 선택에 따라 참조 범위 변경
=VLOOKUP(
A2,
INDIRECT("'" & $B$1 & "'!$F$2:$H$100"),
3,
FALSE
)Troubleshooting 표: VLOOKUP 문제 상황별 원인과 해결 조합
| 증상 | 원인(추정) | 해결에 추천하는 VLOOKUP 함수 조합 |
|---|---|---|
| #N/A 오류가 많이 뜨고 보고서가 지저분함 | 코드 없음, 오타, 일부 데이터 미등록 | IFERROR + VLOOKUP, IFNA + VLOOKUP |
| 같은 코드인데 어떤 건 조회되고 어떤 건 안 됨 | 앞뒤 공백, 숨은 문자, 대소문자, 하이픈 차이 | TRIM/SUBSTITUTE/UPPER + VLOOKUP |
| 한쪽은 00123, 다른 쪽은 123 로 돼 있어 안 맞음 | 텍스트/숫자 형식 차이, 앞자리 0 유무 | TEXT/VALUE + VLOOKUP |
| 열이 추가되거나 순서가 바뀔 때마다 수식이 깨짐 | col_index_num를 숫자로 고정 | MATCH + VLOOKUP, COLUMN + VLOOKUP |
| 상품코드 + 색상 + 사이즈 같이 여러 조건으로 찾기 | VLOOKUP이 조건 하나만 받음 | & (CONCAT) + VLOOKUP (다중 조건 키 만들기) |
| 바코드(오른쪽 열)로 상품코드(왼쪽 열)를 찾고 싶음 | VLOOKUP은 왼쪽→오른쪽만 가능 | CHOOSE + VLOOKUP (가상 테이블로 열 순서 뒤집기) |
| 월별/분기별로 범위가 옆으로 이어져 있고, 기간 선택형 보고서를 만들고 싶음 | 기간에 따라 범위를 바꾸기 어려움 | OFFSET + VLOOKUP, INDIRECT + VLOOKUP |
| 수량 구간별로 다른 요율/배송비/포인트를 적용해야 함 | 구간 테이블 필요 | MAX/MIN + VLOOKUP(TRUE) (근사값 모드) |
| PLU별 단가를 적용한 총 매출액을 한 번에 계산하고 싶음 | 수량과 단가를 각각 곱해 합계 필요 | VLOOKUP + SUMPRODUCT, VLOOKUP + SUMIFS |
| 고유 PLU 목록 + 속성을 동적으로 뽑고 싶음 | 중복값 제거 + 조회 필요 | UNIQUE + VLOOKUP |
마무리: 어떤 VLOOKUP 함수 조합부터 익히면 좋을까?
처음부터 모든 VLOOKUP 함수 조합을 완벽하게 외울 필요는 없습니다. 실무에서 자주 쓰이는 순서대로 익히는 것이 훨씬 효율적입니다.
- 1단계 (기본 안정화): IFERROR + VLOOKUP, MATCH + VLOOKUP, TEXT/TRIM + VLOOKUP
- 2단계 (조건·구간 처리): 다중 조건 키 + VLOOKUP, COUNTIFS + VLOOKUP, MAX + VLOOKUP(TRUE)
- 3단계 (구조적인 한계 극복): CHOOSE + VLOOKUP, OFFSET/INDIRECT + VLOOKUP, UNIQUE/FILTER + VLOOKUP
여기서 다룬 패턴들을 자신의 엑셀 파일에 한 번씩 적용해 보면서, “우리 회사 PLU/매출/재고 데이터는 어떤 VLOOKUP 함수 조합으로 풀 수 있을까?”를 같이 고민해 보세요. VLOOKUP의 한계를 느끼기 시작했다면 INDEX+MATCH, XLOOKUP으로 넘어갈 준비가 된 것입니다.
관련해서 아래 글들도 함께 보면 이해가 훨씬 빨라집니다.
- VLOOKUP 다중 조건 완전 정리
- TEXT 함수와 함께 쓰는 VLOOKUP 코드 정리 팁
- INDEX MATCH로 VLOOKUP 한계 넘기기
- VLOOKUP 오류(#N/A, #VALUE!) 해결법 총정리
- XLOOKUP + SUMPRODUCT로 만드는 자동 보고서