엑셀에서 데이터를 찾는 작업은 단순해 보이지만, 실제 업무에서는 표 구조가 조금만 꼬여도 시간 소모가 크게 늘어납니다. 오늘은 직장인이 가장 자주 쓰는 VLOOKUP과, 그 단점을 크게 보완한 XLOOKUP을 실무 예제로 비교해 보겠습니다.
처음에는 “조회 함수만 알면 된다”라고 생각하기 쉬운데, 데이터가 커질수록 조회 이전에 표를 정리하는 습관이 더 중요해집니다.
엑셀 VLOOKUP 함수의 한계와 기본 원리
VLOOKUP은 지정한 값을 표의 가장 왼쪽 열에서 찾은 뒤, 같은 행에 있는 다른 값을 반환하는 엑셀의 대표적인 검색 함수입니다. 제품 코드를 입력하면 제품명이나 단가를 자동으로 불러오는 식의 작업에 아주 유용합니다.
하지만 VLOOKUP은 치명적인 단점이 몇 가지 있습니다.
첫째, 찾고자 하는 기준 값이 반드시 표의 맨 왼쪽 첫 번째 열에 있어야 합니다. 기준 값보다 왼쪽에 있는 데이터는 불러올 수 없어서 매번 데이터 열의 위치를 수정해야 하는 번거로움이 있습니다. 둘째, 범위 지정 시 열 번호를 숫자로 직접 입력해야 하는데, 중간에 새로운 열이 추가되거나 삭제되면 수식이 엉켜버려 #REF! 오류가 발생하기 쉽습니다. 이러한 구조적 한계 때문에 복잡한 실무 데이터를 다룰 때는 항상 긴장하며 수식을 확인해야 했습니다.
아래 이미지는 VLOOKUP 함수의 오류를 보여줍니다. 실제 실무에서 다운로드하는 로우(Raw) 데이터는 우리가 원하는 순서대로 예쁘게 정렬되어 있지 않습니다. ‘거래처코드’를 기준으로 ‘담당자명’을 찾으려 했지만 에러가 발생했습니다. VLOOKUP은 무조건 검색 범위의 ‘가장 왼쪽 열’에서만 기준값을 찾을 수 있기 때문에, 이 표처럼 기준값이 중간(C열)에 있고 반환값이 왼쪽(B열)에 있으면 함수가 작동하지 않고 멈춰버립니다.
XLOOKUP 함수가 필수 함수인 이유
이러한 VLOOKUP의 단점을 완벽하게 해결하고 나온 것이 바로 XLOOKUP 함수입니다. 오피스 365 버전 이후부터 지원되는 이 함수는 방향의 제한 없이 어떤 위치에 있는 데이터든 자유롭게 찾아올 수 있습니다.
가장 큰 장점은 검색할 범위와 반환할 범위를 따로 지정한다는 것입니다. 기준 값이 표의 중간이나 맨 오른쪽에 있어도 왼쪽의 데이터를 쉽게 불러올 수 있습니다. 또한, 열 번호를 세어 숫자로 입력할 필요가 없기 때문에 중간에 열이 추가되거나 삭제되어도 수식이 깨지지 않습니다.
추가로 VLOOKUP에서 오류가 났을 때 빈칸이나 특정 문자로 처리하기 위해 IFERROR 함수를 중첩해서 써야 했던 반면, XLOOKUP은 함수 자체에 ‘찾지 못했을 때 반환할 값’을 지정하는 인수가 포함되어 있어 수식이 훨씬 깔끔해집니다.
XLOOKUP은 “찾을 범위”와 “반환할 범위”를 따로 지정할 수 있어서, 표 구조를 바꾸지 않고도 원하는 값을 바로 가져올 수 있습니다. 제가 실무에서 특히 유용하다고 느낀 포인트는 아래 3가지입니다.
-
열 위치에 덜 민감합니다: 기준값이 오른쪽에 있어도, 왼쪽 데이터를 반환합니다.
-
수식이 단순합니다: 열 번호를 세어 넣는 방식이 아니라서 유지보수가 편합니다.
-
오류 처리가 깔끔합니다: 값이 없을 때 표시할 문구를 함수 안에서 지정할 수 있습니다.
하지만 XLOOKUP을 사용하면 원본 데이터를 전혀 건드리지 않고도 수식 하나로 문제를 해결할 수 있습니다. 아래 화면의 수식을 보면, 첫 번째로 검색할 값(F2)을 지정하고, 두 번째로 검색할 범위(C열), 세 번째로 반환할 범위(B열)를 각각 따로따로 지정해 주었습니다.
그 결과, 표의 구조를 뒤집거나 열 번호를 숫자로 세어 넣을 필요 없이 “박민수”라는 결과값을 깔끔하게 찾아냅니다. 게다가 수식 끝에 "결과없음"이라는 인수를 하나 추가해 주면, C-1009처럼 데이터에 없는 코드를 검색했을 때 #N/A 같은 보기 싫은 에러 창 대신 우리가 지정한 문구로 예쁘게 마감 처리까지 해줍니다.
실무 예제: 거래처 코드로 담당자명 찾기
실제 업무에서 거래처별 매출 데이터를 취합한다고 가정해 보겠습니다. 거래처 코드는 C열에 있고, 우리가 찾고자 하는 담당자 이름은 A열에 있는 상황입니다.
XLOOKUP은 아래처럼 작성합니다.
=XLOOKUP(찾을 값, 찾을 범위, 반환할 범위, “결과없음”)
예를 들어, F2에 “검색할 거래처 코드”가 있고 원본 표의 거래처코드가 C열, 담당자명이 A열이라면 다음처럼 입력하시면 됩니다.
=XLOOKUP(F2, C2:C6, A2:A6, “결과없음”)
만약 신규 거래처라 아직 명단에 없는 코드라면, 오류 메시지 대신 “결과없음”이라는 텍스트가 출력되어 이후 데이터 정리 작업이 훨씬 수월해집니다.
VLOOKUP을 사용한다면 A열과 C열의 위치를 바꾸거나 데이터를 복사해서 새로운 표를 만들어야 합니다. 하지만 XLOOKUP을 사용하면 원본 데이터를 전혀 건드리지 않고도 수식 하나로 끝낼 수 있습니다.
수식 작성 시 =XLOOKUP(찾을 값, 찾을 범위, 반환할 범위, “결과없음”) 형태로 입력하면 됩니다. 만약 신규 거래처라 아직 명단에 없는 코드라면, 오류 메시지 대신 “결과없음”이라는 텍스트가 깔끔하게 출력되어 이후 데이터 정리 작업이 훨씬 수월해집니다.
참고로 XLOOKUP을 입력했는데 #NAME?이 뜬다면, 엑셀이 해당 함수를 지원하지 않는 버전일 가능성이 큽니다.
업무 효율을 높이는 엑셀 자동화의 시작
결론적으로 아직 구버전 엑셀을 사용하는 환경이 아니라면, 이제는 VLOOKUP 대신 XLOOKUP을 기본 함수로 사용하는 것을 적극 권장합니다. 단순한 열 이동이나 수식 오류를 고치는 데 쓰던 자잘한 시간들을 모으면 하루 업무 시간을 30분 이상 단축할 수 있습니다.
데이터를 빠르고 정확하게 찾는 것은 엑셀 활용의 가장 기본이자 핵심입니다. XLOOKUP에 익숙해지셨다면, 다음 단계로는 매일 반복되는 이 데이터 검색 및 취합 과정을 매크로나 자동화 툴로 넘기는 것을 고민해 볼 차례입니다. 다음 글에서는 코딩을 전혀 몰라도 누구나 쉽게 따라 할 수 있는 엑셀 매크로 기초 활용법에 대해 다루어 보겠습니다.