엑셀 VLOOKUP 사용법, 오류 없이 틀리는 값까지 잡는 실무 예제 총정리

엑셀 VLOOKUP 사용법, 오류 없이 틀리는 값까지 잡는 실무 예제 총정리 1

AI 활용

상품명은 채워졌는데 다른 상품이 들어갔다면, 오류 표시가 없어도 안심할 수 없습니다. 엑셀 VLOOKUP은 네 번째 인수를 생략하면 정확히 같은 값을 찾는 방식으로 작동하지 않습니다. 주문서나 거래처 목록에서는 이 차이 때문에 잘못된 결과를 놓칠 수 있어요.

​

VLOOKUP은 상품코드나 사번을 기준으로 같은 행의 정보를 가져오는 함수입니다. 기본 수식만 외우기보다 기준 열의 위치와 복사할 때 바뀌는 참조를 함께 이해해야 활용하기 편합니다. 상품명 조회부터 시작하면 구조를 눈으로 따라가기 쉬워요.

​

이 글에서는 ① 상품명 조회 수식 만들기, ② 정확한 일치와 근사 일치 구분하기, ③ 범위를 고정해 아래로 채우기를 먼저 다룹니다. 이어서 ④ 값이 있는데도 못 찾는 원인과 ⑤ 결과를 보고서에 넣기 전 확인할 내용을 설명합니다. 예제는 연습용 상품코드로 따라가시면 됩니다.

​

1. 상품코드로 이름을 가져오려면?

​

먼저 ‘상품표’ 시트의 A열에는 코드, B열에는 상품명, C열에는 단가를 둔다고 가정할게요. 연습용 대응 관계는 P101은 공책, P103은 지우개, P110은 형광펜입니다. 조회할 코드가 범위의 맨 왼쪽 열에 있어야 한다는 점부터 확인해 주세요.

​

‘주문표’ 시트 A2에 상품코드를 입력하고 B2에는 =VLOOKUP(A2,상품표!$A$2:$C$7,2,FALSE)를 입력합니다. 이때 상품표의 실제 목록은 A2:C7 안에 있어야 합니다. A2가 P101이고 원본에 올바르게 등록되어 있다면 상품명으로 공책을 가져옵니다.

​

쉼표로 나뉜 네 자리는 찾을 값, 조회 범위, 가져올 열 번호, 일치 방식입니다. 여기서 2는 지정한 범위 안의 두 번째 열인 상품명을 뜻해요. 단가를 가져오려면 세 번째 인수를 3으로 바꾸면 됩니다.

​

2. 마지막 FALSE가 중요한 이유

​

상품번호처럼 정확한 식별자를 찾을 때는 FALSE를 사용합니다. 같은 의미로 숫자 0을 넣어도 됩니다. 이 방식에서는 첫 번째 열을 정렬하지 않아도 정확히 일치하는 값을 찾습니다.

​

반대로 마지막 인수를 생략하면 TRUE인 근사 일치로 처리합니다. 근사 일치는 기준 열이 오름차순으로 정렬되어 있다는 전제가 필요합니다. 정렬되지 않은 목록에서는 엉뚱한 값이 나올 수 있으므로, 오류가 없다는 이유만으로 결과를 확정하면 안 됩니다.

​

TRUE는 점수별 등급처럼 구간을 찾을 때 활용합니다. 기준이 오름차순이라면 조회값 이하의 가장 큰 기준값에 해당하는 결과를 가져와요. 코드가 없어서 생긴 #N/A를 없애려고 FALSE를 TRUE로 바꾸면 조회 목적 자체가 달라집니다.

​

엑셀 VLOOKUP 사용법, 오류 없이 틀리는 값까지 잡는 실무 예제 총정리 2

AI 활용

마지막 FALSE가 중요한 이유

3. 아래로 채우면 왜 틀어질까요?

​

첫 행은 맞는데 아래 행부터 실패한다면 조회 범위를 살펴보세요. A2:C7처럼 상대참조로 입력한 범위는 수식을 아래로 복사할 때 A3:C8처럼 이동합니다. 찾을 상품이 이동한 범위 밖으로 빠지면 조회하지 못합니다.

