엑셀 사번 앞자리 0 사라져서 VLOOKUP 안됨? TEXT로 5자리 맞추는 초보 해결법

엑셀에서 사번 앞자리 0이 사라져서 VLOOKUP 안됨, #N/A 오류가 뜨는 경우가 정말 많습니다. 특히 인사팀 명단은 00101처럼 5자리 사번인데, 설문 결과나 교육 참석 명단을 내려받으면 101처럼 바뀌어 있는 상황이 흔합니다.

처음 보면 이름도 맞고 사번도 비슷해 보이는데 왜 못 찾는지 헷갈립니다. 이유는 간단합니다. 엑셀은 00101과 101을 같은 값으로 보지 않을 때가 많고, 특히 한쪽은 텍스트, 한쪽은 숫자이면 조회 함수가 제대로 맞추지 못합니다.

Q. 실제로 어떤 상황에서 이런 문제가 생기나요?

예를 들어 교육 참석 명단과 인사 마스터를 비교해서 직급을 자동으로 가져오려는 상황입니다. 왼쪽 표는 교육 참석 명단이고, 오른쪽 표는 인사 마스터입니다.

아래 예시에서 A열은 참석명단 사번, B열은 이름, C열은 교육명, D열은 참석여부입니다. 인사 마스터는 H열 사번, I열 부서, J열 직급으로 되어 있다고 가정하겠습니다.

인사 마스터는 같은 시트의 H1:J6에 있다고 하겠습니다.

이제 참석 명단의 E열에는 정리사번, F열에는 직급을 표시해 보겠습니다. 데이터는 2행부터 6행까지 있으므로, 수식은 E2와 F2에 입력한 뒤 아래로 복사하면 됩니다.

Q. 왜 101과 00101은 눈으로는 비슷한데 VLOOKUP이 못 찾나요?

엑셀에서 앞자리 0은 숫자 계산에는 의미가 없습니다. 그래서 셀에 00101을 입력하면 기본적으로 101로 바뀌는 경우가 많습니다.

하지만 회사 사번, 상품코드, 거래처코드, 우편번호 같은 값은 계산할 숫자가 아니라 코드입니다. 코드는 자리수가 중요합니다. 101과 00101은 사람 눈에는 같은 사람처럼 보여도, 조회 기준으로는 다르게 취급해야 안전합니다.

이럴 때는 참석 명단의 A열 사번을 인사 마스터와 같은 모양인 5자리 텍스트로 맞춰 준 뒤 조회하는 것이 가장 쉽습니다.

Q. 사번을 5자리로 맞추는 수식은 어디에 입력하나요?

먼저 E1 셀에 정리사번이라고 제목을 입력합니다. 그리고 E2 셀에 아래 수식을 넣습니다.

=TEXT(A2,"00000")

이 수식은 A2에 있는 값을 5자리 형태로 보여 줍니다. A2가 101이면 E2에는 00101이 표시됩니다. A2가 24이면 00024가 됩니다.

여기서 "00000"은 다섯 자리로 맞추겠다는 뜻입니다. 만약 회사 사번이 6자리라면 "000000"으로 바꾸면 됩니다.

E2에 수식을 입력했다면, E2 셀의 오른쪽 아래 작은 네모를 잡고 E6까지 내려 복사합니다. 그러면 A2:A6의 사번이 모두 5자리 사번으로 정리됩니다.

Q. 이제 VLOOKUP은 어떻게 써야 하나요?

F1 셀에 직급이라고 입력합니다. 그리고 F2 셀에 아래 수식을 입력합니다.

=VLOOKUP(E2,$H$2:$J$6,3,FALSE)

수식의 뜻을 천천히 보겠습니다. E2는 찾을 사번입니다. 방금 TEXT 함수로 00101처럼 정리해 둔 값입니다.

$H$2:$J$6은 인사 마스터 범위입니다. H열에 사번, I열에 부서, J열에 직급이 있으므로 H2부터 J6까지 잡았습니다. 앞에 붙은 달러 표시($)는 수식을 아래로 복사해도 범위가 움직이지 않게 고정하는 표시입니다.

3은 선택한 범위 안에서 세 번째 열 값을 가져오겠다는 뜻입니다. H열이 1번째, I열이 2번째, J열이 3번째이므로 직급을 가져오려면 3을 씁니다.

