엑셀 VLOOKUP #N/A 뜰 때: 거래처코드로 거래처명 자동 입력하는 초보 확인법

엑셀 VLOOKUP #N/A 오류는 거래처코드로 거래처명을 자동 입력할 때 정말 자주 만납니다. 분명히 코드표에는 코드가 있는데 주문내역에서는 #N/A가 뜨고, 어떤 행은 잘 나오는데 어떤 행만 안 나와서 더 헷갈리죠.

오늘은 아주 초보자 기준으로, 거래처코드를 입력하면 거래처명이 자동으로 채워지는 파일을 예로 들어 보겠습니다. 어느 셀에 수식을 넣는지, 범위는 어디부터 어디까지 잡는지, 왜 복사하면 수식이 틀어지는지까지 차근차근 확인해 보겠습니다.

Q. 거래처코드를 입력하면 거래처명이 자동으로 나오게 하려면 어디에 수식을 넣나요?

예시 파일은 한 시트 안에 주문내역과 거래처표가 같이 있다고 가정하겠습니다. 주문내역은 A4:E10 범위에 있고, 거래처표는 H4:I8 범위에 있습니다.

여기서 B열은 거래처코드를 입력하는 칸이고, C열은 거래처명을 자동으로 표시할 칸입니다. 따라서 첫 번째 결과 셀은 C5입니다. C5에 수식을 넣은 뒤 아래로 복사하면 됩니다.

예시 데이터는 아래처럼 작게 시작해 보겠습니다. 실제 파일에서는 행이 수백 줄이어도 원리는 같습니다.

거래처표는 H4:I8에 둡니다. 중요한 점은 찾을 값인 거래처코드가 범위의 첫 번째 열에 있어야 한다는 것입니다.

Q. C5에 넣는 기본 VLOOKUP 수식은 어떻게 쓰나요?

C5 셀을 클릭하고 아래 수식을 입력합니다. 그런 다음 C5 오른쪽 아래 작은 점을 잡고 C10까지 끌어내리면 됩니다.

=VLOOKUP(B5,$H$5:$I$8,2,FALSE)

이 수식을 말로 풀면 이렇습니다. B5에 있는 거래처코드를 H5:I8 거래처표에서 찾고, 그 범위의 두 번째 열에 있는 거래처명을 가져오라는 뜻입니다.

여기서 초보자가 가장 많이 헷갈리는 부분은 2와 FALSE입니다. 숫자 2는 엑셀 열 번호 B열, C열을 뜻하는 것이 아니라, 내가 잡은 범위 H5:I8 안에서 두 번째 열이라는 뜻입니다. H열이 1번째, I열이 2번째입니다.

마지막의 FALSE는 정확히 같은 코드만 찾겠다는 의미입니다. 거래처코드, 상품코드, 사번처럼 딱 맞는 값을 찾아야 할 때는 거의 항상 FALSE를 넣는다고 생각하면 됩니다.

Q. #N/A는 무슨 뜻인가요? 수식이 고장 난 건가요?

#N/A는 수식 자체가 망가졌다는 뜻이 아닙니다. 엑셀이 찾으라는 값을 기준표에서 못 찾았다는 뜻에 가깝습니다.

위 예시에서 B8에 C005가 입력되어 있는데 거래처표 H5:H8에는 C005가 없습니다. 그러면 C8에는 #N/A가 표시됩니다. 이 경우는 수식을 고칠 문제가 아니라, 거래처표에 C005를 추가해야 합니다.

실무에서는 신규 거래처가 먼저 주문내역에 들어오고, 거래처표 업데이트가 늦어지는 경우가 많습니다. 그래서 #N/A가 보이면 먼저 이렇게 확인해 보세요.

Q. 수식을 아래로 복사했더니 갑자기 안 됩니다. 왜 그런가요?

이 문제는 거의 대부분 거래처표 범위를 고정하지 않아서 생깁니다. 예를 들어 C5에 아래처럼 입력했다고 해보겠습니다.

=VLOOKUP(B5,H5:I8,2,FALSE)

처음 C5에서는 잘 나올 수 있습니다. 하지만 C6으로 복사하면 엑셀이 자동으로 범위를 H6:I9처럼 한 줄 아래로 밀어버립니다. C7에서는 H7:I10처럼 또 밀립니다.

거래처표는 항상 H5:I8을 봐야 하므로, 범위를 움직이지 않게 고정해야 합니다. 그때 쓰는 표시가 달러 기호입니다.

=VLOOKUP(B5,$H$5:$I$8,2,FALSE)

직접 달러 기호를 입력해도 되고, 수식에서 H5:I8 부분을 클릭한 뒤 키보드의 F4 키를 눌러도 됩니다. 노트북에서는 Fn 키와 함께 눌러야 하는 경우도 있습니다.

Q. 거래처코드가 비어 있을 때 #N/A가 안 보이게 할 수 있나요?

주문내역을 미리 100행까지 만들어 두면, 아직 거래처코드를 입력하지 않은 빈 행에도 #N/A가 줄줄이 보일 수 있습니다. 이때는 IF와 IFERROR를 같이 쓰면 보기 편합니다.

C5에 아래 수식을 넣고 아래로 복사합니다.

=IF(B5="","",IFERROR(VLOOKUP(B5,$H$5:$I$8,2,FALSE),"코드 확인"))

