안녕하세요, IT와 테크 지식을 공부하고 기록하는 루카(Luka)입니다.
엑셀은 비즈니스와 학업을 불문하고 데이터 분석의 필수 도구로 자리매김하고 있습니다. 특히 데이터 검색과 추출에 있어 VLOOKUP 함수는 오랜 시간 동안 우리의 든든한 동반자였죠. 하지만 시대가 변하고 데이터의 양과 복잡성이 증가하면서, VLOOKUP의 한계를 느끼는 분들이 많아졌습니다. 다행히도 마이크로소프트는 이러한 사용자들의 니즈를 반영하여 더욱 강력하고 유연한 XLOOKUP 함수와 혁신적인 동적 배열 수식을 선보였습니다.
오늘은 단순히 VLOOKUP을 대체하는 것을 넘어, XLOOKUP과 동적 배열 수식이 어떻게 엑셀 데이터 처리 방식을 혁신하고 우리의 업무 생산성을 극대화하는지, 그 활용 팁을 깊이 있게 다뤄보겠습니다.
VLOOKUP, 이제는 안녕? 그 한계를 돌아보다
VLOOKUP은 특정 값을 기준으로 테이블에서 관련 데이터를 찾아오는 데 유용했습니다. 하지만 다음과 같은 고질적인 문제점들을 안고 있었습니다.
- 왼쪽 방향 검색 불가: 항상
찾을_값이 있는 열이 테이블의 가장 왼쪽에 있어야 했죠. - 열 번호 의존성: 데이터를 추가하거나 삭제하면
열 번호를 일일이 수정해야 하는 번거로움이 있었습니다. - 정확한 일치 기본값 아님:
Range_lookup인수를FALSE로 지정하지 않으면 잘못된 값을 반환할 위험이 있었습니다. - 다중 기준 검색의 어려움: 여러 조건을 동시에 만족하는 데이터를 찾으려면
CONCATENATE등을 이용한 복잡한 보조 열이 필요했습니다. - 하나의 값만 반환: 여러 조건에 맞는 여러 데이터를 동시에 가져오기 어려웠습니다.
이러한 한계는 복잡한 데이터를 다룰 때마다 시간과 노력을 소모하게 만들었습니다. 이제, 이 모든 단점을 한 번에 해결해 줄 XLOOKUP을 만나볼 시간입니다.
XLOOKUP, VLOOKUP의 단점을 완벽하게 극복하다
XLOOKUP은 엑셀 365 및 웹 버전에서 사용 가능한 최신 함수로, VLOOKUP과 HLOOKUP은 물론 INDEX MATCH 조합까지 대체할 수 있는 강력한 기능들을 제공합니다.
XLOOKUP의 기본 구조와 강력한 장점
XLOOKUP 함수의 기본적인 구조는 다음과 같습니다.
=XLOOKUP(찾을_값, 찾을_범위, 반환할_범위, [찾지_못할_경우], [일치_모드], [검색_모드])
각 인수의 의미는 다음과 같습니다.
* 찾을_값: 찾으려는 값입니다.
* 찾을_범위: 찾을_값이 존재하는 범위입니다.
* 반환할_범위: 찾을_값과 일치하는 행/열에서 반환하려는 값이 있는 범위입니다. 이 인수가 VLOOKUP의 열 번호를 대체합니다.
* [찾지_못할_경우] (선택 사항): 찾을_값을 찾지 못했을 때 반환할 값입니다. IFERROR 함수를 대체합니다.
* [일치_모드] (선택 사항):
* 0: 정확히 일치 (기본값)
* -1: 정확히 일치 또는 다음으로 작은 항목
* 1: 정확히 일치 또는 다음으로 큰 항목
* 2: 와일드카드 문자 일치
* [검색_모드] (선택 사항):
* 1: 처음부터 마지막까지 검색 (기본값)
* -1: 마지막부터 처음까지 검색
* 2: 오름차순으로 정렬된 상태에서 이진 검색 (성능 향상)
* -2: 내림차순으로 정렬된 상태에서 이진 검색 (성능 향상)
XLOOKUP의 가장 큰 장점은 다음과 같습니다.
* 방향의 자유: 찾을_범위가 반환할_범위의 어느 쪽에 있든 상관없이 값을 찾을 수 있습니다.
* 열 번호의 종속성 해방: 반환할_범위를 직접 지정하므로 열 삽입/삭제에도 수식을 수정할 필요가 없습니다.
* 오류 처리 내장: 찾지_못할_경우 인수를 통해 IFERROR 없이 깔끔하게 오류를 처리할 수 있습니다.
* 기본값 정확한 일치: 일치_모드를 지정하지 않아도 기본적으로 정확히 일치하는 값을 찾아줍니다.
* 다양한 검색 모드: 역순 검색, 이진 검색 등 다양한 검색 시나리오에 대응할 수 있습니다.
XLOOKUP 활용 팁: 실제 예제로 배우기
팁 1: 양방향 검색의 자유
VLOOKUP으로는 불가능했던 왼쪽 방향 검색은 XLOOKUP의 가장 큰 장점 중 하나입니다.
예를 들어, '제품명'으로 '제품ID'를 찾아야 할 때, '제품명'이 '제품ID'보다 오른쪽에 있어도 문제없습니다.
=XLOOKUP(B2, D:D, A:A)
(B2셀의 제품명으로 D열에서 찾고, 해당 제품의 A열에 있는 제품ID 반환)
팁 2: '찾지 못할 경우' 오류 처리
더 이상 IFERROR를 중첩해서 사용할 필요가 없습니다.
예를 들어, 직원ID로 직원을 찾을 때, 해당 ID가 없으면 "정보 없음"이라고 표시하고 싶을 때.
=XLOOKUP(A2, C:C, D:D, "정보 없음")
(A2셀의 직원ID로 C열에서 찾고, 해당 직원의 D열에 있는 부서를 반환. 없으면 "정보 없음" 반환)
3: 와일드카드 문자를 이용한 부분 일치 검색
일치_모드를 2로 설정하면 와일드카드(*, ?)를 사용하여 부분 일치 검색이 가능합니다.
예를 들어, '서울'이 포함된 지점을 찾을 때.
=XLOOKUP("*"&B2&"*", D:D, C:C, "해당 지점 없음", 2)
(B2셀의 텍스트가 포함된 D열의 지점을 찾아 C열의 해당 지점 코드 반환. 없으면 "해당 지점 없음" 반환)
팁 4: 역순 검색으로 최신 데이터 찾기
같은 값을 가진 여러 항목 중 가장 최근에 추가되거나 마지막에 있는 값을 찾을 때 검색_모드를 -1로 활용합니다.
예를 들어, 특정 직원의 가장 최근 급여 변경 이력을 찾을 때.
=XLOOKUP(A2, C:C, D:D, "", , -1)
(A2셀의 직원ID로 C열에서 찾고, 해당 직원의 D열에 있는 급여를 반환. 가장 마지막에 일치하는 값을 찾음)
동적 배열 수식, 엑셀의 혁신을 이끌다
XLOOKUP이 개별 함수로서의 혁신이라면, 동적 배열 수식은 엑셀의 데이터 처리 패러다임을 바꾼 변화입니다. 하나의 수식을 입력하면 결과가 인접한 여러 셀에 자동으로 "스필(Spill)"되는 방식으로 작동합니다. 이는 이전에는 배열 수식(Ctrl+Shift+Enter)으로만 가능했거나 VBA를 통해서만 구현 가능했던 복잡한 작업을 함수 하나로 해결할 수 있게 해줍니다.
FILTER, UNIQUE, SORT, SORTBY, SEQUENCE, RANDARRAY 등이 대표적인 동적 배열 함수입니다.
XLOOKUP과 동적 배열 수식의 시너지
XLOOKUP 자체도 여러 열을 반환하는 등 동적 배열의 특성을 가지고 있지만, 다른 동적 배열 함수들과 결합될 때 그 진가가 발휘됩니다.
팁 1: XLOOKUP으로 여러 결과 한 번에 가져오기 (배열 반환)
반환할_범위를 단일 열이 아닌 여러 열로 지정하면, XLOOKUP이 해당 여러 열의 값을 한 번에 반환하여 스필됩니다.
=XLOOKUP(A2, C:C, D:F)
(A2셀의 직원ID로 C열에서 찾고, 해당 직원의 D, E, F열에 있는 부서, 직급, 전화번호를 한 번에 반환)
팁 2: FILTER 함수로 특정 조건 만족하는 모든 행 찾기
VLOOKUP으로는 꿈도 꾸지 못했던 기능입니다. FILTER 함수를 사용하면 특정 조건을 만족하는 모든 행을 쉽게 추출할 수 있습니다.
=FILTER(A:D, C:C="영업부")
(A열부터 D열까지의 데이터 중, C열의 부서가 "영업부"인 모든 행을 반환)
이것을 XLOOKUP과 결합하면 더욱 강력해집니다. 특정 부서에서 특정 직급을 가진 직원 정보만 필터링한 후, 다시 XLOOKUP으로 특정 값을 찾을 수도 있습니다.
팁 3: UNIQUE와 SORT로 중복 없이 정렬된 목록 만들기
데이터에서 고유한 값만을 추출하고 싶을 때 UNIQUE 함수를 사용합니다. 여기에 SORT 함수를 결합하면 중복 없이 정렬된 목록을 한 번에 만들 수 있습니다.
=SORT(UNIQUE(A2:A100))
(A2:A100 범위에서 중복 값을 제거하고, 오름차순으로 정렬된 고유 목록을 반환)
이 기능은 드롭다운 목록이나 보고서의 기준 데이터를 만들 때 매우 유용합니다.
팁 4: XLOOKUP과 FILTER의 고급 결합 (다중 조건 검색)
이제 XLOOKUP과 FILTER를 결합하여 VLOOKUP으로는 거의 불가능했던 다중 조건 검색을 해보겠습니다. 예를 들어, '영업부' 소속 '차장'의 '전화번호'를 찾고 싶을 때.
=XLOOKUP(B2, FILTER(C:C, (D:D="영업부")*(E:E="차장")), F:F)
(B2셀의 이름을 찾는데, FILTER 함수로 먼저 D열이 "영업부"이고 E열이 "차장"인 행들만 추출한 후, 그 안에서 C열의 이름을 찾아 F열의 전화번호를 반환)
여기서 (D:D="영업부")*(E:E="차장")는 다중 조건을 나타내는 배열 수식 표현입니다. 각 조건이 TRUE(1) 또는 FALSE(0)로 평가되어 곱셈을 통해 모든 조건이 TRUE인 경우만 1이 되고, FILTER 함수가 이를 이용해 원하는 행을 걸러냅니다.
VLOOKUP에서 XLOOKUP으로의 전환, 그리고 동적 배열 마스터하기
지금까지 VLOOKUP의 한계를 넘어 XLOOKUP의 강력함과 동적 배열 수식의 혁신적인 활용법을 알아보았습니다. 엑셀은 더 이상 정적인 스프레드시트 프로그램이 아닌, 동적인 데이터 분석 플랫폼으로 진화하고 있습니다.
처음에는 새로운 함수와 개념에 익숙해지는 데 시간이 걸릴 수 있지만, 한 번 익숙해지면 기존에 복잡하고 번거로웠던 엑셀 작업이 얼마나 효율적이고 간단해지는지 경험하게 되실 겁니다. 특히 데이터의 양이 많고 복잡한 작업을 자주 하시는 분들이라면 XLOOKUP과 동적 배열 수식은 선택이 아닌 필수가 될 것입니다.
오늘 알려드린 팁들을 바탕으로 여러분의 엑셀 스킬을 한 단계 업그레이드하시고, 데이터 분석의 새로운 지평을 열어보시길 바랍니다. 궁금한 점이나 추가적으로 다뤄줬으면 하는 주제가 있다면 언제든지 댓글로 남겨주세요. 다음 포스팅에서 또 유익한 정보로 찾아뵙겠습니다!