엑셀 VLOOKUP 사용법: 정확히 일치·범위 고정·오류 해결

상품 코드가 A2에 있고 코드·상품명·가격 표가 F2:H100에 있다면 가격 조회는 =VLOOKUP(A2,$F$2:$H$100,3,FALSE)처럼 구성할 수 있습니다. 핵심은 조회값이 표 범위의 첫 열에 있어야 하고, 반환할 가격이 그 범위의 세 번째 열이며, 정확히 일치는 FALSE로 지정한다는 점입니다.

Microsoft 공식 문서는 VLOOKUP을 표나 범위에서 행별 항목을 찾는 함수로 설명합니다. 수식을 복사하기 전에 한 행에서 정확히 작동하는지 확인하고, 원본 표의 중복·공백·숫자/텍스트 형식을 점검하세요.

VLOOKUP 네 인수

인수	역할	확인할 점
lookup_value	찾을 값	셀 값의 형식과 공백
table_array	찾을 표 범위	조회 열이 맨 왼쪽
col_index_num	돌려줄 열 번호	범위 안에서 1부터 계산
range_lookup	정확/근사 일치	FALSE 또는 TRUE 명시

1. 기본 수식 구조

구문은 VLOOKUP(조회값, 표범위, 열번호, 일치방식)입니다. 조회값은 표범위 첫 번째 열에서 찾습니다. 일치하는 행을 발견하면 같은 행의 지정한 열 번호 값을 반환합니다.

열 번호는 워크시트의 실제 열 문자가 아니라 table_array 안에서 왼쪽부터 센 번호입니다. F:H 범위라면 F는 1, G는 2, H는 3입니다.

2. 정확히 일치에는 FALSE

상품 코드, 사번, 주문번호처럼 같은 값을 정확히 찾아야 하면 네 번째 인수에 FALSE를 씁니다. Microsoft 문서는 FALSE 또는 0이 정확한 일치를 찾는다고 안내합니다. 인수를 생략하면 근사 일치가 기본이 될 수 있어 예상치 못한 값이 반환될 수 있습니다.

결과가 나왔다고 항상 맞는 것은 아닙니다. 일부 표본 코드를 원본 표에서 직접 찾아 반환값을 비교하세요. 잘못된 근사 결과는 오류 표시 없이 나타날 수 있어 더 위험합니다.

TRUE는 언제 쓰나요?

세율 구간이나 등급 기준처럼 가장 가까운 하한값을 찾는 표에서는 TRUE 근사 일치를 사용할 수 있습니다. 이 경우 첫 열이 올바르게 정렬되어야 합니다. 정렬되지 않은 표에 근사 일치를 쓰면 잘못된 결과가 나올 수 있습니다.

3. 조회 열은 범위의 첫 열

VLOOKUP은 table_array의 맨 왼쪽 열에서 조회값을 찾습니다. 반환 열이 조회 열의 왼쪽에 있다면 범위를 바꾸거나 INDEX/MATCH, 지원되는 Excel에서는 XLOOKUP을 고려하세요.

표에 새 열을 삽입해 조회 열이 더 이상 첫 열이 아니게 되면 수식 의미가 달라질 수 있습니다. 열 구조를 변경한 뒤 조회 수식을 다시 검산합니다.

4. 열 번호 계산

col_index_num은 table_array의 첫 열을 1로 셉니다. 전체 워크시트의 열 번호를 넣는 것이 아닙니다. 반환할 열이 범위 밖이면 #REF! 오류가 날 수 있습니다.

중간 열을 삽입하거나 삭제하면 고정된 열 번호가 다른 항목을 가리킬 수 있습니다. 표 구조 변경이 잦다면 XLOOKUP처럼 반환 배열을 직접 지정하는 함수가 관리하기 쉬울 수 있습니다.

5. 범위를 절대참조로 고정