​

상품표!$A$2:$C$7처럼 열과 행에 달러 기호를 붙이면 범위를 고정할 수 있어요. 반면 주문표의 조회값 A2는 아래 행에서 A3로 바뀌어야 합니다. 바뀌어야 할 셀과 유지해야 할 범위를 나눠 생각하면 이해하기 쉽습니다.

​

수식 입력 중 범위 부분을 선택하고 F4를 누르면 절대참조로 전환할 수 있습니다. 노트북에서는 Fn과 F4를 함께 눌러야 할 수도 있으며, 달러 기호를 직접 입력해도 됩니다. 다만 범위를 고정해도 새로 추가된 상품이 자동으로 포함되지는 않으니 목록의 마지막 행도 확인해 주세요.

​

4. 값이 있는데도 #N/A라면?

​

정확한 일치에서 #N/A는 조건에 맞는 값을 찾지 못했다는 신호입니다. 먼저 원본 목록에서 Ctrl+F로 코드를 검색하고 조회 범위 안에 있는지 확인합니다. 코드가 A열에 있는데 범위를 B열부터 잡았다면 검색 대상부터 잘못된 상태예요.

​

목록에 있다면 조회값과 원본의 저장 형식을 비교해 보세요. 숫자 1002와 텍스트로 저장된 1002는 조회에서 일치하지 않을 수 있습니다. =ISTEXT(A2)로 텍스트 여부를 확인하고, 숫자 코드로 관리하는 자료라면 VALUE로 변환할 수 있어요.

​

앞뒤 일반 공백은 TRIM으로 정리하며, 조회값 쪽 문제라면 첫 번째 인수를 TRIM(A2)로 바꿀 수 있습니다. 원본 코드에 공백이 있다면 원본 쪽을 정리해야 하고, 특수 공백은 TRIM만으로 제거되지 않을 수 있어요. 또한 0012처럼 앞자리 0이 의미 있는 코드는 무조건 숫자로 바꾸지 말고 코드 규칙부터 확인해야 합니다.

​

엑셀 VLOOKUP 사용법, 오류 없이 틀리는 값까지 잡는 실무 예제 총정리 3

AI 활용

 값이 있는데도 #N/A라면?

5. 보고서에 넣기 전 확인할 것

​

범위가 세 열인데 열 번호를 4로 지정하면 #REF!가 발생합니다. 열 번호를 0으로 넣으면 #VALUE!가 나타납니다. 중간에 열을 삽입한 뒤에는 결과가 나오더라도 의도한 열을 가져오는지 다시 확인해야 해요.

​

같은 코드가 여러 번 있으면 VLOOKUP은 첫 번째 일치 결과만 반환합니다. 여러 거래를 합산하거나 조건에 맞는 항목을 전부 가져오는 용도와는 다릅니다. 왼쪽 열의 값을 가져와야 한다면 INDEX·MATCH나, 지원되는 버전의 XLOOKUP을 검토할 수 있어요.

​

원인을 해결한 뒤에는 =IFERROR(VLOOKUP(A2,상품표!$A$2:$C$7,2,FALSE),"코드 확인")으로 표시를 정리할 수 있습니다. IFERROR는 #N/A 외의 오류도 가리므로 처음부터 적용하기보다 검산 후 사용하는 편을 권합니다. 특히 단가 조회 실패를 0으로 덮으면 실제 누락과 무료 항목을 구분하기 어려워집니다.

​

함수 구문과 동작 기준은 Microsoft 지원의 VLOOKUP 함수 설명을 참고했습니다. 수식을 채운 뒤에는 원본에서 확인 가능한 코드와 목록에 없는 코드를 각각 넣어 보세요. 정상 조회와 조회 실패를 모두 구분할 수 있어야 실제 업무에서도 결과를 판단할 수 있습니다.

​

엑셀 VLOOKUP 사용법, 오류 없이 틀리는 값까지 잡는 실무 예제 총정리 4

AI 활용

보고서에 넣기 전 확인할 것