엑셀 VLOOKUP 오류 #N/A 1분 만에 해결하는 실무 함수 3가지 팁

수백 명의 이름이 적힌 명단에서 사원 번호를 일일이 찾아본 적 있으신가요? 데이터가 10개라면 눈으로 찾겠지만, 1,000개가 넘어간다면 화면만 뚫어지게 쳐다보다 퇴근 시간이 다 지나가 버릴 거예요. 직장인들이 엑셀을 켜면 가장 먼저 배우게 되는 것이 바로 이럴 때 쓰는 엑셀 VLOOKUP 함수랍니다. 하지만 처음 쓰다 보면 알 수 없는 #N/A 오류 때문에 속이 터지곤 하죠. 이 글에서는 엑셀 VLOOKUP의 진짜 쉬운 공식과 잦은 오류를 단번에 해결하는 꿀팁을 알아볼게요.

목차

1. 요리 레시피처럼 쉬운 4가지 기본 공식
2. 자동 채우기 할 때 필수, F4 절대참조의 마법
3. 맨 뒤에 붙이는 숫자 0의 중요한 비밀
4. 최신 버전을 쓴다면 XLOOKUP으로 갈아타기
5. 한눈에 정리

---

1. 요리 레시피처럼 쉬운 4가지 기본 공식

엑셀 VLOOKUP 함수는 복잡해 보이지만, 사실 서랍장에서 물건을 꺼내오는 심부름꾼과 같아요. 이 심부름꾼에게 일을 시키려면 딱 4가지 지시사항만 순서대로 넘겨주면 된답니다. 괄호 안에 들어가는 4가지 재료를 하나씩 뜯어볼게요.

첫 번째 재료는 '누구를 찾을까?'예요. 예를 들어 내가 김철수라는 사람의 부서명을 알고 싶다면, 김철수의 이름이 적힌 칸(셀)을 콕 찍어주면 돼요. 이것이 바로 찾을 기준이 되는 값이죠. 두 번째 재료는 '어느 서랍장을 뒤질까?'예요. 김철수부터 이름과 부서가 쭉 적혀 있는 전체 데이터 표 범위를 마우스로 드래그해서 알려주는 과정이랍니다.

세 번째 재료는 '그 서랍장 몇 번째 칸을 열까?'예요. 내가 지정한 전체 데이터 범위에서, 이름이 첫 번째 열이라면 부서명은 두 번째나 세 번째 열에 있겠죠. 만약 부서명이 두 번째 열에 있다면 숫자 2를 적어주면 끝이에요. 마지막 네 번째 재료는 '어떻게 찾을까?'인데, 보통 0(FALSE)을 적어 넣어요. 이 부분은 아래에서 조금 더 자세히 다루어 볼게요.

결국 '=VLOOKUP(기준값, 데이터범위, 열번호, 0)' 이 공식 하나만 외워두면 엑셀에서 길을 잃을 일이 없어요. 이전에 정리한 엑셀 중복값 찾기 1분 만에 끝내는 3가지 실무 팁 글도 참고해 보세요. 중복값을 정리한 뒤에 VLOOKUP을 걸어주면 훨씬 깔끔하게 데이터 검색을 해낼 수 있답니다.

---

반응형

2. 자동 채우기 할 때 필수, F4 절대참조의 마법

엑셀 VLOOKUP을 배운 뒤 첫 번째 칸에 수식을 예쁘게 넣고, 아래로 쭉 드래그해서 자동 채우기를 하는 순간을 떠올려보세요. 첫 줄은 잘 나왔는데, 중간부터 갑자기 #N/A 라는 무시무시한 에러 창이 연속해서 뜨기 시작하잖아요. 도대체 왜 이러는 걸까요?

이유는 바로 두 번째 재료였던 '데이터 범위'가 밑으로 미끄러져 내려갔기 때문이에요. 엑셀은 내가 수식을 한 칸 밑으로 내리면, 검색할 서랍장의 범위도 똑같이 한 칸 밑으로 내려버리는 착각을 하거든요. 그러니 위에 있던 김철수 데이터는 검색 범위에서 벗어나 버리고 마는 거죠.

이 불상사를 막아주는 구원자가 바로 키보드 맨 윗줄에 있는 'F4' 키예요. 두 번째 인수인 데이터 범위를 마우스로 드래그한 직후에 F4 키를 한 번 톡 눌러보세요. 알파벳과 숫자 사이에 달러 기호($)가 철컥철컥 붙는 걸 볼 수 있을 거예요. 이 달러 기호는 범위를 움직이지 못하게 강력한 테이프로 고정해 두는 역할을 해요.

