엑셀 VLOOKUP 참조셀 오류 #N/A 해결 방법 | 와일드카드의 존재 | 엑셀미마생

엑셀 함수 중 VLOOKUP이라는 찾기 함수가 있습니다. 무려 인수가 4개나 들어가는 고급함수라고 할 수 있는데, 고윳값(key)이 존재하는 경우 찾기 기능 중에서 가장 수완이 좋아 개인적으로는 애용하는 편입니다.

 

 

 

문제는 가끔 이 함수가 내는 참조셀 오류(#N/A)가 골치 아픈데, 오늘은 이에 대한 해결 방법에 관한 아이디어를 공유하고자 합니다.

 

 

엑셀 VLOOKUP 함수 기본 개념

엑셀 VLOOKUP 함수는 기본적으로 아래의 4개의 인수를 사용합니다.

 

1. 찾고자 하는 값(텍스트); 고유값
2. 찾고자 하는 값이 포함된 범위표
3. 찾고자 하는 하는 값(참조값)으로 매칭되는 짝 열(몇 번째 열?)
4. 0 : 정확히 일치

위의 4개 인수에 대한 설명은 제가 이해하는 대로 썼습니다. 공식 설명이 아니니 참고만 하시기 바랍니다. 우선 이 글을 읽으신다는 것은 VLOOKUP 함수의 기본 개념 따위나 알겠다고 보고 계신 게 아닐 테니, 불필요한 코멘트는 자제하도록 하겠습니다.

 

 

그럼에도 불구하고, 굳이 위의 정보를 나열한 것은 구동원리를 알아야만 오류가 지칭하는 내용을 정확히 알 수 있기 때문입니다.

 

 

그중에서도 가장 중요한 것이 바로 오늘의 주제와 맞닿아있는 1번 '찾고자 하는 값(텍스트)' 부분입니다.

 

 

 

 

 

고윳값 여부

가장 기본적인 내용이지만, VLOOKUP 함수는 고윳값을 참조셀로 하여 매칭되는 내용을 끌어오는 경우에 사용합니다. 따라서 고유값을 참조셀로 하지 않는 경우, 쉽게 말해 ID가 중복되는 경우 제 기능을 할 수 없습니다.

 

 

 

여담으로 제 전 직장에서 다루는 빅데이터가 고윳값이 없는 엑셀 파일이었는데, 참조를 걸어 해당 내용을 끌어와야 하는 경우가 있었습니다.

 

 

 

이런 경우 몇 개의 열을 &로 엮어 하나의 새로운 키(key) 값을 만들어준다면 자료의 방대함에 따라 다르겠지만, 몇 만개 수준 정도선에서는 어느 정도 고윳값 역할을 할 수 있는 키가 됩니다.

 

 

 

 

텍스트 서식 여부

 

[퇴사 준비] 후임을 위해 정리하는 엑셀 1차 밑자료 취합 가공 시 주의 사항

한편, 한 가지 핵심적인 원리 중 하나로 해당 함수의 구동은 참조셀이 '텍스트'여야 한다는 사실입니다. 따라서 숫자나 날짜 등의 데이터 서식으로 지정된 참조 열이라면 함수가 에러를 낼 가능성이 있습니다.

 

 

 

따라서 이런 경우에는 참조셀이나 열을 '텍스트' 형식으로 변경해 주는 처리가 필요합니다.

 

 

 

가장 확실한 방법은 데이터 탭의 텍스트 바꾸기를 활용하는 방법인데, 가공 과정에서 줄 바꿈 등의 데이터가 유실되는 이슈가 있으므로 정확한 가공 처리 방법이 필요한 경우 아래 글을 참고해 보시기 바랍니다.

 

2024.02.29 - [엑셀] - 엑셀 줄바꿈 셀 밑자료 rawdata 데이터 가공 취합 시 주의사항 | 엑셀미마생

엑셀 줄바꿈 셀 밑자료 rawdata 데이터 가공 취합 시 주의사항 | 엑셀미마생

 

 

 

참조셀 오류 #N/A 의미

VLOOKUP 함수에서 #N/A 오류는 MS 엑셀 도움말에서도 직관적으로 참조셀을 찾지 못한 오류라고 말하고 있습니다. 

 

중복값도 없고, 텍스트 지정까지 완료하였음에도 해당 오류가 반복되는 경우에는 쉽게 떠올리긴 어렵지만, 아래의 한 가지 가능성이 있습니다.

 

 

 

와일드카드(물결표시 또는 별표시) 존재 여부

엑셀에는 '와일드카드'라는 것이 존재합니다. 물결표시(~) 또는 별표시(*) 등인데, 쉽게 말해 해당 모양들의 기호는 그 자체로 다른 기능을 담고 있습니다.

 

 

예를 들어 *모양 기호는 '그 기호가 위치한 자리에 어떤 길이의 텍스트든 대체되어 들어갈 수 있다'는 의미를 그 자체로 담고 있기 때문에 VLOOKUP과 같은 일부 함수의 경우 해당 기호 자체를 텍스트로 인식하지 못합니다.

분명 있는데, 찾지를 못 하는 와일드카드

 

 

바로 이 '텍스트로 와일드카드를 인식하지 못한다.'는 부분이 에러(오류)의 핵심입니다.

 

 

그렇다면 이 부분을 어떻게 해결해 줄 수 있을까요? 

 

찾기 바꾸기(Ctrl + H) 통한 해결 방법

해당 와일드카드를 바꾸어줌으로써 해결이 가능합니다. 해결 방법은 간단하지만, 와일드카드 자체를 써서 검색하면 검색이 안 된다는 신박한 점으로 수많은 직장인들이 2차 화남의 순간을 맞이합니다.

 

와일드카드를 찾고자 할 때에는 앞에 물결표시 하나를 붙여주어야 합니다.(마치 특정 셀에 숫자를 입력할 때, 가장 앞부분의 0이 사라지지 않게 하기 위해 어퍼스트로피(')를 붙이는 것과 비슷한 방식이라고 생각하시면 좋습니다.)

 

*을 찾고자 한다면 ~*과 같이 입력해야 찾아집니다.
마찬가지로 ~표시를 찾고자 한다면 ~~ 처럼 찾아야 찾아집니다.

 

 

이런 방식으로 와일드카드를 찾아, 모두 바꾸기 기능을 통해 와일드카드가 아닌 다른 기호로 바꾸어준다면 정상적으로 텍스트 인식이 가능하며, 함수 오류가 사라질 수 있습니다.

 

 

 

해결 방법은 의외로 간단한 곳에 있지만, 대부분의 직장인들은 평소에 사용하지 않는 '와일드카드' 개념을 떠올리기 쉽지 않습니다.

 

 

따라서 몇 시간 동안 머리만 쥐어뜯게 되는데, 이 글이 부디 많은 직장인들의 고민에 도움이 되기를 바랍니다.