엑셀 XLOOKUP 함수 사용법 완벽 가이드 (VLOOKUP과의 차이점)

엑셀에서 데이터를 찾을 때 마주하는 답답함

직장인이나 학생이라면 엑셀을 사용하면서 특정한 값을 찾아야 하는 상황을 매일 마주하게 됩니다. 수천 줄이 넘어가는 데이터 속에서 사원번호로 이름을 찾거나 상품코드로 가격을 불러와야 할 때 우리는 보통 VLOOKUP 함수를 떠올립니다. 하지만 VLOOKUP은 사용할 때마다 뭔가 아쉽고 제약이 많다는 느낌을 지우기 어렵습니다. 찾으려는 값이 반드시 기준 열의 왼쪽에 있어야 하고 열 번호를 일일이 세어야 하는 번거로움이 대표적입니다.

이런 엑셀 사용자의 오랜 갈증을 한 번에 해결해 주기 위해 등장한 강력한 도구가 바로 XLOOKUP 함수입니다. 마이크로소프트가 엑셀의 오랜 제약들을 허물고 새롭게 선보인 이 함수는 기존 찾기 함수의 패러다임을 완전히 바꿨습니다. VLOOKUP이 가지고 있던 수많은 단점을 보완했을 뿐만 아니라 훨씬 더 직관적이고 강력한 기능을 제공하기 때문에 엑셀 작업을 하는 사람이라면 반드시 익혀야 할 필수 기술이 되었습니다.

엑셀 XLOOKUP 함수가 무엇이며 왜 혁신적인가

XLOOKUP 함수는 지정한 범위나 배열에서 특정 값을 찾아 그에 대응하는 다른 범주의 값을 반환해 주는 엑셀의 최신 검색 함수입니다. 기존의 VLOOKUP이나 HLOOKUP이 가진 한계를 극복하기 위해 만들어졌으며 검색의 방향, 일치 여부, 찾지 못했을 때의 처리 방식 등을 모두 하나의 함수로 해결할 수 있는 놀라운 범용성을 자랑합니다.

이 함수가 혁신적이라고 평가받는 가장 큰 이유는 바로 유연성입니다. 기존에는 데이터를 찾기 위해 머리를 싸매며 인덱스 함수와 매치 함수를 복잡하게 조합하거나 데이터의 열 순서를 강제로 바꾸어야 했습니다. 하지만 XLOOKUP은 찾을 범위와 결과가 있는 범위를 각각 독립적으로 지정할 수 있기 때문에 데이터 원본을 수정할 필요가 전혀 없습니다. 함수 하나로 왼쪽 오른쪽 방향에 구애받지 않고 데이터를 자유자재로 불러올 수 있게 된 것입니다.

XLOOKUP 함수의 기본 구조와 사용 방법

새로운 함수를 배울 때 가장 먼저 해야 할 일은 그 생김새와 구성 요소를 이해하는 것입니다. XLOOKUP 함수는 생각보다 단순한 구조를 가지고 있으며 몇 가지 핵심 인수만 알고 있으면 곧바로 실무에 적용할 수 있습니다.

이 함수의 기본 형태는 다음과 같습니다. =XLOOKUP(찾을값, 찾을범위, 반환할범위, [없을때값], [일치옵션], [검색방향])

각 인수가 의미하는 바를 자세히 살펴보면 다음과 같습니다.

• 찾을값: 내가 데이터에서 찾고자 하는 대상입니다. 예를 들어 특정 사원의 이름이나 상품의 바코드 번호가 됩니다.

• 찾을범위: 찾을값이 포함되어 있는 한 줄 또는 한 칸의 데이터 영역입니다.
• 반환할범위: 실제로 내가 가져오고 싶은 결과값이 들어있는 영역입니다.
• 없을때값: 일치하는 데이터를 찾지 못했을 때 화면에 표시할 대체 텍스트나 숫자입니다. 이 인수를 지정하지 않으면 값이 없을 때 오류가 발생하므로 빈칸이나 0 등으로 처리할 때 매우 유용합니다.
• 일치옵션: 정확하게 일치하는 값을 찾을 것인지 아니면 유사한 값을 찾을 것인지를 결정합니다. 0은 정확히 일치, -1은 작거나 같은 값, 1은 크거나 같은 값 등을 의미합니다.
• 검색방향: 데이터를 위에서 아래로 찾을 것인지 아니면 아래에서 위로 거꾸로 찾을 것인지를 지정합니다.

