VLOOKUP이 안 될 때 확인할 5가지 (순서대로)

VLOOKUP은 배우기는 쉬운데 실무에서 자주 어긋납니다. 어제까지 잘 되던 수식이 오늘 #N/A를 뱉기도 하고, 아래로 채우니 값이 밀리기도 합니다.

원인은 거의 정해져 있습니다. 아래 순서대로 확인하면 대부분 처음 두 가지에서 해결됩니다.

확인 1. 네 번째 인수를 넣었는가

가장 흔한 원인이자 가장 위험한 원인입니다.

=VLOOKUP(A2, 범위, 2, 0)

마지막 0(또는 FALSE)은 정확히 일치하는 값만 찾으라는 뜻입니다. 이걸 생략하면 엑셀은 TRUE로 간주하고 근사값을 찾습니다.

근사값 모드는 데이터가 오름차순으로 정렬돼 있다고 가정합니다. 정렬돼 있지 않으면 #N/A가 나오거나, 더 나쁘게는 엉뚱한 값을 오류 없이 가져옵니다. 오류가 안 나기 때문에 발견이 늦어지고, 그대로 보고에 들어갑니다.

정확히 일치를 원한다면 마지막 인수는 항상 0으로 쓰세요. 예외는 구간별 등급표처럼 근사 조회가 목적일 때뿐입니다.

확인 2. 찾는 값의 형식이 같은가

한쪽은 숫자 1001, 다른 쪽은 텍스트 "1001"인 경우입니다. 화면에는 똑같이 보이지만 VLOOKUP은 다른 값으로 봅니다.

확인은 =ISNUMBER(A2)로 양쪽을 비교하면 됩니다. 다르다면 한쪽을 맞춰야 합니다.

공백도 같은 문제를 일으킵니다. 대성물산과 대성물산 (뒤에 공백)은 다른 값입니다.

=VLOOKUP(TRIM(A2), 범위, 2, 0)

다만 이건 찾는 쪽만 정제합니다. 조회 대상 표에 공백이 있다면 그쪽도 정제한 열을 따로 만들어야 합니다.

확인 3. 범위를 절대참조로 고정했는가

수식을 아래로 채웠더니 위쪽은 맞고 아래로 갈수록 #N/A가 늘어난다면 이 경우입니다.

=VLOOKUP(A2, $B$2:$D$500, 2, 0)

범위에 $가 없으면 아래로 채울 때 조회 범위도 함께 내려갑니다. 500행짜리 표가 점점 잘려나가는 셈입니다.

범위를 표(Ctrl + T)로 만들면 이 문제가 아예 생기지 않습니다. 표 이름으로 참조하면 절대참조가 필요 없고 데이터가 늘어나도 자동으로 따라옵니다.

확인 4. 찾을 값이 첫 열에 있는가

VLOOKUP은 지정한 범위의 첫 번째 열에서만 값을 찾습니다. 코드로 이름을 찾을 수는 있어도, 이름으로 코드를 찾을 수는 없습니다.

이 경우 INDEX와 MATCH를 씁니다.

=INDEX($B$2:$B$500, MATCH(A2, $D$2:$D$500, 0))

읽는 법은 간단합니다. MATCH가 "D열에서 A2가 몇 번째에 있는지" 세고, INDEX가 "B열에서 그 번째 값을 가져오라"고 지시합니다. 왼쪽이든 오른쪽이든 상관없습니다.

확인 5. 열을 추가하거나 삭제하지 않았는가

세 번째 인수는 열 번호입니다. 표 중간에 열을 하나 끼워 넣으면 이 번호가 어긋나면서, 오류 없이 옆 열의 값을 가져옵니다.

표 구조가 자주 바뀐다면 처음부터 INDEX/MATCH를 쓰는 편이 안전합니다. 열 위치가 아니라 열 자체를 참조하기 때문에 열이 이동해도 따라갑니다.

Microsoft 365를 쓴다면 XLOOKUP이 더 간단합니다.

=XLOOKUP(A2, $D$2:$D$500, $B$2:$B$500, "없음")

다만 Excel 2021 이하에서는 #NAME?이 뜨므로, 파일을 공유한다면 상대방 버전을 확인해야 합니다.

자주 막히는 지점 세 가지

① 오류를 숨기고 싶을 때

보고서에 #N/A가 그대로 보이면 곤란합니다.

=IFERROR(VLOOKUP(A2,$B$2:$D$500,2,0), "")

단, 검증이 끝난 뒤에 씌우세요. 처음부터 IFERROR로 감싸면 진짜 문제가 있는 건까지 조용히 빈칸이 되어버립니다. 값이 없는 건지 수식이 틀린 건지 구분할 수 없게 됩니다.

② 중복된 키가 있을 때

VLOOKUP은 첫 번째로 만나는 값만 가져옵니다. 같은 코드가 여러 번 있으면 나머지는 무시됩니다.

금액 합계가 목적이라면 SUMIF가 맞습니다. 중복 자체가 문제라면 먼저 중복부터 정리해야 합니다.

③ 부분 일치로 찾고 싶을 때

"대성"으로 "대성물산"을 찾으려면 와일드카드를 씁니다.

=VLOOKUP("*"&A2&"*", $B$2:$D$500, 2, 0)

여러 건이 걸릴 수 있으므로 결과를 반드시 확인하세요.

자주 묻는 질문

Q. VLOOKUP과 INDEX/MATCH 중 뭘 써야 하나요?

일회성 작업이면 VLOOKUP이 빠릅니다. 계속 유지·공유할 파일이면 INDEX/MATCH가 낫습니다. 열 추가·삭제에 영향받지 않고, 왼쪽 조회도 되며, 대용량에서 조금 더 빠릅니다.

Q. 다른 파일의 표를 참조해도 되나요?

됩니다. 다만 그 파일을 닫으면 수식이 전체 경로로 바뀌고, 파일 위치가 변하면 #REF!가 됩니다. 가능하면 데이터를 같은 통합문서로 가져와서 쓰세요.

Q. 계산이 너무 느립니다.

조회 범위를 B:D처럼 열 전체로 지정하면 100만 행을 매번 훑습니다. $B$2:$D$500처럼 실제 범위만 지정하거나 표로 만들어 쓰세요.

Q. 구글 시트에서도 되나요?

VLOOKUP, INDEX, MATCH, IFERROR 모두 동일합니다. XLOOKUP도 지원하며 버전 문제가 없다는 게 장점입니다.

정리

  • 마지막 인수는 항상 0
  • 양쪽 값의 형식과 공백을 맞출 것
  • 범위는 $로 고정하거나 표로 만들 것
  • 구조가 바뀌는 표라면 INDEX/MATCH
  • IFERROR는 검증이 끝난 뒤에

관련 글

  • 엑셀 숫자가 텍스트로 저장됐을 때, 원인과 해결법 4가지
  • 두 파일 대사, 눈으로 비교하지 말고 COUNTIF로 끝내기
  • 엑셀 중복 데이터, 지우기 전에 먼저 확인하는 법

댓글

댓글 쓰기