수식을 아래로 복사할 때 table_array가 F2:H100에서 F3:H101로 밀리면 조회 결과가 달라집니다. 범위에 달러 기호를 붙여 $F$2:$H$100처럼 절대참조로 고정하세요. 수식 편집 중 F4로 참조 형식을 바꿀 수 있는 환경도 있습니다.

조회값 A2는 아래로 복사할 때 A3, A4로 바뀌어야 하므로 보통 상대참조를 유지합니다. 어느 부분이 움직여야 하는지 먼저 정한 뒤 달러 기호를 적용하세요.

6. #N/A 오류

#N/A는 조회값을 찾지 못했을 때 흔히 나타납니다. 원본 표에 값이 실제로 있는지, 앞뒤 공백이 있는지, 숫자와 숫자 모양 텍스트가 섞였는지 확인하세요. Microsoft는 선행·후행 공백과 인쇄되지 않는 문자가 예기치 않은 결과를 만들 수 있다고 설명합니다.

TRIM은 불필요한 일반 공백, CLEAN은 일부 인쇄되지 않는 문자를 정리하는 데 도움이 됩니다. 원본을 직접 덮어쓰기 전에 정리용 새 열에서 결과를 비교합니다.

숫자와 텍스트 구분

코드 00123을 숫자 123으로 바꾸면 앞자리 0이 사라집니다. 조회 열과 조회값을 같은 데이터 형식으로 맞추되 식별 코드는 텍스트로 보존하는 것이 좋습니다.

7. #REF! 오류

지정한 열 번호가 table_array의 열 수보다 크거나 참조 열이 삭제되면 #REF! 오류가 발생할 수 있습니다. 범위를 선택해 실제 열 수를 세고, 삭제한 열이 수식에 필요했는지 확인하세요.

오류만 IFERROR로 숨기기 전에 원인을 고쳐야 합니다. 구조 오류를 빈칸으로 바꾸면 데이터 누락을 발견하기 어려워집니다.

8. #VALUE!와 #NAME? 오류

열 번호가 1보다 작거나 인수 형식이 잘못되면 #VALUE!가 날 수 있습니다. 함수 이름 오타, 인수 구분자, 텍스트 따옴표 문제가 있으면 #NAME?이 보일 수 있습니다. Excel 언어와 지역 설정에 따라 인수 구분 기호가 다를 수도 있습니다.

수식 입력줄에서 괄호와 쉼표, 따옴표를 확인하고 가장 단순한 수식으로 줄여 어느 인수에서 문제가 생기는지 찾습니다.

9. 중복 조회값

조회 열에 같은 코드가 여러 번 있으면 VLOOKUP은 첫 번째로 만난 일치 항목을 반환합니다. 중복이 허용되는 자료라면 어떤 행을 선택해야 하는지 기준을 추가해야 합니다.

먼저 조건부 서식이나 피벗테이블, COUNTIF로 중복을 확인하세요. 여러 결과를 모두 반환해야 한다면 VLOOKUP 하나로 해결하려 하지 말고 FILTER 같은 지원 함수나 데이터 정리 방법을 검토합니다.

10. 테이블 사용

원본 범위를 Excel 테이블로 만들면 행이 추가될 때 범위 관리가 쉬워질 수 있습니다. 머리글이 명확하고 빈 행·열 없이 이어진 자료가 적합합니다. 테이블 이름과 열 제목은 뜻이 분명하게 정하세요.

외부 파일을 참조한다면 파일 위치와 권한 변화도 확인합니다. 공유받는 사람이 원본 파일에 접근할 수 없으면 값 갱신이 실패할 수 있습니다.

11. IFNA로 표시 바꾸기

IFNA는 수식이 #N/A를 반환할 때 지정한 값을 보여 주고, 그렇지 않으면 원래 결과를 반환합니다. 예를 들어 조회 실패를 ‘확인 필요’로 표시할 수 있습니다.