실제 예시를 통해 이해해 보겠습니다. A열에 사원번호가 있고 C열에 부서명이 있을 때 사원번호 1005번의 부서를 찾고 싶다면 =XLOOKUP(1005, A2:A100, C2:C100)이라고 입력하면 끝납니다. VLOOKUP처럼 열 번호가 몇 번째인지 세어볼 필요도 없고 기준 열이 왼쪽에 있어야 한다는 압박감도 없습니다.

VLOOKUP과 XLOOKUP의 결정적인 차이점 비교

여전히 많은 사람들이 익숙하다는 이유로 VLOOKUP을 고집하지만 두 함수의 차이점을 알게 된다면 오늘부터라도 XLOOKUP으로 갈아타게 될 것입니다. 두 함수가 실무에서 어떤 차이를 보이는지 구체적으로 비교해 보겠습니다.

검색 방향의 자유로움

VLOOKUP은 반드시 가장 왼쪽에 있는 열을 기준으로 삼아 오른쪽의 데이터를 가져와야 합니다. 만약 가져와야 할 데이터가 기준 열보다 왼쪽에 있다면 VLOOKUP으로는 불가능하여 원본 표의 열 순서를 바꾸거나 INDEX와 MATCH를 써야 했습니다. 반면 XLOOKUP은 찾을 범위와 반환할 범위를 사용자가 각각 지정하기 때문에 왼쪽에서 오른쪽은 물론이고 오른쪽에서 왼쪽으로의 검색도 아무런 제약 없이 가능합니다.

열 번호 입력의 번거로움 해소

VLOOKUP 함수는 세 번째 인수로 찾고자 하는 데이터가 몇 번째 열에 있는지 숫자로 입력해야 합니다. 데이터가 20개 열로 이루어진 거대한 표라면 사용자가 직접 눈으로 열 번호를 세어야 하며 중간에 새로운 열이 하나라도 삽입되면 번호가 밀려서 오류가 발생하거나 엉뚱한 값을 가져오게 됩니다. XLOOKUP은 열 번호 대신 결과가 있는 구체적인 범위를 지정하므로 중간에 열이 추가되거나 삭제되어도 수식이 깨지지 않고 안전하게 작동합니다.

정확한 일치 기본 설정과 오류 처리

VLOOKUP을 쓸 때 가장 귀찮은 점 중 하나는 정확히 일치하는 값을 찾으려면 마지막 인수로 0이나 FALSE를 꼭 적어주어야 한다는 것입니다. 이를 깜빡하면 엉뚱한 유사 값을 가져와서 큰 실수로 이어지기도 합니다. 하지만 XLOOKUP은 기본적으로 정확한 일치를 기본값으로 작동합니다. 또한 데이터를 찾지 못했을 때 발생하는 #N/A 오류를 해결하기 위해 별도의 IFERROR 함수를 중첩해서 쓸 필요 없이 네 번째 인수에 찾지 못했을 때 출력할 문구를 바로 넣을 수 있어 수식이 훨씬 간결해집니다.

실생활과 업무에서 XLOOKUP을 활용하는 다양한 방법

XLOOKUP 함수는 단순한 값 찾기를 넘어서 다양한 실무 상황에서 업무 생산성을 극대화하는 데 활용될 수 있습니다. 일상적인 업무에서 마주치는 상황별 활용 팁을 살펴보겠습니다.

오른쪽에 있는 코드로 왼쪽의 이름을 찾아올 때

인사관리 데이터를 다루다 보면 사원 이름은 왼쪽에 있고 사원번호나 이메일 주소는 오른쪽에 있는 경우가 많습니다. 이때 이메일 주소만 가지고 사원의 이름을 찾아내야 한다면 VLOOKUP으로는 불가능하지만 XLOOKUP을 사용하면 이메일 범위를 찾을 범위로 지정하고 이름 범위를 반환할 범위로 지정하여 간단하게 해결할 수 있습니다.

