Vlookup함수 사용시 나타나는 #N/A 오류
예전에 근무하던 회사 ERP 에서 Raw data 원본 데이터들을 받아서 vlookup함수로 작업을 할 때 종종 #N/A 오류가 나오곤 했었습니다. 아직 엑셀 초보티를 못 벗어났었고, 엑셀에 대한 신뢰도 아직 없었을 때라… 그 때는 해결 방법을 찾기는 커녕 당황해서 셀 하나 하나 수동으로 세고 그랬었죠 (참 어린 시절이었습니다)
초보자 입장에서 기껏 함수 사용해서 복잡한 수식을 만들었는데 아래와 같이 #N/A 오류가 뜨면 좌절할 수 밖에 없죠. 물론 엑셀 함수의 오류코드도 많고 그 원인도 다양하지만, 오늘 공유 드리는 내용 한 번 보시고 vlookup함수 오류 해결에 조금이나마 도움이 되길 바랍니다.
위에 스크린 샷 또는 아래에 첨부된 파일을 보시면 C5, C11 두 셀에 입력된 수식 모두 같은 사과의 가격을 동일한 테이블에서 가지고 오고, 수식 자체에도 아무런 문제가 없는 것 같은데… #N/A 오류값을 반환해버리고 맙니다. 오히려 수식 자체가 여러 함수를 사용하고 복잡하기라도 하면, “에고, 내가 괜히 복잡한 수식을 사용했구나… 좀 더 공부해볼 걸” 하면서 저의 부족함을 탓할 텐데… 이렇게 vlookup함수 하나만 사용한 상대적으로 간단한 수식에서 아무 이유 없이 오류값이 나니… 인터넷을 뒤져서 여러 관련 정보를 찾아봐도 소득이 없으니 답답할 노릇이었죠… 결국 원본 데이터 (참조하는 값) 과 함수에서 lookup value 인수에 해당하는 값이 아주 아주 미세하게 달라서 그랬을 뿐인데… 나중에 알게 되니 허탈할 뿐이었습니다.
오류의 원인
오류의 원인은 위에서 언급한 것 처럼 원본 데이터와 함수에서 lookup value 인수로 쓰인 값이 동일하지 않아서 발생한 것입니다. 예를 들어, 첨부 파일 B11셀에 입력된 ‘사과’ – vlookup함수에서 lookup value 인수에 해당하는 값 – 의 경우 ‘사과’라는 단어 뒤에 스페이스바 (뛰어쓰기)가 한 칸 더 들어가 있지만, 과일별 Unit Price 를 가지고 오는 원본 데이터 N4:Q10 테이블에 있는 ‘사과’ 는 텍스트 앞 뒤로 스페이스바 (뛰어쓰기)가 들어가 있지 않습니다. 이러한 미세한 차이 때문에 참조하는 원본 데이터 테이블에서 사과의 Unit Price 를 가지고 오지 못하고 #N/A 오류값만 반환하고 있었던 것입니다..
예제 파일에 들어있는 데이터는 아주 단순한 편이라, 셀 들을 하나 하나 수동으로 까보면, 운좋게 이러한 오류의 원인을 파악할 수도 있습니다. 하지만, 우리가 업무에서 마주하는 데이터는 아무래도 훨씬 더 사이즈가 크고 복잡할 텐데요. 그러한 방대한 데이터 안에서, 이러한 원본 (참조) 데이터 – lookup value 인수로 쓰이는 값의 미세한 차이를 한 눈에 파악하는 것은 정말 어려운 일일 겁니다. 때문에, 이러한 오류를 보다 손쉽게 해결하기 위해 우리는 Trim 함수를 사용해 보는 것을 추천드립니다.
Trim 함수로 vlookup함수 오류 제거
엑셀 Trim 함수는 엑셀에서도 상대적으로 사용하기 쉬운 함수에 속합니다. Trim 함수는 아래 공식 마이크로소프트 고객지원 사이트에 설명되어 있는 것 처럼 단어 사이에 있는 공백을 제외한 텍스트의 모든 공백을 제거해주는 함수입니다. 보다 자세한 함수 구문 설명과 예시가 필요하신 분들은 아래 마이크로소프트 고객지원 사이트를 참고해보셔도 좋습니다. (사실 그 정도로 어려운 함수는 아니라고 생각하지만…)
일단 위에서 다룬 #N/A 오류를 해결하기 위해서는, 제가 아래에 드리는 예제 파일 두 번째 탭에 나온 것처럼, Trim 함수를 활용해 단어 앞 뒤에 있을지 모를 스페이스바를 제거하고 원본 데이터 기준열에 값 복사하기로 붙여 넣으면 됩니다. 가지고 계신 Raw Data 원본 데이터가 아무리 방대해도 엑셀의 필터 기능과 리본 메뉴에 “값 붙여 놓기” 를 단축키로 설정해 놓았다면 약 30초만에 오류 수정이 가능해집니다. 한 번 예제파일을 다운로드 받고 아래의 스크린샷에 나온대로 천천히 연습해보시는 것을 추천드립니다.
예제 파일
vlookup함수 사용할 때 종종 마주치는 #N/A 오류를 Trim 함수로 해결하는 방법을 담은 예제 파일을 공유 드립니다. 다운 받으시고 시간 되실 때 위에 설명해드린 내용과 함께 천천히 연습해 보세요. 위에 언급한대로, 예제 파일에 있는 데이터는 상대적으로 작은 편입니다. 물론 업무에서 마주치는 원본 데이터의 크기와 복잡성은 훨씬 다양하겠지만, 오류를 해결하는 원리는 같으니, 예제 파일에서 충분히 익숙하게 사용하실 수 있으면, 실무에서 마주하는 오류도 쉽고 빠르게 해결할 수 있을 겁니다.
그리고 오늘 본 vlookup 함수 뿐 아니라 다른 함수를 사용할 때도 – 특히 offset, sumif, countif, indirect 함수와 같이 원본 데이터를 참조하는 – 함수들에서 유사한 오류들이 나올 때가 있습니다. 이러한 경우에도 우선 Trim 함수를 사용해 원본 데이터와 인수로 사용한 값 들이 정확하게 매칭하는지 먼저 보시는 게 오류를 파악하고 수정하는 데 큰 도움이 될 것입니다.
그럼 오늘도 업무 보시는 와중에 엑셀 공부하느라 고생하셨습니다. 다들 빨리 업무 마무리 하시고 저녁이 있는 삶을 보내시길 바랍니다. 화이팅!!!
엑셀 사용하면서 다른 쉽고 유용한 엑셀 팁들을 보고 싶으다면 아래 링크 클릭해주세요!
https://keepthefaith.co.kr/category/smart-workplace/excel/