FALSE는 정확히 일치하는 사번만 찾겠다는 뜻입니다. 실무에서는 사번, 코드, 거래처명 조회 시 대부분 FALSE를 사용한다고 기억해도 좋습니다.

Q. #N/A가 계속 뜨면 무엇부터 확인해야 하나요?

#N/A는 “찾는 값이 표 안에 없다”는 뜻입니다. 하지만 실제로는 값이 있는데도 형태가 달라서 못 찾는 경우가 많습니다.

먼저 E열의 정리사번과 H열의 인사 마스터 사번이 같은 자리수인지 확인합니다. E2가 00101인데 H2가 101이면 또 맞지 않습니다. 반대로 E2는 101인데 H2가 00101이어도 문제가 생길 수 있습니다.

자리수를 확인하고 싶으면 빈 셀에 LEN 함수를 써 보면 됩니다. 예를 들어 G2 셀에 아래 수식을 넣으면 E2 값의 글자 수를 확인할 수 있습니다.

=LEN(E2)

결과가 5라면 5자리입니다. H2도 같은 방식으로 확인해 5가 나오는지 보면 됩니다.

Q. 인사 마스터 쪽 사번도 숫자로 되어 있으면 어떻게 하나요?

참석 명단뿐 아니라 인사 마스터의 H열도 101, 102처럼 앞자리 0이 빠져 있을 수 있습니다. 이때는 인사 마스터에도 정리사번 열을 하나 추가하는 것이 가장 깔끔합니다.

예를 들어 K1 셀에 마스터정리사번이라고 입력하고, K2 셀에 아래 수식을 넣습니다.

=TEXT(H2,"00000")

그런 다음 K2:K6까지 복사합니다. 이제 조회 범위를 K열부터 시작하도록 바꾸면 됩니다. 직급이 J열에 있으므로, K열과 J열이 떨어져 있어서 VLOOKUP으로는 조금 불편합니다.

초보자라면 이럴 때 마스터 표를 잠깐 정리해서 정리사번, 부서, 직급 순서로 붙여 두는 방법이 가장 이해하기 쉽습니다. 예를 들어 M열에 정리사번, N열에 부서, O열에 직급을 만들어 두고 조회하면 아래처럼 쓸 수 있습니다.

=VLOOKUP(E2,$M$2:$O$6,3,FALSE)

VLOOKUP은 찾는 값이 범위의 가장 왼쪽 열에 있어야 합니다. 이 부분을 모르면 수식은 맞게 쓴 것 같은데 계속 오류가 나서 헷갈립니다.

Q. Microsoft 365를 쓰면 XLOOKUP으로도 가능한가요?

가능합니다. XLOOKUP은 찾는 열과 가져올 열을 따로 지정할 수 있어서 표의 열 순서를 바꾸지 않아도 됩니다. Microsoft 365 또는 Excel 2021 이후 버전에서 주로 사용할 수 있습니다.

F2 셀에 아래 수식을 입력하면 E2의 정리사번을 H2:H6에서 찾고, J2:J6의 직급을 가져옵니다.

=XLOOKUP(E2,$H$2:$H$6,$J$2:$J$6,"확인필요")

마지막의 "확인필요"는 값을 못 찾았을 때 표시할 문구입니다. VLOOKUP에서 #N/A가 뜨는 대신 확인필요라고 보이게 할 수 있어서 실무 점검용으로 좋습니다.

다만 회사 PC마다 엑셀 버전이 다를 수 있습니다. 아래 수식은 Microsoft 365 최신 버전을 기준으로 설명합니다. Excel 2021/2024 등 영구 버전에서는 일부 함수 지원 여부가 다를 수 있으니, 사용 중인 버전에서 함수 지원 여부를 먼저 확인해 주세요.

Q. 오류를 숨기고 빈칸으로 보이게 할 수 있나요?

보고서 제출용 파일에서는 #N/A가 보이면 지저분해 보일 수 있습니다. 이럴 때는 IFERROR를 함께 쓰면 됩니다.

F2 셀에 VLOOKUP을 쓰는 경우 아래처럼 입력합니다.

=IFERROR(VLOOKUP(E2,$H$2:$J$6,3,FALSE),"")

