vlookup 함수 오류 원인 F4 고정과 인수 체크리스트
수식 한 줄 잘 만들어 놓고 아래로 쭉 채웠는데, 중간쯤부터 #N/A가 줄줄이 뜨거나 엉뚱한 단가가 끼어 있던 적 있으세요?
어디부터 손을 대야 할지 막막할 때는 수식을 처음부터 다시 쓰기보다 인수 두 군데만 먼저 확인해 보면 돼요.
VLOOKUP에서 자주 생기는 오류는 인수 네 개 중 딱 두 자리에서 나오거든요.
두 번째 인수인 원본 범위에 $ 표시로 고정을 안 걸었거나, 네 번째 인수를 비워 둔 경우가 대부분이에요.
원본 범위를 드래그한 직후 F4 키로 고정하고 마지막 자리에 FALSE를 넣으면 수식을 복사할 때 생기는 범위 밀림과 엉뚱한 값 오류를 한 번에 막을 수 있어요.
오류 코드가 뜨는 것보다 더 곤란한 상황은 에러 표시조차 없이 엉뚱한 값이 들어가는 경우예요.
마지막 인수를 TRUE로 두거나 아예 생략하면 엑셀이 똑같은 값 대신 비슷한 근삿값을 가져올 수 있어서, 화면엔 멀쩡한 숫자가 찍히는데 실제로는 다른 행의 정보일 때가 있거든요.
그래서 에러 없이 이상한 숫자가 들어와 있다면 내가 넣은 수식의 마지막 자리가 FALSE(또는 0)로 되어 있는지부터 열어 보는 게 가장 빨라요.
마지막 인수는 왜 꼭 FALSE여야 할까?
네 번째 인수는 엑셀에게 값을 얼마나 정확하게 찾을지 알려 주는 자리예요.
여기에 FALSE나 숫자 0을 넣으면 내가 지정한 값과 토씨 하나 안 틀리고 똑같은 항목만 찾아 주고, 반대로 TRUE를 넣거나 자리를 비워 두면 가장 비슷한 근삿값을 대신 돌려주거든요.
물론 실무에서 근삿값이 일부러 필요한 순간도 있긴 해요.
예를 들어 점수 구간표에서 85점의 등급을 찾을 때 TRUE를 쓰면 80점 이상 구간에 해당하는 B 등급을 알아서 찾아 주는 식이죠.
하지만 상품 코드, 사번, 주문번호처럼 단 하나의 정확한 값을 찾아야 할 때는 마지막 인수에 무조건 FALSE를 넣어 두세요.
네 자리는 어떤 순서로 채울까?
기본 형태는 =VLOOKUP(찾을 값, 원본 표, 열 번호, FALSE) 순서로 채워 넣으면 돼요.
이때 첫 번째 인수인 찾을 값은 반드시 원본 표의 맨 왼쪽 첫 열에 있어야 하고, 세 번째 열 번호는 시트 전체의 알파벳 열이 아니라 내가 드래그한 범위 안에서 왼쪽부터 몇 번째인지를 숫자로 적어야 해요.
만약 B2:E10을 원본 범위로 잡았다면 B열이 1번, C열이 2번이 되는 식이죠.
여기서 결과가 갈리는 핵심은 F4 키를 누르는 타이밍이에요.
두 번째 인수에서 원본 범위를 마우스로 드래그한 바로 그 순간에 F4를 눌러야 다음 인수로 넘어가기 전에 행과 열 고정이 깔끔하게 끝나거든요.
반대로 첫 번째 인수인 찾을 값은 아래로 수식을 복사할 때 한 줄씩 자연스럽게 따라 내려가야 하니까 $ 없이 그대로 두는 게 맞아요.
수백 줄짜리 긴 목록을 작업할 때는 첫 행의 수식 결과부터 눈으로 확인하고 나머지 행에 복사하는 습관을 들이는 게 좋아요.
한 번에 전체 행에 채워 넣는 것보다 어디서 꼬였는지 실수를 찾기가 훨씬 쉽거든요(이거 은근 쓸모 있어요).
화면 옆에 엑셀을 띄워 두고 아래 순서대로 차근차근 입력해 보세요.
- 결과를 넣을 칸 선택 → 수식 → 찾기 및 참조 영역 → VLOOKUP
- Lookup_value: 찾을 코드가 있는 셀 클릭(E2처럼 $ 없이 그대로 두기)
- Table_array: 원본 표 범위 드래그 → 바로 F4 키 입력 → $A$2:$C$100처럼 바뀌었는지 확인
- Col_index_num: 잡은 범위 안에서 가져올 값이 있는 열의 순번을 숫자로 입력
- Range_lookup: FALSE 또는 0 입력 → 확인
- 첫 행 결과가 맞는지 확인한 뒤 홈 → 채우기 → 아래쪽으로 복사
아래로 채웠더니 몇 줄만 #N/A가 뜬다면?
$ 기호 없이 A2:C4처럼 적은 범위는 상대 참조 상태라서, 수식을 아래로 한 줄 내릴 때마다 원본 범위도 덩달아 한 줄씩 아래로 밀려 내려가요.
그러다 내가 찾으려는 값이 들어 있던 위쪽 행이 밀려난 범위 밖으로 빠져나가는 순간 그 줄에서만 #N/A 오류가 뜨게 돼요.
이해를 돕기 위해 A2:C4 영역에 상품 코드 P001, P002, P003과 상품명, 단가가 차례대로 들어 있고, D2~D4 칸에서 순서를 뒤집어 P003, P002, P001의 단가를 찾는다고 가정해 볼게요.
E2 칸에 =VLOOKUP(D2,A2:C4,3,FALSE)를 적고 E4까지 아래로 끌어서 채우면, 두 번째 줄인 E3은 원본 범위를 A3:C5로 잡고, 세 번째 줄인 E4는 A4:C6을 쳐다보게 돼요.
정작 찾아야 하는 P001은 2행(A2)에 들어 있는데, E4의 수식은 4행부터 아래쪽만 뒤지고 있으니 값을 못 찾고 에러를 뱉는 거죠.
이럴 때 원본 범위를 $A$2:$C$4로 단단히 고정해 두면 세 줄 모두 똑같은 원본 표를 바라보고, 찾을 값만 D2 → D3 → D4 순서로 한 칸씩 내려가며 정확히 찾아내요.
수식 한 칸을 더블클릭했을 때 원본 표를 감싼 파란색이나 빨간색 테두리가 첫 줄보다 아래로 내려가 있다면 바로 이 고정 문제라고 보면 돼요.
일부 행만 틀리면 $부터 의심해 보세요
그래도 오류가 남으면 어디부터 볼까?
$ 고정과 FALSE 입력까지 다 챙겼는데도 오류가 사라지지 않는다면 화면에 뜬 오류 문구별로 원인을 다르게 짚어 봐야 해요.
#N/A가 뜬다면 찾을 값이 원본 표 첫 열에 아예 없는 경우가 흔한데, 우리 눈에는 똑같은 코드로 보여도 한쪽은 숫자 형식이고 다른 쪽은 텍스트 형식이거나 코드 앞뒤에 눈에 안 보이는 공백이 한 칸 숨어 있으면 엑셀은 다른 값으로 판단해요.
이럴 땐 양쪽 셀의 표시 형식을 같게 맞추거나 스페이스바 공백을 지워 주면 바로 풀려요.
#REF! 오류는 세 번째 인수에 적은 열 번호가 내가 마우스로 잡은 원본 범위의 전체 열 개수보다 클 때 주로 나타나요.
예를 들어 가로로 세 칸짜리(A열~C열) 범위를 잡아 놓고선 4번째 열의 값을 가져오라고 숫자를 잘못 적은 경우죠.
IFERROR 같은 함수로 오류 표시를 감추기 전에 원본 데이터의 서식과 참조 범위 구조부터 바로잡는 게 진짜 해결 순서예요.
- ✔#N/A → 찾을 값이 범위 맨 왼쪽 열에 있는지
- ✔#N/A → 숫자·문자 표시 형식이 같은지, 앞뒤 공백이 없는지
- ✔#REF! → 열 번호가 범위의 열 개수를 넘지 않는지
- ✔#VALUE! → 인수 형식과 입력한 숫자가 올바른지
- ✔오류 없이 값이 이상함 → 마지막 인수가 FALSE인지
- ✔일부 행만 결과가 다름 → 두 번째 인수에 $가 붙어 있는지
원본 범위를 잡자마자 F4로 고정하고 마지막 칸에 FALSE를 넣는 습관만 들여도, 수식을 복사하다 생기는 범위 밀림과 엉뚱한 값 오류는 대부분 걸러낼 수 있어요.
다만 점수 구간으로 등급을 매기는 표처럼 근삿값을 일부러 찾아야 하는 상황이라면 그때만 마지막 인수를 TRUE로 바꿔 쓰면 돼요.
지금 열려 있는 엑셀 시트에서 오류가 난 셀을 더블클릭해 보고, 여러분의 수식 두 번째 자리에 $ 기호가 잘 들어가 있는지 바로 확인해 보세요.
이 글과 함께 보면 좋은 상품
VLOOKUP 범위 고정과 인수 점검을 넘어 엑셀 실무 전반을 더 익히고 싶은 직장인·학생이 참고할 수 있는 선택지입니다.
댓글
댓글 쓰기