조건이 두 개 이상일 때 다중 조건 검색

실무에서는 부서도 같고 직급도 같은 사람의 실적을 찾아야 하는 것처럼 여러 가지 조건을 동시에 만족하는 데이터를 찾아야 하는 경우가 많습니다. XLOOKUP을 활용하면 앤드 연산자를 사용하여 여러 조건을 아주 간단하게 처리할 수 있습니다. 예를 들어 부서가 영업부이고 직급이 과장인 사람을 찾고 싶다면 =XLOOKUP(1&1, (부서범위=”영업부”)*(직급범위=”과장”), 결과범위)와 같은 방식으로 다중 조건 검색을 완벽하게 구현할 수 있습니다.

가장 마지막 데이터를 가져오는 역방향 검색

시간순으로 쌓이는 데이터의 경우 동일한 항목이 여러 번 기록될 때가 있습니다. 재고 관리 대장에서 가장 최근에 입고된 단가를 가져와야 할 때 VLOOKUP은 항상 위에서부터 첫 번째로 만나는 값만 가져오기 때문에 곤란해집니다. 이때 XLOOKUP의 마지막 검색 방향 인수를 조절하여 아래에서 위로 검색하도록 설정하면 가장 마지막에 입력된 최신 데이터를 정확하게 불러올 수 있습니다.

XLOOKUP 사용 시 자주 하는 실수와 해결책

강력한 함수인 만큼 사용할 때 몇 가지 주의해야 할 점들이 있습니다. 엑셀 초보자들이 자주 범하는 실수를 미리 알고 대비하면 오류 없는 깔끔한 문서를 만들 수 있습니다.

가장 흔한 실수는 찾을 범위와 반환할 범위의 행 개수를 다르게 지정하는 것입니다. 예를 들어 찾을 범위는 2행부터 100행까지 잡았는데 반환할 범위는 2행부터 50행까지만 지정한다면 엑셀은 범위를 제대로 인식하지 못하고 #VALUE! 오류를 뱉어냅니다. 두 범위의 크기는 항상 완벽하게 일치해야 한다는 점을 기억해야 합니다.

데이터의 형식이 서로 일치하지 않는 경우에도 값을 찾지 못하는 상황이 발생합니다. 찾으려는 값은 숫자 형태인데 표 안의 데이터는 텍스트 형태로 저장되어 있다면 엑셀은 서로 다른 값으로 인식합니다. 이럴 때는 데이터의 서식을 통일해 주거나 값 뒤에 빈 문자열을 더하는 등의 방식으로 데이터 형식을 맞춰주어야 정상적으로 검색이 이루어집니다.

엑셀 버전 호환성과 XLOOKUP 사용을 위한 조언

XLOOKUP은 마이크로소프트의 최신 엑셀 환경에서 강력한 성능을 자랑하는 기능입니다. 하지만 회사나 학교에서 사용하는 엑셀 버전이 구형인 경우 함수를 지원하지 않아 #NAME? 이라는 오류가 발생할 수 있습니다.

XLOOKUP 함수는 오피스 365 구독 버전이나 엑셀 2021 이후 버전에서만 기본적으로 지원됩니다. 따라서 만약 구버전을 사용하는 동료나 거래처와 파일을 공유해야 하는 상황이라면 수식이 깨지지 않는지 미리 확인하는 것이 좋습니다. 파일을 공유할 때는 수식 결과값만 남기기 위해 값 복사를 활용하는 것도 좋은 방법입니다.

처음에는 기존에 쓰던 VLOOKUP이 더 익숙해서 XLOOKUP이 낯설게 느껴질 수 있습니다. 하지만 한두 번 직접 수식을 입력해 보면 VLOOKUP이 얼마나 불편했는지 깨닫게 될 것입니다. 방향에 구애받지 않고 열 번호를 세지 않아도 되는 편리함은 엑셀 작업을 하는 동안 여러분의 소중한 시간을 크게 아껴줄 것입니다.