엑셀을 어느 정도 쓰다 보면 반드시 만나게 되는 함수가 VLOOKUP입니다. 사원번호로 이름을 찾거나, 상품코드로 단가를 가져오는 것처럼 한쪽 표에 있는 값을 다른 표에서 끌어오는 작업에 씁니다.
이름은 거창하지만 원리는 단순합니다. “이 값을 저 표의 첫 번째 열에서 찾아서, 같은 행의 몇 번째 열 값을 가져와라”는 명령입니다.
기본 구조
=VLOOKUP(찾을값, 찾을범위, 가져올열번호, 일치옵션)네 개의 값이 들어갑니다. 하나씩 보겠습니다.
찾을값은 기준이 되는 값입니다. 사원번호로 이름을 찾는다면 사원번호가 여기 들어갑니다.
찾을범위는 데이터가 들어 있는 표 전체입니다. 여기서 중요한 규칙이 하나 있습니다. 찾을값이 반드시 이 범위의 첫 번째 열에 있어야 합니다. 이 조건을 못 맞춰서 오류가 나는 경우가 대부분입니다.
가져올 열 번호는 범위의 왼쪽부터 세어서 몇 번째 열의 값을 가져올지입니다. 시트 전체 기준이 아니라 지정한 범위 안에서의 순서라는 점을 기억하세요.
일치 옵션은 0 또는 1을 넣습니다. 0은 정확히 일치하는 값만, 1은 비슷한 값도 허용합니다. 실무에서는 거의 항상 0을 씁니다. 이걸 빼먹으면 엉뚱한 값을 가져오는 사고가 납니다.
실제 예제
A열에 사원번호, B열에 이름, C열에 부서가 들어 있는 표가 A2부터 C100까지 있다고 하겠습니다. E2에 사원번호를 입력하면 F2에 이름이 자동으로 나오게 하고 싶습니다.
=VLOOKUP(E2, $A$2:$C$100, 2, 0)E2 값을 A2:C100 범위의 첫 열(A열)에서 찾아, 같은 행의 2번째 열(B열) 값을 가져오라는 뜻입니다.
범위에 붙은 달러 표시가 핵심입니다. 수식을 아래로 끌어내릴 때 범위가 같이 밀려 내려가는 것을 막아줍니다. 이걸 빼면 아래쪽 행에서 데이터를 못 찾는 일이 생깁니다. F4 키를 누르면 달러 표시가 자동으로 붙습니다.
자주 하는 실수 세 가지
첫째, 찾을값이 범위의 첫 열에 없는 경우입니다. VLOOKUP은 왼쪽에서 오른쪽으로만 찾습니다. 이름으로 사원번호를 찾는 것처럼 반대 방향은 불가능합니다. 그럴 때는 INDEX와 MATCH를 조합하거나, 최신 버전이라면 XLOOKUP을 쓰면 됩니다.
둘째, 열 번호를 시트 기준으로 세는 경우입니다. 범위를 C열부터 잡았다면 C열이 1번입니다. A열이 1번이 아닙니다.
셋째, 마지막 옵션을 생략하는 경우입니다. 생략하면 1로 처리되어 근사값을 찾습니다. 정렬되지 않은 데이터에서 이러면 전혀 관계없는 값이 나옵니다. 습관적으로 0을 붙이세요.
정리
VLOOKUP을 쓸 때 확인할 것은 세 가지입니다. 찾을값이 범위의 첫 열에 있는가, 범위에 달러 표시를 했는가, 마지막에 0을 넣었는가. 이 셋만 지키면 대부분 문제없이 작동합니다.
발표 자료를 다룰 일이 있다면 파워포인트 용량 줄이는 방법도 참고하세요. 오류가 계속된다면 VLOOKUP N/A 오류 원인 다섯 가지에서 원인별 해결법을 확인하실 수 있습니다. 그래도 오류가 난다면 대개 데이터 형식 문제입니다. 같은 1234라도 한쪽은 숫자, 한쪽은 텍스트로 저장되어 있으면 찾지 못합니다.
실무에서 자주 쓰는 형태
다른 시트에서 값 가져오기
원본 데이터를 별도 시트에 두고 작업 시트에서 불러오는 경우가 많습니다. 시트 이름을 느낌표와 함께 적으면 됩니다.
=VLOOKUP(A2, 원본데이터!$A$2:$D$500, 3, 0)시트 이름에 공백이 있다면 작은따옴표로 감싸야 합니다.
=VLOOKUP(A2, '거래처 목록'!$A$2:$D$500, 3, 0)직접 입력하기 번거롭다면 수식을 쓰다가 범위를 지정할 때 해당 시트를 클릭해서 마우스로 드래그하면 엑셀이 알아서 시트 이름을 붙여줍니다.
오류를 깔끔하게 처리하기
찾는 값이 없으면 #N/A가 표시됩니다. 보고서에 그대로 나가면 보기 안 좋으니 IFERROR로 감쌉니다.
=IFERROR(VLOOKUP(A2, $D$2:$F$100, 2, 0), "-")값이 없으면 하이픈이 표시됩니다. 빈칸으로 두고 싶다면 큰따옴표 두 개를 연달아 쓰면 됩니다.
다만 처음부터 IFERROR를 씌우는 건 권하지 않습니다. 진짜 오류까지 가려버려서 나중에 문제를 찾기 어려워집니다. 수식이 제대로 작동하는지 확인한 뒤에 마무리 단계에서 씌우세요.
여러 열을 한꺼번에 가져오기
이름, 부서, 직급을 각각 가져오려면 열 번호만 바꿔서 세 개의 수식을 써야 합니다. 그런데 수식을 옆으로 복사하면 열 번호가 자동으로 바뀌지 않아 일일이 수정해야 하죠.
COLUMN 함수를 쓰면 해결됩니다.
=VLOOKUP($A2, $D$2:$G$100, COLUMN()-1, 0)COLUMN 함수는 현재 셀의 열 번호를 돌려줍니다. 수식을 오른쪽으로 복사하면 이 값이 자동으로 늘어나므로 열 번호도 함께 올라갑니다. 빼는 숫자는 시작 위치에 맞춰 조정하시면 됩니다.
VLOOKUP 대신 쓸 수 있는 것
XLOOKUP은 최신 버전에 추가된 함수로 VLOOKUP의 단점을 대부분 해결했습니다. 왼쪽 방향으로도 찾을 수 있고, 열 번호 대신 범위를 직접 지정합니다.
=XLOOKUP(A2, $D$2:$D$100, $E$2:$E$100, "없음")찾을값, 찾을범위, 가져올범위, 없을 때 표시할 값 순입니다. 훨씬 직관적이죠.
다만 구버전 엑셀에서는 작동하지 않습니다. 파일을 다른 사람과 주고받는다면 상대방 버전을 고려해야 합니다. 회사에서 구버전을 쓴다면 여전히 VLOOKUP이 안전한 선택입니다.
INDEX와 MATCH 조합은 구버전에서도 작동하면서 방향 제약이 없습니다. 조금 복잡하지만 익혀두면 어떤 환경에서도 쓸 수 있습니다.
VLOOKUP 함수 사용법을 표로 정리한 공식 문서가 더 궁금하다면 마이크로소프트 엑셀 공식 지원 센터에서 확인할 수 있습니다.