이걸 전문 용어로 '절대참조'라고 부르는데, 실무에서 수백 줄의 데이터를 검색할 때 절대 빼먹으면 안 되는 필수 과정이에요. 이제 수식을 아래로 아무리 길게 드래그해도 범위가 밀리지 않으니 오류 없이 깔끔한 결과값을 얻으실 수 있을 거예요.

---

3. 맨 뒤에 붙이는 숫자 0의 중요한 비밀

기본 공식에서 마지막 네 번째 재료로 숫자 0을 넣으라고 말씀드렸죠? 이 자리에 들어가는 옵션은 엑셀에게 '대충 비슷한 걸 찾을지, 아니면 완벽하게 똑같은 걸 찾을지' 지시하는 역할을 해요. 숫자 0을 쓰거나 FALSE라고 영어로 적으면 무조건 '글씨 토씨 하나 틀리지 않고 똑같은 것만 찾아와!'라는 뜻이 된답니다.

만약 이 마지막 인수를 깜빡하고 안 적으면 어떻게 될까요? 엑셀은 기본적으로 숫자 1(TRUE)이 들어간 것으로 착각해요. 이건 '대충 비슷한 걸 찾아와'라는 뜻의 유사 일치 옵션이거든요. 90점부터 100점 사이면 A등급을 주라는 식의 점수 구간을 나눌 때는 이 유사 일치가 참 쓸모 있어요.

하지만 우리가 실무에서 이름으로 전화번호를 찾거나, 제품 코드로 가격을 찾을 때는 상황이 전혀 다르잖아요. 'A102'라는 제품 코드를 찾아야 하는데 엑셀 마음대로 'A101' 가격을 비슷하다고 가져와 버리면 대형 사고가 터지는 거죠.

그래서 직원 명부나 상품 재고 표처럼 정확한 값을 매칭해야 하는 일반적인 상황이라면, 마지막에 쉼표(,)를 찍고 0이나 FALSE를 무의식적으로 적어주는 습관을 들이셔야 해요. 이것만 지켜도 엉뚱한 데이터를 불러오는 실수를 완벽하게 차단할 수 있어요.

---

728x90

4. 최신 버전을 쓴다면 XLOOKUP으로 갈아타기

엑셀 VLOOKUP은 정말 훌륭한 함수지만, 오랫동안 사람들을 괴롭힌 치명적인 단점이 하나 있어요. 바로 무조건 '왼쪽에서 오른쪽'으로만 검색할 수 있다는 점이에요. 내가 찾으려는 기준값이 데이터 표의 한가운데 떡하니 버티고 있다면, 그보다 왼쪽에 있는 데이터는 죽었다 깨어나도 가져올 수가 없었죠. 그래서 억지로 표의 순서를 바꾸곤 했어요.

이런 답답함을 한 방에 해결해 준 최신 함수가 바로 XLOOKUP이에요. 2021년 출시된 엑셀 버전이나 요즘 많이 쓰시는 Microsoft 365 버전을 사용 중이시라면 이 마법 같은 함수를 곧바로 써보실 수 있답니다.

XLOOKUP은 VLOOKUP처럼 몇 번째 열인지 숫자를 셀 필요도 없고, 데이터가 왼쪽에 있든 오른쪽에 있든 자유자재로 값을 찾아와요. 심지어 범위를 위아래로 훑기 때문에 기존의 번거로운 과정이 절반으로 줄어든답니다. 게다가 찾을 값이 없을 때 #N/A 대신 '데이터 없음'이라는 친절한 문구를 직접 띄울 수 있는 기능까지 품고 있어요.

물론 회사 컴퓨터가 아직 옛날 엑셀 버전을 쓰고 있거나, 다른 사람들과 파일을 공유해야 한다면 여전히 호환성 최고인 VLOOKUP이 대세일 수밖에 없어요. 그러니 두 가지 함수의 장단점을 모두 알아두시면 어떤 상황에서든 데이터 검색의 달인이 되실 거예요.

---

한눈에 정리

엑셀 VLOOKUP 함수는 4가지 재료(기준값, 표 범위, 열 번호, 0)만 기억하면 실무 데이터 검색을 획기적으로 줄여주는 필수 도구예요. 수식을 아래로 복사할 때 #N/A 오류를 막으려면 범위 지정 후 F4(절대참조)를 누르고, 마지막 인수 0을 넣어 정확히 일치하는 값만 불러오는 습관이 중요하답니다.

오늘 당장 엑셀을 열고 예제 표를 만들어 범위에 F4 키를 눌러 달러 기호($)를 고정해 보는 연습을 해보세요!