엑셀로 데이터를 합칠 때마다 찾으려는 값이 왼쪽 열에 있어서 표를 잘라내 붙여넣은 적 있으신가요? 수식 끝에 매번 숫자 0이나 FALSE를 덧붙이고, 중간에 열 하나 추가했다가 전체 수식이 깨져 당황한 경험도 많으실 겁니다. 이제 복잡한 함수를 겹쳐 쓰지 않고도 깔끔하게 데이터를 찾아오는 방법이 있습니다.
한눈에 보기
- 기본 수식:
=XLOOKUP(찾을값, 찾을범위, 반환범위)형태로 3개 필수 인수만 입력하면 즉시 검색- 방향 제한 해소: 기준 열의 왼쪽에 위치한 데이터도 열 이동 없이 바로 조회 가능
- 자체 오류 처리: 4번째 인수에 대체 텍스트를 적어
#N/A오류를IFERROR없이 방지- 지원 환경: Microsoft 365 및 Excel 2021 이상에서 지원 (하위 버전은
#NAME?발생)
기존 VLOOKUP 함수, 무엇이 가장 불편했을까?
그동안 실무에서 가장 많이 쓰인 조회 함수는 VLOOKUP이었습니다. 하지만 구조적인 한계 때문에 작업 시간이 늘어나는 일이 잦았습니다.
가장 큰 문제는 단방향 검색입니다. VLOOKUP은 기준값이 무조건 데이터 범위의 첫 번째(가장 왼쪽) 열에 있어야 하며, 그보다 왼쪽에 있는 데이터는 가져오지 못합니다. 마치 책장의 맨 왼쪽 라벨만 보고 오른쪽 칸의 책만 꺼낼 수 있는 서가와 같습니다.
또한 가져올 열의 위치를 ‘3’ 같은 고정 숫자로 지정하기 때문에, 표 중간에 새로운 열을 넣거나 삭제하면 엉뚱한 열의 값을 가져오는 문제가 발생했습니다. 일치 옵션 기본값도 유사 일치로 되어 있어, 끝에 정확히 일치를 뜻하는 0이나 FALSE를 빠뜨리면 잘못된 결과를 불러오기 일쑤였습니다.
필수 인수 3개로 끝내는 XLOOKUP 기본 구문
XLOOKUP은 복잡했던 조회 방식을 획기적으로 단순화했습니다. 전체 6개 인수 중 앞의 3개만 넣으면 즉시 작동합니다.
기본 수식 구조는 =XLOOKUP(찾을값, 찾을범위, 반환범위)입니다. 찾는 범위와 가져올 범위를 각각 독립적으로 지정하므로, 가져올 데이터가 기준 열의 왼쪽에 있든 오른쪽에 있든 위치에 상관없이 값을 가져옵니다.
| 인수 순서 | 인수 명칭 | 필수 여부 | 설명 |
|---|---|---|---|
| 1 | lookup_value | 필수 | 찾으려는 기준값 |
| 2 | lookup_array | 필수 | 기준값을 검색할 범위 |
| 3 | return_array | 필수 | 결과로 가져올 데이터 범위 |
| 4 | [if_not_found] | 선택 | 일치하는 값이 없을 때 띄울 텍스트 |
| 5 | [match_mode] | 선택 | 일치 조건 (0: 정확히 일치 기본값) |
| 6 | [search_mode] | 선택 | 검색 방향 (1: 정방향, -1: 역방향) |
반환 범위(return_array)에 여러 열(예: B2:D9)을 한 번에 지정하면, 하나의 셀에만 수식을 입력해도 오른쪽 셀들로 결과가 자동 확장되어 채워지는 동적 배열 기능도 지원합니다.
오류 처리부터 역방향 검색까지, 선택 인수 활용법
XLOOKUP은 별도의 함수를 중첩하지 않아도 자체적으로 오류를 제어하고 검색 방향을 바꿀 수 있습니다.
기존에는 찾는 데이터가 없을 때 #N/A 오류가 뜨는 것을 막기 위해 IFERROR나 IFNA 함수로 감싸야 했습니다. XLOOKUP은 4번째 인수인 [if_not_found]에 "데이터 없음"처럼 원하는 문구를 적기만 하면 깔끔하게 오류 표기를 대체합니다.
일치 모드(match_mode)의 기본값이 ‘0(정확히 일치)’으로 설정되어 있어 뒤쪽 옵션을 일일이 적을 필요가 없습니다. 또한 6번째 인수 [search_mode]에 -1을 입력하면 데이터 범위의 맨 아래(마지막 행)부터 위로 올라가는 역방향 검색을 실행하여 가장 최근에 등록된 데이터를 손쉽게 찾을 수 있습니다.
내 엑셀에서도 쓸 수 있을까? 지원 버전과 오류 코드
XLOOKUP은 최신 엑셀 환경에 맞춰 도입된 함수이므로 사용 전 프로그램 버전을 확인해야 합니다.
현재 XLOOKUP은 Microsoft 365 구독 버전과 Excel 2021 이상 영구 버전에서 지원됩니다. 이전 버전인 Excel 2016이나 Excel 2019에서 해당 수식이 포함된 파일을 열면 함수를 인식하지 못해 #NAME? 오류가 나타납니다.
| 오류 코드 | 주요 발생 원인 | 해결 방법 |
|---|---|---|
#NAME? | Excel 2016/2019 등 하위 버전에서 문서를 열었을 때 | 지원 버전(M365, Excel 2021 이상) 사용 |
#VALUE! | 찾을 범위와 반환할 범위의 행/열 개수가 다를 때 | 두 범위의 크기(행 수)를 동일하게 맞춤 |
#N/A | 일치하는 값이 없는데 4번째 인수를 생략했을 때 | 4번째 인수에 대체 텍스트 입력 |
지금 사용하는 엑셀 버전을 확인하고, 번거로웠던 VLOOKUP 수식을 필수 인수 3개의 단순한 XLOOKUP으로 바꿔보세요.
답글 남기기