주문 목록에 상품코드만 있고 상품명과 단가는 다른 표에 있다면, 줄마다 눈으로 찾아 옮기다가 한 줄쯤 틀려도 알아채기 어려워요.
VLOOKUP을 쓰면 수식 한 줄을 아래로 채우는 것으로 끝나요. 이 글에서는 그 수식을 넣는 순서와 #N/A가 떴을 때 원인을 찾는 순서를 예제 파일로 따라 해 봅니다.
괄호 안에 찾을 값, 찾을 범위, 가져올 열 번호, FALSE를 차례로 넣고 범위에는 $를 붙여 고정하면 됩니다. #N/A가 뜨면 값이 정말 없는지, 공백이 붙었는지, 숫자와 글자가 섞였는지, 범위가 맞는 열에서 시작하는지를 차례로 보시면 돼요.
플릭스 업무노트는 작은 사업장에서 쓰는 엑셀·자격증·연말정산 정보를 정리하는 블로그예요. 예제도 이 블로그에서 연습용으로 만든 가상 표예요. 왼쪽 A~D열이 주문 목록이고, 오른쪽 G~I열이 상품표입니다. 화면은 영어판 Microsoft Excel이지만 한국어판도 수식은 같아요.
예제 파일을 내려받아 그대로 따라 해 보셔도 돼요. 용량은 5.6KB이고, 파일 속 상품코드·상품명·가격은 모두 연습용 가상 데이터입니다.
괄호 안 4칸에는 무엇을 넣나요
VLOOKUP 괄호 안에는 쉼표로 나눈 값 4개가 들어가요. 이 값들을 인수라고 부르고, 모양은 =VLOOKUP(찾을 값, 찾을 범위, 열 번호, [일치 방식])입니다. 첫 주문의 상품명이 들어갈 C3 셀을 기준으로 보면 이래요.
| 순서 | 인수 | 하는 일 | 예제에 넣은 값 | 이렇게 넣은 이유 |
|---|---|---|---|---|
| 1 | 찾을 값 | 무엇을 찾을지 | A3 | 주문한 상품코드 A-101 |
| 2 | 찾을 범위 | 어디서 찾을지 | $G$3:$I$6 | 상품코드가 맨 왼쪽 G열에 있는 상품표 전체 |
| 3 | 열 번호 | 범위에서 몇 번째 열을 가져올지 | 2 | G가 1, 상품명 H가 2, 단가 I가 3 |
| 4 | 일치 방식 | 똑같은 값만 찾을지 | FALSE | 비슷한 코드 말고 같은 코드만 |
영어판 도움말에서는 이 네 인수를 lookup_value, table_array, col_index_num, range_lookup이라고 불러요.
그래서 C3에는 =VLOOKUP(A3,$G$3:$I$6,2,FALSE)가 들어갑니다. 단가 셀 D3은 열 번호만 3으로 바꾼 =VLOOKUP(A3,$G$3:$I$6,3,FALSE)예요.
마지막 인수는 그냥 FALSE라고 외워 두셔도 돼요. TRUE를 넣거나 마지막 인수를 아예 빼면, 엑셀은 첫 열이 정렬돼 있다고 보고 근사값을 찾아옵니다. 근사값은 똑같은 값이 없을 때 대신 가져오는 가까운 값이에요. 상품코드나 사번은 딱 맞아야 하니까 FALSE를 써요.
C3과 D3에 수식을 넣고 아래로 채우면 나머지 주문도 한 번에 채워집니다.
아래로 채웠더니 범위가 밀린다면, $로 고정하기
수식을 아래로 채우면 A3이 A4, A5로 바뀌어요. 이건 원하던 대로죠. 그런데 범위도 같이 움직입니다. G3:I6으로만 써 두면 다음 줄에서는 G4:I7이 되고, 아래로 갈수록 상품표 윗부분을 못 보게 돼요.
그래서 범위는 $G$3:$I$6처럼 씁니다. 이렇게 쓰면 어느 줄로 복사해도 범위가 그대로예요. 이걸 절대 참조라고 해요.
$를 하나씩 치기 번거롭다면 F4 키를 쓰면 돼요. 수식이 들어 있는 셀을 선택하고, 수식 입력줄에서 G3:I6 부분을 선택한 다음 F4를 누르면 $G$3:$I$6으로 바뀝니다. 한 번 더 누를 때마다 G$3:I$6 → $G3:$I6 → G3:I6 순서로 돌아가요.
#N/A가 떴을 때, 위에서부터 하나씩 지워 보기
범위를 고정해도 예제 7행에는 #N/A가 떠요. 7행의 D-999는 일부러 상품표에 없는 코드로 넣었거든요. FALSE로 찾았는데 똑같은 값이 없으면 VLOOKUP은 #N/A를 돌려주니까, C7과 D7의 #N/A는 정상입니다.
그런데 분명히 표에 있는 코드인데도 #N/A가 뜰 때가 있어요. 그럴 때는 먼저 마지막 인수가 FALSE인지 보고, 아래 순서대로 의심 가는 곳을 지워 가면 원인을 좁힐 수 있어요. 확인 수식은 예제의 A7과 상품표 첫 셀 G3을 기준으로 적었습니다.
- 정말 표에 없는 값인가? 상품표 첫 열에서 Ctrl+F로 그 코드를 찾아보세요. 안 나오면 표에 추가하거나, 아래에 나오는 IFERROR로 안내 문구를 띄우면 됩니다.
- 눈에 안 보이는 공백이 붙어 있나? 빈 셀에
=LEN(A7)을 넣어 보세요. LEN은 공백도 한 글자로 셉니다. D-999는 다섯 글자인데 6이 나온다면 어딘가 공백이 숨어 있다는 뜻이에요.=VLOOKUP(TRIM(A7),...)처럼 TRIM으로 감싸면 앞뒤 공백이 빠져요. - 한쪽은 숫자, 한쪽은 글자인가? 1001처럼 숫자로 된 코드에서 자주 생겨요. 겉보기엔 같아도 엑셀은 숫자와 텍스트를 다른 값으로 봅니다.
=ISNUMBER(A7)과=ISNUMBER(G3)의 결과가 서로 다르면 형식이 다른 거예요. 텍스트로 저장된 숫자를 숫자로 바꾸는 식으로 두 쪽 형식을 맞춰 주세요. - 범위가 상품코드 열에서 시작하나? VLOOKUP은 찾을 값이 범위의 맨 왼쪽 열에 있어야 해요. 범위를 상품코드 열부터 다시 잡거나, 아래에서 소개할 XLOOKUP을 쓰면 됩니다.
정말 없는 값이면 #N/A 대신 안내 문구 띄우기
점검해 봐도 정말 표에 없는 코드라면 #N/A를 그대로 둘 이유가 없어요. 그래서 알아보기 쉬운 문구를 대신 띄워 둡니다. IFERROR 괄호 안에는 두 가지를 넣어요. 앞에는 원래 수식을, 뒤에는 오류가 났을 때 대신 보여 줄 값을 씁니다. 수식이 제대로 계산되면 원래 결과가 그대로 나오고, 오류가 나면 뒤에 적은 값이 나와요.
=IFERROR(VLOOKUP(A7,$G$3:$I$6,2,FALSE),"상품표에 없음")
캡처 속 D7 단가 셀에는 =IFERROR(VLOOKUP(A7,$G$3:$I$6,3,FALSE),0)을 넣어서 오류일 때 0이 나오게 했어요. 나중에 합계를 낼 셀이라면 글자보다 숫자 0이 계산하기 편하거든요.
하나 조심할 게 있어요. IFERROR는 #N/A만이 아니라 #VALUE!, #REF! 같은 다른 오류도 전부 가립니다. 그래서 수식을 처음 만들 때는 IFERROR 없이 결과부터 보고, 원인을 다 확인한 뒤에 감싸는 순서를 권해요.
VLOOKUP 말고 XLOOKUP도 있어요
그런데 점검 4번처럼 찾을 값이 범위 맨 왼쪽에 없으면 VLOOKUP으로는 찾을 수 없어요. 이럴 때 쓸 수 있는 새 함수가 XLOOKUP입니다. Microsoft는 XLOOKUP을 VLOOKUP의 개선된 버전으로 소개하는데, 왼쪽 방향으로도 찾을 수 있고 따로 정하지 않으면 정확히 일치로 찾아요. 다만 Microsoft 365, Excel 2021, Excel 2024 등에서 쓸 수 있고, Excel 2016과 2019에서는 쓸 수 없습니다. 파일을 주고받는 상대가 있다면 그쪽 엑셀 버전부터 확인해 보세요.
참고 자료
- Microsoft 지원: VLOOKUP 함수
- Microsoft 지원: VLOOKUP 함수에서 #N/A 오류를 수정하는 방법
- Microsoft 지원: IFERROR 함수
- Microsoft 지원: XLOOKUP 함수
- Microsoft 지원: 상대 참조, 절대 참조 및 혼합 참조 간 전환
- Microsoft 지원: LEN 함수 · IS 함수(ISNUMBER)
- 본문 화면: Microsoft Excel 캡처. Used with permission from Microsoft.