VLOOKUP TEXT 함수 조합으로 코드 형식 맞추기: 앞자리 0·날짜·텍스트 오류 완전 해결
엑셀에서 VLOOKUP만 잘 써도 웬만한 조회는 다 됩니다. 하지만 실무에서는 한쪽은 001234, 다른 쪽은 1234로 되어 있거나, 긴 숫자 코드가 1.23E+11처럼 과학적 표기로 바뀌어 버리는 등 형식 때문에 조회가 안 되는 문제가 자주 발생합니다.
이 글에서는 VLOOKUP TEXT 함수 조합을 이용해서 앞자리 0(leading zero) 문제, 숫자와 텍스트 형식 차이, 서로 다른 날짜 형식, 과학적 표기 문제를 한 번에 해결하는 패턴을 정리해 보겠습니다.
왜 VLOOKUP TEXT 함수 조합이 필요한가?
먼저 두 함수의 역할을 간단히 정리해 보겠습니다.
- VLOOKUP: 테이블의 첫 번째 열에서 기준 값을 찾고, 같은 행의 N번째 열 값을 가져오는 함수
- TEXT: 숫자(또는 날짜·시간)를 지정한 서식 문자열(format code)에 맞는 텍스트로 변환하는 함수
실무에서 자주 터지는 오류는 이 둘의 “형식”이 맞지 않아서 생깁니다. VLOOKUP은 1234(숫자)를 찾고 있는데 실제 테이블에는 “001234”(텍스트)로 들어 있다면, 값은 같아 보여도 형식이 달라 일치하지 않는 것으로 판단되어 #N/A가 발생합니다.
이럴 때 TEXT 함수로 lookup_value 쪽을 "000000" 같은 형식의 텍스트로 바꿔서 코드표와 형식을 맞춘 다음 VLOOKUP을 수행하면 문제가 깔끔하게 해결됩니다.
Quick Fix: 지금 당장 써먹는 VLOOKUP TEXT 함수 조합 4가지
1) 앞자리 0 맞추기: 숫자 → 고정 자리 텍스트
문제 상황: A열은 숫자 코드(123, 45), 코드표는 6자리 텍스트(000123, 000045)로 관리되는 경우입니다.
=VLOOKUP(
TEXT(A2, "000000"),
$F$2:$G$100,
2,
FALSE
)TEXT(A2, "000000")은 A2가 123일 때 “000123”으로 변환하므로 코드표의 형식과 정확히 일치하게 됩니다.
2) 날짜 형식 통일: 날짜 → yyyymmdd 텍스트
문제 상황: 조회 시트는 날짜가 날짜 형식(2024-01-15), 코드표는 yyyymmdd 텍스트(20240115)로 되어 있는 경우입니다.
=VLOOKUP(
TEXT(A2, "yyyymmdd"),
$F$2:$G$100,
2,
FALSE
)3) 과학적 표기로 깨진 긴 숫자 코드 찾기
문제 상황: 13자리 이상의 긴 숫자 코드를 붙여 넣었더니 1.23E+11처럼 과학적 표기로 바뀌는 경우입니다.
=VLOOKUP(
TEXT(A2, "0000000000000"),
$F$2:$G$100,
2,
FALSE
)4) 숫자/텍스트 섞인 코드 통일
한쪽은 숫자, 다른 쪽은 텍스트로 저장되어 있어 같은 값인데도 VLOOKUP이 안 될 때 사용할 수 있는 패턴입니다.
=VLOOKUP(
TEXT(A2, "0"),
$F$2:$G$100,
2,
FALSE
)TEXT 함수 기본 개념 & 자주 쓰는 형식 코드
TEXT 함수의 기본 문법은 다음과 같습니다.
=TEXT(value, format_text)value: 숫자, 날짜, 시간 등 형식을 바꾸고 싶은 값format_text: 적용할 표시 형식 코드(문자열)
| 용도 | 예시 수식 | 결과 예시 |
|---|---|---|
| 앞자리 0 패딩(6자리) | =TEXT(123, "000000") |
“000123” |
| 날짜 yyyymmdd | =TEXT(DATE(2024,1,15),"yyyymmdd") |
“20240115” |
| 연-월-일 텍스트 | =TEXT(A2, "yyyy-mm-dd") |
“2024-01-15” |
| 시간 hhmm | =TEXT(A2, "hhmm") |
“0930” |
실무 예제 1: 상품코드·바코드의 앞자리 0 문제 해결
예제 시나리오
조회 시트 (판매 데이터):
| A열(바코드) | B열(판매수량) |
|---|---|
| 12345 | 10 |
| 7 | 5 |
| 89 | 3 |
코드표 시트:
| F열(바코드) | G열(상품명) |
|---|---|
| 000007 | 상품A |
| 000089 | 상품B |
| 012345 | 상품C |
잘못된 VLOOKUP 예
=VLOOKUP(A2, $F$2:$G$4, 2, FALSE)A2는 숫자 12345, F열 값은 “012345” 텍스트이므로 형식이 달라 #N/A가 발생합니다.
TEXT로 lookup_value 형식 맞추기
=VLOOKUP(
TEXT(A2, "000000"),
$F$2:$G$4,
2,
FALSE
)코드표 쪽에 보조열 추가하기
코드표 시트에 H열(보조 코드)을 추가하여 다음과 같이 입력합니다.
=TEXT(VALUE(F2), "000000")그리고 VLOOKUP에서는 보조열을 기준으로 조회합니다.
=VLOOKUP(
TEXT(A2, "000000"),
$H$2:$I$4, /* H: 보조코드, I: 상품명 */
2,
FALSE
)실무 예제 2: 서로 다른 날짜 형식 맞춰서 VLOOKUP 하기
예제 시나리오
매출 시트:
| A열(매출일자) | B열(고객코드) | C열(매출액) |
|---|---|---|
| 2024-01-15 | C001 | 100,000 |
| 2024-01-28 | C002 | 80,000 |
환율 테이블:
| F열(기준일, yyyymmdd) | G열(환율) |
|---|---|
| 20240131 | 1320.5 |
| 20240229 | 1315.0 |
월말 날짜 구하기 + TEXT 변환
=EOMONTH(A2, 0)위 수식은 매출일자가 속한 달의 월말 날짜를 반환합니다. 이를 yyyymmdd 텍스트로 변환하면 다음과 같습니다.
=TEXT(EOMONTH(A2, 0), "yyyymmdd")최종 VLOOKUP TEXT 함수 조합
=VLOOKUP(
TEXT(EOMONTH(A2, 0), "yyyymmdd"),
$F$2:$G$10,
2,
FALSE
)실무 예제 3: 복합 코드 + TEXT로 포맷 통일
코드 구조
- 브랜드: 2자리 숫자
- 카테고리: 2자리 숫자
- 색상: 3자리 숫자
- 사이즈: 2자리 숫자
조회 시트에서 B열=브랜드, C열=카테고리, D열=색상, E열=사이즈라면 다음과 같이 코드 문자열을 만들 수 있습니다.
=TEXT(B2, "00")
& TEXT(C2, "00")
& TEXT(D2, "000")
& TEXT(E2, "00")VLOOKUP TEXT 함수 조합
=VLOOKUP(
TEXT(B2, "00")
& TEXT(C2, "00")
& TEXT(D2, "000")
& TEXT(E2, "00"),
$J$2:$K$100,
2,
FALSE
)다른 텍스트 함수와 함께 쓰는 보너스 조합
LEFT/RIGHT/MID + TEXT
코드의 일부만 기준으로 조회할 때 사용할 수 있습니다.
=VLOOKUP(
TEXT(RIGHT(A2, 2), "00"),
$F$2:$G$100,
2,
FALSE
)TRIM + TEXT
복사해온 데이터에 공백이 섞여 있을 때는 TRIM과 TEXT를 함께 사용합니다.
=VLOOKUP(
TEXT(TRIM(A2), "000000"),
$F$2:$G$100,
2,
FALSE
)VALUE + TEXT
텍스트 숫자를 일단 숫자로 정규화한 후 자릿수를 맞추고 싶다면 다음과 같은 패턴이 유용합니다.
=TEXT(VALUE(A2), "000000")Troubleshooting: TEXT + VLOOKUP 조합에서 자주 터지는 문제
| 증상 | 원인(추정) | 해결법/체크 포인트 |
|---|---|---|
| TEXT까지 썼는데도 VLOOKUP이 여전히 #N/A | 자리수 또는 형식 코드가 코드표와 다름 | 코드표 한 셀의 길이와 형식을 직접 확인하고 format_text를 정확히 맞추기 |
| 코드를 TEXT로 바꾼 뒤 정렬 순서가 이상함 | 숫자가 아닌 텍스트로 정렬됨 | 정렬 기준 열이 숫자인지 텍스트인지 확인, 필요하면 VALUE로 별도 열 생성 |
| 긴 숫자 코드를 붙여 넣으면 1.23E+11로 바뀜 | 엑셀이 자동으로 과학적 표기 형식을 적용 | 붙여넣기 전에 열 서식을 텍스트로 지정하거나 TEXT/VALUE 조합으로 수정 |
| 날짜 TEXT 변환 뒤 VLOOKUP이 안 됨 | 기준 테이블의 날짜 코드 형식과 불일치 | yyyymmdd, yyyy-mm-dd 등 정확한 형식을 일치시키기 |
| 숫자/텍스트 형식이 섞여 있고 어느 쪽이 문제인지 알기 어려움 | 같은 값인데 왼쪽 정렬/오른쪽 정렬이 섞여 있음 | ISNUMBER, ISTEXT로 타입을 확인하고 TEXT 또는 VALUE로 통일 |
마무리: VLOOKUP TEXT 함수 조합 연습 루틴
VLOOKUP TEXT 함수 조합은 다음과 같은 상황에서 특히 유용합니다.
- 앞자리 0이 포함된 코드(고객번호, 바코드, PLU 등)를 다룰 때
- 날짜 형식이 서로 다른 시트/시스템을 연결할 때
- 긴 숫자 코드가 과학적 표기로 깨질 때
- 숫자와 텍스트 형식이 섞인 ID/코드를 통일하고 싶을 때
연습 순서는 다음과 같이 가져가면 좋습니다.
- 현재 사용하는 VLOOKUP 중 앞자리 0 또는 날짜 문제로 자주 막히는 수식 하나를 고른다.
- lookup_value 부분을 TEXT 함수로 감싸 형식을 먼저 통일해 본다.
- 월말 환율/요율 등 날짜 기준 테이블이 있다면 EOMONTH + TEXT + VLOOKUP 패턴을 직접 적용해 본다.
- 복합 코드(브랜드+카테고리+색상+사이즈)는 여러 TEXT를 이어 붙이는 패턴으로 직접 코드를 만들어 본다.
형식 문제를 TEXT로 해결하는 패턴에 익숙해지면, 나머지 조회 구조(열 번호, 다중 조건 등)는 아래 글들과 함께 보면서 단계적으로 확장해 나가면 됩니다.
- VLOOKUP 함수 조합 완전 정리: 조회 문제 20가지 패턴 한 번에 끝내기
- VLOOKUP MATCH 함수로 열 번호 자동 찾기
- VLOOKUP 다중 조건 공식 정리
- VLOOKUP 오류(#N/A, #VALUE!) 해결법 모음
- INDEX MATCH로 VLOOKUP 한계 넘기기