엑셀 XLOOKUP 함수는 범위에서 일치하는 값을 찾아 원하는 데이터를 반환하는 함수입니다.
=XLOOKUP(찾을값, 찾을범위, 반환범위, [N/A값], [일치옵션], [검색방향])
- 찾을 범위와 반환 범위의 크기는 반드시 동일해야 합니다. 크기가 다를 경우, #VALUE! 오류가 출력됩니다.
- 0 : [기본값] 정확히 일치 검색, 값이 없을 경우 #N/A 오류를 반환합니다.
- -1 : 작은 값 검색 (정확히 일치하는 값이 없을 시, 다음으로 작은 항목을 검색합니다.)
- 1 : 큰 값 검색 (정확히 일치하는 값이 없을 시, 다음으로 큰 항목을 검색합니다.)
- 2 : 와일드카드 검색 (다양한 부분 일치 검색을 수행합니다.)
- 1 : [기본값] 위에서 아래로 검색
- -1 : 아래에서 위로 검색
- 2 : 오름차순 정렬된 범위에서 이진 검색
- -2 : 내림차순 정렬된 범위에서 이진 검색
(이진 검색은 대량의 데이터를 더욱 빠르게 검색합니다.)
엑셀 XLOOKUP 함수는 범위에서 조회값과 일치하는 항목을 찾아 반환 범위의 데이터를 출력하는 함수입니다. VLOOKUP 함수의 개선된 버전으로 더 유연하고 강력한 검색 기능을 제공합니다.
VLOOKUP 함수보다 더욱 강력한 기능을 제공하지만 XLOOKUP 함수가 제공되지 않는 이전 버전에서 실행 시 #NAME? 오류가 반환됩니다. 따라서 여러 사람과 공유하는 파일에서는 주의해서 사용합니다.
XLOOKUP 함수의 주요 특징은 다음과 같습니다.
- 왼쪽/오른쪽 양방향 검색 가능
- VLOOKUP 함수는 오른쪽으로만 조회 가능한 반면, XLOOKUP 함수는 방향에 상관없이 검색 가능합니다. - 와일드카드를 이용한 부분일치 검색 가능
- 와일드카드를 활용해 특정단어를 포함하는 값 검색이 가능합니다. - 마지막 일치 항목 검색 가능
- XLOOKUP 함수는 범위의 아래에서부터 검색할 수 있습니다. - 가로 방향 검색 가능
- XLOOKUP 함수는 세로/가로 방향 검색을 모두 지원합니다. - 결과값으로 배열 반환 가능
- XLOOKUP 함수는 결과로 배열을 반환해서 다른 함수와 다양한 방법으로 응용할 수 있습니다.
XLOOKUP 함수의 실제 사용법과 다양한 사례는 아래 영상 강의를 참고하세요!👇
XLOOKUP 함수는 출력범위 위치에 상관없이 편리하게 검색할 수 있습니다.
XLOOKUP 함수는 출력 범위의 위쪽 또는 아래쪽에서부터 양방향으로 검색할 수 있습니다. (=마지막 항목 검색 가능)
XLOOKUP 함수는 와일드카드를 사용한 다양한 부분일치 검색을 제공합니다.
XLOOKUP 함수는 가로로 작성된 범위에서도 사용할 수 있습니다.
XLOOKUP 함수는 결과값으로 '배열'을 반환합니다. 이를 다른 함수와 함께 활용하면 다양한 고급 검색을 수행할 수 있습니다.
=XLOOKUP("OPD003",제품번호,제품명:납품처) / / OPD003 제품의 제품명부터 납품처까지 데이터를 배열로 반환합니다. =TEXTJOIN("&",,XLOOKUP("OPD003",제품번호,제품명:납품처)) / / OPD003 제품 정보를 "&" 기호로 병합합니다. / / "참깨라면 & 봉지라면 & 햇살공장"이 반환됩니다.
XLOOKUP함수는 검색에 꼭 필요한 항목인 찾을범위와 반환범위만 인수로 사용합니다. 따라서 VLOOKUP 함수보다 더욱 빠르게 동작합니다.
특히 오름차순/내림차순으로 정렬된 범위에서 '이진 검색'을 수행할 경우, 기존 VLOOKUP 함수보다 10배 이상 빠르게 검색할 수 있으며, 이는 데이터의 양이 많을 수록 더욱 큰 성능 차이를 체감할 수 있습니다.
VLOOKUP 함수의 일치옵션 기본값은 '유사일치' 입니다. 하지만 실무에서는 일반적으로 '정확히 일치'를 사용하기 때문에 4개의 인수를 입력해야 했습니다.
=VLOOKUP(찾을값,범위,열번호,0) / / VLOOKUP 함수를 '정확히 일치'로 사용하려면 4개의 인수를 입력해야 합니다.
반면, XLOOKUP 함수의 일치옵션 기본값은 '정확히 일치' 이므로, 3개 인수만 입력하여 편리하게 사용할 수 있습니다.
=XLOOKUP(찾을값,찾을범위,출력범위) / / XLOOKUP 함수의 일치옵션 기본값은 '정확히 일치' 이므로, 3개의 인수만 입력합니다.
XLOOKUP 함수의 결과로 배열을 반환할 경우, 출력 범위에 다른 값이 있으면 #SPILL!(#분산!) 오류를 출력합니다.
찾을범위와 반환범위의 크기는 동일해야 합니다. 만약 두 범위의 크기가 다르면 #VALUE! 오류가 반환됩니다.
XLOOKUP 함수의 일치옵션으로 -1 (작은값 검색)을 수행하고, 찾을 값보다 작은 값이 없을 경우 #N/A 오류를 반환합니다..
XLOOKUP 함수는 M365 및 엑셀 2021 이후 버전에서 제공됩니다. 함수가 제공되지 않은 버전에서는 #NAME? 오류를 반환하므로, 이전 버전 사용자와 파일을 공유할 때는 호환성에 주의합니다.
조건을 만족하는 여러 결과를 동시에 출력하려면 FILTER 함수를 사용합니다. 엑셀 2019 이전 버전 사용자는 VLOOKUP 여러개 항목 검색 공식을 활용합니다.