엑셀 VLOOKUP 함수 사용법 – 실무에서 바로 쓰는 완벽 가이드

icon

엑셀을 쓰다 보면 두 개의 표에서 공통 값을 기준으로 데이터를 가져와야 하는 상황이 자주 생긴다. 이때 가장 많이 쓰는 함수가 VLOOKUP이다. 이름은 어렵게 들리지만 원리를 한번 이해하면 업무 효율이 확 달라진다.

VLOOKUP 함수란 – 기본 구조

VLOOKUP은 Vertical Lookup의 줄임말로, 세로 방향으로 값을 찾아서 같은 행의 다른 열 데이터를 가져오는 함수다. 기본 구조는 다음과 같다.

=VLOOKUP(찾을값, 범위, 열번호, 일치유형)

가장 흔한 활용 사례로 이해해보자. A열에 사원번호, B열에 이름이 있는 ‘사원 목록’ 표가 있고, 다른 시트에서 사원번호만 가지고 이름을 가져오고 싶을 때 VLOOKUP을 쓴다.

=VLOOKUP(D2, A:B, 2, FALSE)

이 수식의 의미는 “D2 셀의 값을 A열에서 찾고, 찾으면 같은 행의 B열(2번째 열) 값을 가져와라. 정확히 일치하는 것만 찾아라”이다.

VLOOKUP을 사용할 때 가장 많이 하는 실수와 해결법을 정리했다.

첫째, #N/A 오류가 뜨는 경우. 찾을 값이 범위의 첫 번째 열에 없을 때 발생한다. 범위 설정이 올바른지 확인하자. 찾을 값에 공백이 섞여 있어도 #N/A가 뜰 수 있으니 TRIM 함수로 공백을 제거하는 것도 방법이다.

둘째, 범위를 절대참조($)로 고정하지 않는 실수. 수식을 아래로 복사하면 범위도 같이 밀려서 엉뚱한 결과가 나온다. 범위는 반드시 $A$1:$C$100처럼 절대참조로 고정해야 한다.

셋째, 열 번호를 잘못 세는 경우. 열 번호는 범위의 첫 번째 열부터 세는 것이지, 워크시트의 A열부터 세는 게 아니다.

넷째, 일치유형을 생략하는 실수. 일치유형을 생략하면 기본값이 TRUE(근사치)가 되어 엉뚱한 값을 가져올 수 있다. 꼭 FALSE를 명시하자.

다섯째, 찾을 값이 범위의 첫 번째 열에 없는 경우. VLOOKUP은 무조건 왼쪽에서 오른쪽으로만 찾는다. 오른쪽 열을 기준으로 왼쪽 데이터를 가져오려면 INDEX+MATCH 조합을 써야 한다.

실무에서는 VLOOKUP 대신 INDEX+MATCH 조합을 쓰는 사람도 많다. 두 방식의 차이를 알아보자.

VLOOKUP의 장점은 배우기 쉽고 직관적이라는 것이다. 반면 약점이 있는데, 왼쪽 방향으로 데이터를 가져올 수 없고, 열을 추가·삭제하면 열 번호가 틀어질 수 있다는 점이다.

INDEX+MATCH는 방향 제한이 없고, 열 번호 대신 열 참조를 사용해서 구조 변경에 강하다. 다만 수식이 조금 더 복잡해 보인다. 마이크로소프트 엑셀 도움말에서 상세한 함수 설명을 확인할 수 있다.

엑셀 365나 최신 버전을 사용한다면 XLOOKUP을 쓸 수 있다. VLOOKUP의 모든 한계를 해결한 함수다. 왼쪽 방향 검색이 가능하고, 열 번호 대신 반환 범위를 직접 지정해서 실수가 줄어든다.

=XLOOKUP(D2, A:A, B:B, "없음")

네 번째 인수로 찾지 못했을 때의 기본값을 설정할 수 있어서 #N/A 오류 처리도 간편하다. 마이크로소프트 오피스 지원에서 XLOOKUP 상세 사용법을 확인할 수 있다.

Q. VLOOKUP에서 여러 조건으로 검색할 수 있나?

A. VLOOKUP 자체는 단일 조건만 지원한다. 여러 조건이 필요하면 보조 열에 조건들을 합친 값을 만들어서 검색하거나, INDEX+MATCH+MATCH 또는 XLOOKUP을 활용하는 것이 좋다.

Q. 다른 파일의 데이터도 VLOOKUP으로 가져올 수 있나?

A. 가능하다. 범위 부분에 [파일명]시트명!범위 형식으로 입력하면 된다. 다만 참조하는 파일이 닫혀 있으면 전체 경로를 포함해야 하고, 속도가 느려질 수 있다.

Q. VLOOKUP 결과가 0으로 나오는 이유는?

A. 찾은 셀이 비어 있을 때 VLOOKUP은 0을 반환한다. 빈 셀을 빈 칸으로 표시하려면 IF 함수와 조합해서 =IF(VLOOKUP(…)=””,””,VLOOKUP(…)) 형태로 쓰면 된다.