이 수식은 VLOOKUP 결과가 정상이라면 직급을 보여 주고, 오류가 나면 빈칸으로 보여 줍니다. 다만 처음 검산할 때는 빈칸보다 "확인필요"라고 표시하는 편이 좋습니다.

=IFERROR(VLOOKUP(E2,$H$2:$J$6,3,FALSE),"확인필요")

빈칸으로 숨겨 버리면 진짜 누락인지, 수식 오류인지 놓치기 쉽습니다. 자료를 다 맞춘 뒤 최종 제출용으로 바꿔도 늦지 않습니다.

Q. 같은 사번이 마스터에 두 번 있으면 어떻게 확인하나요?

사번이 중복되어 있으면 VLOOKUP은 위에서 처음 만난 값만 가져옵니다. 그래서 인사 마스터에 같은 사번이 두 번 들어가 있으면 엉뚱한 부서나 직급이 표시될 수 있습니다.

마스터 사번 중복 여부는 COUNTIF로 확인합니다. K1 셀에 중복확인이라고 입력하고, K2 셀에 아래 수식을 넣습니다.

=COUNTIF($H$2:$H$6,H2)

결과가 1이면 한 번만 있는 사번입니다. 결과가 2 이상이면 같은 사번이 마스터에 여러 번 있다는 뜻이므로, 어느 행이 맞는지 먼저 정리해야 합니다.

만약 H열 사번이 앞자리 0이 섞인 상태라면, 앞에서 만든 정리사번 열을 기준으로 COUNTIF를 하는 것이 더 안전합니다.

Q. 처음부터 앞자리 0이 안 사라지게 입력할 수는 없나요?

가능합니다. 사번을 입력하기 전에 해당 열을 선택하고 셀 서식을 텍스트로 바꾸면 됩니다.

예를 들어 A열에 사번을 입력할 예정이라면, A열 머리글을 클릭해서 열 전체를 선택합니다. 그다음 상단 메뉴에서 홈 > 표시 형식을 열고 텍스트를 선택합니다.

그 상태에서 00101을 입력하면 앞자리 0이 유지됩니다. 단, 이미 101로 바뀐 뒤에는 서식만 텍스트로 바꿔도 자동으로 00101이 되돌아오지는 않습니다. 이미 바뀐 값은 TEXT 함수로 다시 자리수를 맞춰야 합니다.

Q. 내 파일에 적용할 때 셀 주소는 어디를 바꾸면 되나요?

핵심은 세 곳입니다. 첫째, TEXT 함수 안의 A2는 내 파일에서 원본 사번이 있는 셀로 바꿉니다. 둘째, "00000"은 사번 자리수에 맞게 바꿉니다. 셋째, VLOOKUP의 $H$2:$J$6은 내 인사 마스터 범위로 바꿉니다.

예를 들어 내 파일에서 원본 사번이 C열, 인사 마스터가 P열부터 R열까지라면 이런 식으로 바뀔 수 있습니다.

=TEXT(C2,"00000")

=VLOOKUP(E2,$P$2:$R$500,3,FALSE)

데이터가 500행까지 있다면 범위도 500행까지 잡아야 합니다. 범위를 너무 짧게 잡으면 아래쪽 직원은 조회되지 않습니다.

이럴 때 이렇게 쓰면 된다

사번, 상품코드, 거래처코드처럼 앞자리 0이 의미 있는 값은 숫자가 아니라 코드로 봐야 합니다. 조회가 안 될 때는 VLOOKUP 수식부터 고치기보다, 먼저 양쪽 코드의 자리수와 형태가 같은지 확인하는 것이 빠릅니다.

원본 사번이 101처럼 들어왔고 마스터가 00101이라면 TEXT(A2,"00000")로 정리사번을 만든 뒤 조회하세요. VLOOKUP을 쓴다면 찾는 사번이 표의 가장 왼쪽 열에 오게 만들고, XLOOKUP을 쓸 수 있는 환경이라면 찾는 열과 가져올 열을 따로 지정하면 됩니다.

실무에서는 오류를 바로 숨기지 말고 처음에는 확인필요로 표시해 두는 편이 안전합니다. 자리수 맞추기, 중복 확인, 범위 확인까지 보면 대부분의 사번 조회 오류는 금방 잡힙니다.