이 수식은 세 부분으로 나누어 보면 쉽습니다. 먼저 B5가 비어 있으면 C5도 빈칸으로 둡니다. B5에 코드가 있으면 VLOOKUP으로 거래처명을 찾습니다. 그래도 못 찾으면 #N/A 대신 코드 확인이라고 표시합니다.

초보자에게는 이 수식이 조금 길어 보일 수 있지만, 실무에서는 훨씬 보기 좋습니다. 특히 다른 사람이 파일을 열었을 때 #N/A보다 코드 확인이라는 문구가 훨씬 친절합니다.

Q. 분명히 C001이 있는데도 #N/A가 뜨는 이유는 뭔가요?

겉으로 볼 때 C001과 C001이 같아 보여도 실제 값이 다를 수 있습니다. 가장 흔한 원인은 앞뒤 공백입니다. 예를 들어 한쪽은 C001이고, 다른 한쪽은 C001 뒤에 공백이 하나 붙어 있으면 엑셀은 서로 다른 값으로 봅니다.

간단히 확인하려면 빈 셀에 아래처럼 입력해 볼 수 있습니다. 예를 들어 주문내역의 B5와 거래처표의 H5가 정말 같은지 보는 방식입니다.

=B5=H5

결과가 TRUE면 같은 값이고, FALSE면 다르다는 뜻입니다. 눈으로는 같아 보여도 FALSE가 나온다면 공백, 숨은 문자, 숫자와 문자 형식 차이를 의심해야 합니다.

거래처코드를 새로 입력하기 전이라면 먼저 B열과 H열을 선택한 뒤 홈 탭 > 표시 형식 > 텍스트로 맞춰 두는 것이 좋습니다. 특히 거래처코드가 001, 002처럼 앞자리 0이 있는 형식이라면 숫자가 아니라 텍스트로 관리하는 편이 안전합니다.

Q. 거래처표에서 코드가 오른쪽에 있으면 VLOOKUP이 안 되나요?

VLOOKUP은 기본적으로 범위의 첫 번째 열에서 값을 찾고, 오른쪽 열의 값을 가져오는 함수입니다. 그래서 거래처표가 H열 거래처명, I열 거래처코드 순서로 되어 있으면 VLOOKUP으로 거래처명을 가져오기가 불편합니다.

초보자에게 가장 쉬운 해결은 거래처표 열 순서를 바꾸는 것입니다. H열에 거래처코드, I열에 거래처명을 두면 앞에서 배운 수식을 그대로 쓸 수 있습니다.

만약 열 순서를 바꾸기 어렵고, 사용 중인 엑셀에서 XLOOKUP 함수가 된다면 아래처럼 쓸 수도 있습니다. C5에 입력하는 수식입니다.

=XLOOKUP(B5,$I$5:$I$8,$H$5:$H$8,"코드 확인")

이 수식은 B5의 코드를 I5:I8에서 찾고, 같은 행의 H5:H8 값을 가져옵니다. 다만 회사 PC마다 엑셀 버전이 다를 수 있으니, 여러 사람이 같이 쓰는 파일이라면 VLOOKUP 방식이 더 무난할 때가 많습니다.

Q. 결과가 이상할 때 확인 순서는 어떻게 잡으면 좋나요?

무작정 수식을 다시 쓰기보다 순서를 정해 확인하면 훨씬 빠릅니다. 먼저 C5 하나만 제대로 맞춘 뒤, 그다음 아래로 복사하는 방식이 좋습니다.

첫째, 찾을 값 셀을 확인합니다. 이 예제에서는 B5입니다. B5가 비어 있거나 잘못된 코드면 어떤 수식을 써도 결과가 맞을 수 없습니다.

둘째, 기준표 범위를 확인합니다. 이 예제에서는 $H$5:$I$8입니다. 제목 행 H4:I4를 포함해도 큰 문제는 없는 경우가 많지만, 초보자라면 실제 데이터 범위만 잡는 습관을 들이는 것이 좋습니다.

셋째, 가져올 열 번호를 확인합니다. H5:I8 범위에서 거래처명은 두 번째 열이므로 2입니다. 만약 H5:J8처럼 범위가 넓어지고 J열 값을 가져와야 한다면 열 번호는 3이 됩니다.

넷째, 마지막 인수 FALSE를 확인합니다. TRUE로 되어 있거나 생략되어 있으면 비슷한 값, 가까운 값처럼 엉뚱한 결과가 나올 수 있습니다. 코드 찾기에서는 FALSE를 넣는다고 기억해 두세요.

이럴 때 이렇게 쓰면 된다

거래처코드를 입력하면 거래처명을 자동으로 채우고 싶다면, 먼저 기준표를 코드가 왼쪽, 이름이 오른쪽인 구조로 정리하세요. 그리고 결과 셀 C5에 VLOOKUP 수식을 넣고, 기준표 범위는 반드시 $로 고정합니다.

빈 행에 오류가 보이는 것이 싫다면 IF와 IFERROR를 함께 쓰면 됩니다. 기준표에 없는 코드는 #N/A 대신 코드 확인으로 표시해 두면, 나중에 신규 거래처를 추가해야 할 행만 빠르게 찾을 수 있습니다.

정리하면 단순 조회는 VLOOKUP + FALSE + 범위 고정으로 시작하면 충분합니다. 코드가 있는데도 안 나오면 수식보다 먼저 코드표 누락, 공백, 숫자와 문자 형식 차이를 확인하면 됩니다.