[Skill/Excel] 매번 기준표 열어서 하나씩 찾고 있다면, 이 함수 하나로 끝납니다

실무에서 이런 상황이 있습니다.

거래처 코드를 보고 거래처명을 확인해야 할 때. 직원 번호를 보고 소속 부서를 찾아야 할 때. 상품코드를 보고 단가표에서 가격을 가져와야 할 때. 매번 기준표 파일을 열고, 눈으로 쭉 훑어서 일치하는 값을 찾아 복사해 붙입니다. 데이터가 100건이라면 100번 반복합니다.

 

이 작업에 하루 30분씩 쓰고 있다면 한 달이면 10시간이 넘습니다. 함수 하나(VLOOKUP 또는 XLOOKUP)만 알아도 이 시간을 대부분 줄일 수 있습니다.

1. 왜 기준표 대조가 이렇게 오래 걸리는가

문제의 핵심은 "찾기"를 사람이 하고 있다는 점입니다.

기준표가 별도 파일에 있고, 실적 데이터에는 코드만 있습니다. 이때 사람이 할 수 있는 방법은 두 가지입니다. 코드를 복사해서 기준표에서 Ctrl+F로 찾거나, 두 파일을 나란히 놓고 눈으로 하나씩 맞춰보는 것입니다.

 

양이 적을 때는 괜찮습니다. 문제는 데이터가 쌓일수록, 기준표가 커질수록, 이 과정이 선형으로 늘어난다는 점입니다. 오타가 하나라도 끼어 있으면 잘못된 값을 가져와도 알아차리기 어렵습니다.

 

이 문제를 해결하는 것이 바로 찾기 함수입니다.

2. VLOOKUP의 기본 구조를 먼저 이해해야 한다

VLOOKUP은 "특정 값을 기준표에서 찾아서, 그 행의 지정한 열 값을 가져온다"는 함수입니다.

=VLOOKUP(찾을 값, 기준표 범위, 가져올 열 번호, 0)

 

실제 예시를 들겠습니다.

 

A열에 거래처코드(G001, G002...)가 있고, 별도 시트에 코드-거래처명-담당자 순서의 기준표가 있을 때, B열에 거래처명을 자동으로 채우고 싶다면 이렇게 씁니다.

=VLOOKUP(A2, 기준표!A:C, 2, 0)

• A2: 찾을 거래처코드
• 기준표!A:C: 기준표가 있는 범위
• 2: 기준표에서 2번째 열(거래처명)을 가져올 것
• 0: 정확히 일치하는 값만 찾을 것 (반드시 0으로 설정)

이 수식을 B2에 입력하고 아래로 복사하면 100행이든 1,000행이든 순식간에 채워집니다.

 

3. VLOOKUP의 한계와 XLOOKUP이 필요한 상황

VLOOKUP은 강력하지만 두 가지 구조적 한계가 있습니다.

 

첫째, 기준열이 반드시 기준표의 첫 번째 열이어야 합니다.

기준표가 "거래처명-코드-담당자" 순서라면, 코드로 거래처명을 찾으려 해도 코드가 첫 번째 열이 아니기 때문에 VLOOKUP으로는 바로 처리할 수 없습니다.

 

둘째, 기준표에 열이 추가되면 열 번호가 밀립니다.

기준표에 새 열을 끼워 넣으면 함수 안의 열 번호를 하나씩 다 수정해야 합니다.

이런 상황에서는 XLOOKUP이 훨씬 유연합니다.

=XLOOKUP(찾을 값, 찾을 범위, 가져올 범위)

같은 예시를 XLOOKUP으로 쓰면 이렇습니다.

=XLOOKUP(A2, 기준표!B:B, 기준표!A:A)

찾는 범위와 가져오는 범위를 분리해서 지정하기 때문에 기준표 구조가 바뀌어도 수식이 흔들리지 않습니다. Microsoft 365 또는 Excel 2021 이상 버전에서 사용할 수 있습니다. 만약 XLOOKUP을 쓸 수 없는 구버전이라면 INDEX+MATCH 조합으로 같은 효과를 낼 수 있습니다.

4. 실무에서 자주 하는 실수 3가지

첫째, 마지막 인수를 생략하는 경우입니다.

VLOOKUP의 마지막 인수(0 또는 FALSE)를 쓰지 않으면 "근사값 일치" 모드로 동작합니다. 정렬된 데이터에서는 가장 가까운 값을 가져오기 때문에 전혀 다른 값이 들어와도 오류 없이 넘어갑니다. 반드시 0을 명시해야 합니다.

 

둘째, 기준표 범위를 절대참조로 고정하지 않는 경우입니다.

수식을 아래로 복사할 때 기준표 범위도 같이 내려가면 엉뚱한 범위를 참조하게 됩니다. 기준표 범위는 _$A$2:$C$100처럼 달러 기호를 붙여 고정_해야 합니다. F4 키를 눌러 빠르게 절대참조로 전환할 수 있습니다.

 

셋째, 기준표 공백 혼입으로 #N/A 오류가 나는 경우입니다.

눈으로 봤을 때 같은 값인데 VLOOKUP이 #N/A 오류를 돌려주는 경우가 있습니다. 코드 앞뒤에 공백이 끼어 있는 경우가 가장 흔합니다. 이때는 _TRIM 함수로 공백을 정리한 뒤 찾는 것_이 안전합니다.

 

5. 기준표 대조 자동화의 실전 흐름

찾기 함수를 실무에 적용할 때 권장하는 순서입니다.

• 기준표를 별도 시트 또는 별도 파일로 분리해 관리할 것
• 기준표의 첫 번째 열은 반드시 코드나 고유 식별자로 구성할 것 (VLOOKUP 사용 시)
• 실적 데이터에 코드 열이 있으면 VLOOKUP/XLOOKUP으로 나머지 정보를 자동으로 채울 것
• 수식 입력 후 오류(#N/A)가 나는 행은 코드 불일치를 의미하므로, 오류 행만 따로 확인할 것
• 수식이 완성되면 값 붙여넣기로 고정해두면 기준표가 바뀌어도 과거 데이터가 흔들리지 않음

기준표와 실적 데이터를 분리 관리하는 구조가 갖춰지면, 이후 새 데이터가 들어올 때마다 수식이 자동으로 값을 채워줍니다. 사람이 하나씩 찾아 입력하는 과정은 사라집니다.

6. 마무리 체크리스트

아래 항목 중 해당되는 게 있다면 VLOOKUP/XLOOKUP 도입을 먼저 검토해보세요.

• 코드나 번호를 보고 이름, 부서, 가격을 기준표에서 매번 찾아 입력한다
• 기준표와 실적 파일을 나란히 놓고 눈으로 대조한다
• 데이터 행이 많을수록 이 과정에 시간이 선형으로 늘어난다
• 가끔 잘못된 값이 들어가 있어도 뒤늦게 발견한다
• Ctrl+F로 기준표를 검색하는 일이 하루에도 여러 번 있다

2개 이상 해당된다면 지금 당장 함수 하나로 바꿀 수 있는 업무입니다.

찾기 함수는 익히는 데 30분이면 충분합니다. 그 30분이 매달 수 시간을 돌려줍니다.