Vlookup 함수 실전 활용, 상황별 차이

VLOOKUP은 다른 시트의 값을 그대로 가져올 때 가장 빠른 방법입니다. 조회 값이 범위의 첫 번째 열에 있는 단순한 상황에 적합합니다.

찾는 값이 왼쪽 열에 없거나 조건이 두 개 이상이면 기본 설정만으로는 오류가 납니다. 상황에 따라 도움 열, INDEX/MATCH, XLOOKUP 중 하나를 골라야 합니다.

다른 시트 데이터를 합칠 때, 기본 설정부터 맞춥니다

다른 시트 값을 가져올 때는 조회 값, 범위, 열 번호, 일치 옵션 네 가지를 순서대로 채웁니다. 이 중 하나만 틀려도 결과가 어긋납니다.

범위는 항상 절대참조(F4)로 고정해야 합니다. 고정하지 않으면 수식을 아래로 채울 때 범위가 밀려서 빈 값이 나옵니다.

수식 구조와 자주 틀리는 인수

열 번호는 조회 범위의 첫 열을 1로 세어 계산합니다. 시트에 열을 하나 추가하면 이 번호도 함께 바뀌어야 하므로 매번 확인이 필요합니다.

일치 옵션은 거의 모든 업무 보고서에서 FALSE나 0으로 둡니다. TRUE로 두면 정렬되지 않은 데이터에서 엉뚱한 값이 붙습니다.

조건이 두 개 이상일 때, 도움 열로 우회합니다

VLOOKUP은 조건 하나만 검색하도록 설계되어 있습니다. 제품명과 지역처럼 조건이 두 개면 기본 수식만으로는 값을 찾지 못합니다.

이때는 조회 범위 앞에 도움 열을 하나 추가합니다. 두 조건을 & 연산자로 붙여 새로운 열을 만들고 그 열을 기준으로 찾습니다.

& 연산자로 조회 값 만들기

조회 값도 똑같은 방식으로 만들어야 합니다. 제품명 셀과 지역 셀을 &로 연결한 값을 VLOOKUP의 첫 번째 인수에 넣습니다.

도움 열은 데이터가 늘어날 때마다 함께 채워야 합니다. 자동 채우기를 걸어두지 않으면 새로 추가된 행에서 오류가 납니다.

• 조회 범위 왼쪽에 도움 열 삽입
• =제품명&지역 형태로 값 채우기
• VLOOKUP 조회 값도 동일한 방식으로 연결
• 열 번호를 도움 열 기준으로 다시 계산

#N/A 오류가 뜨는 상황별 원인

#N/A는 조회 값이 범위 안에 아예 없을 때 가장 먼저 뜹니다. 오타나 앞뒤 공백 하나 때문에 발생하는 경우가 많습니다.

데이터 형식이 다를 때도 같은 오류가 납니다. 숫자로 보이는데 실제로는 텍스트로 저장된 코드값이 대표적입니다.

상황별 해결법

공백이 의심되면 TRIM 함수로 조회 값과 범위를 한 번씩 정리합니다. 형식이 의심되면 VALUE나 TEXT로 형식을 통일합니다.

오류 자체를 감추고 싶다면 IFERROR로 VLOOKUP을 감쌉니다. #N/A 대신 공백이나 원하는 문구를 표시할 수 있습니다.

예를 들어 거래처 코드가 '001'처럼 앞에 0이 붙은 텍스트라면, 상대 시트에서는 숫자 1로 저장돼 있을 수 있습니다. 이때는 TEXT 함수로 형식을 맞춘 뒤 다시 찾아야 합니다.

왼쪽 열을 찾아야 하는 상황, INDEX/MATCH가 필요합니다

VLOOKUP은 조회 값이 항상 범위의 첫 번째 열에 있어야 합니다. 왼쪽에 있는 열의 값을 오른쪽 열 기준으로 찾아오는 건 원천적으로 불가능합니다.

이런 상황에서는 INDEX와 MATCH를 조합합니다. MATCH가 위치를 찾고 INDEX가 그 위치의 값을 가져오는 구조입니다.

INDEX/MATCH 기본 구조

MATCH(조회값, 범위, 0)은 값이 몇 번째 행에 있는지 숫자로 알려줍니다. INDEX(결과범위, 그 숫자)로 최종 값을 반환합니다.

열을 추가하거나 순서를 바꿔도 INDEX/MATCH는 그대로 작동합니다. VLOOKUP처럼 열 번호를 다시 세지 않아도 된다는 점이 가장 큰 차이입니다.

• 왼쪽 열 조회: VLOOKUP은 불가능, INDEX/MATCH는 가능
• 열 추가 대응: VLOOKUP은 번호 재계산 필요, INDEX/MATCH는 그대로 작동
• 구조 변경 잦은 보고서: INDEX/MATCH 쪽이 안정적인 경우가 많습니다

대용량 데이터나 잦은 구조 변경, XLOOKUP과 대안을 고려할 때

시트 구조가 자주 바뀌거나 데이터 양이 많다면 XLOOKUP을 검토할 만합니다. 열 번호 대신 범위 자체를 지정하기 때문에 구조 변경에 덜 민감합니다.

다만 XLOOKUP은 최신 버전의 엑셀과 Microsoft 365에서만 지원됩니다. 오래된 버전을 함께 쓰는 환경이라면 파일 호환성부터 확인해야 합니다.

XLOOKUP이 유리한 경우

여러 시트를 넘나드는 보고서가 반복적으로 늘어난다면 함수 하나로 버티기보다 데이터베이스 기반 도구를 검토하는 편이 낫습니다. 데이터 양이 계속 커지는 상황에서는 특히 그렇습니다.

파워쿼리나 별도의 데이터 관리 솔루션은 시트 여러 개를 수동으로 연결하는 수고를 줄여줍니다. 수식 오류를 찾아다니는 시간 자체가 줄어드는 셈입니다.

실무에서 막히는 상황과 체크리스트

수식은 맞는데 값이 하나도 안 나온다면 범위 지정이 상대참조로 되어 있는지부터 봅니다. 아래로 채우면서 범위가 밀린 경우가 흔합니다.

값은 나오는데 전부 틀렸다면 일치 옵션이 TRUE로 되어 있을 가능성이 큽니다. FALSE나 0으로 바꾸고 다시 확인합니다.

일부 행만 오류가 난다면 그 행의 조회 값에 공백이나 특수문자가 섞여 있는지 확인합니다. 데이터를 옮겨 붙이는 과정에서 자주 생기는 문제입니다.

복사해 쓰는 점검 순서

• 1. 범위를 F4로 절대참조했는가
• 2. 일치 옵션이 FALSE 또는 0인가
• 3. 조회 값과 범위의 데이터 형식이 같은가
• 4. 앞뒤 공백이 섞여 있지 않은가
• 5. 열 번호가 최신 구조와 맞는가

이 다섯 가지만 순서대로 확인해도 VLOOKUP 오류의 많은 부분은 해결됩니다. 그래도 안 풀리면 INDEX/MATCH로 바꿔보는 편이 빠를 수 있습니다.

자주 묻는 질문