빈칸으로 숨기면 누락을 알아채기 어려우므로 처음에는 명확한 경고 문구를 쓰세요. #REF!나 #VALUE! 같은 다른 오류까지 무조건 숨기지 말고 원인을 구분합니다.

12. XLOOKUP과 차이

Microsoft는 XLOOKUP을 VLOOKUP의 개선된 함수로 소개합니다. 조회 배열과 반환 배열을 따로 지정할 수 있어 왼쪽과 오른쪽 어느 방향으로도 찾을 수 있고 기본값이 정확히 일치입니다.

다만 Microsoft 안내에 따르면 XLOOKUP은 Excel 2016과 Excel 2019에서 사용할 수 없습니다. 파일을 주고받을 상대의 버전을 확인하고 호환성이 필요하면 VLOOKUP이나 INDEX/MATCH를 유지하세요.

13. 수식 복사 전 검산

1. 조회값이 원본 표에 있는지 직접 찾습니다.
2. 조회 열이 table_array 첫 열인지 확인합니다.
3. 반환 열 번호를 범위 안에서 셉니다.
4. 정확히 일치라면 FALSE를 명시합니다.
5. 표 범위를 절대참조로 고정합니다.
6. 정상·누락·중복 값으로 각각 시험합니다.
7. 수식을 채운 뒤 첫 행·중간 행·마지막 행을 검산합니다.

14. 흔한 실수

• 네 번째 인수를 생략해 근사 일치가 적용됩니다.
• 조회 열이 표 범위의 첫 열이 아닙니다.
• 열 번호를 워크시트 기준으로 셉니다.
• 수식 복사 때 표 범위가 함께 이동합니다.
• 숫자와 텍스트 코드가 섞여 있습니다.
• 중복 조회값에서 첫 번째 결과만 반환됩니다.
• 오류를 숨기고 원인을 확인하지 않습니다.

자주 묻는 질문

VLOOKUP의 FALSE는 무슨 뜻인가요?

조회값과 정확히 같은 항목을 찾으라는 뜻입니다. 코드 조회에는 보통 FALSE를 명시합니다.

왜 조회값이 첫 열에 있어야 하나요?

VLOOKUP은 지정한 표 범위의 첫 열에서 값을 찾고 오른쪽 열의 값을 반환하기 때문입니다.

#N/A가 뜨는 이유는 무엇인가요?

값이 없거나 공백·숨은 문자·숫자/텍스트 형식 차이로 일치하지 않을 수 있습니다.

수식을 아래로 복사하면 결과가 틀리는 이유는 무엇인가요?

표 범위가 상대참조로 함께 이동했을 수 있습니다. table_array를 절대참조로 고정하세요.

중복된 값은 어느 행을 반환하나요?

일반적으로 첫 번째 일치 항목을 반환합니다. 중복 기준을 먼저 정리하세요.

VLOOKUP 대신 XLOOKUP을 써도 되나요?

지원 버전이고 공유 상대도 사용할 수 있다면 편리합니다. Excel 2016·2019 호환성은 확인하세요.

IFNA로 모든 오류를 빈칸 처리해도 되나요?

누락을 놓칠 수 있습니다. 처음에는 경고 문구로 표시하고 오류 원인을 해결하세요.

공식 출처

• Microsoft 지원 – VLOOKUP 함수
• Microsoft 지원 – XLOOKUP 함수
• Microsoft 지원 – IFNA 함수
• Microsoft 지원 – VLOOKUP·INDEX·MATCH 조회

마무리

VLOOKUP을 정확히 쓰려면 조회값, 첫 열이 조회 열인 표 범위, 반환 열 번호, FALSE 네 요소를 분명히 해야 합니다. 표 범위를 고정하고 공백·형식·중복을 정리한 뒤 정상값과 누락값을 시험하세요. 지원 버전에서는 XLOOKUP도 비교하되 공유 파일의 호환성을 먼저 확인합니다.