엑셀 XLOOKUP 함수 사용법 및 예제 (VLOOKUP과 차이점 및 장단점 비교)

엑셀의 함수 중 데이터 분석 및 정리를 할 때 가장 많이 사용되는 함수가 바로 VLOOKUP 함수입니다. VLOOKUP함수는 엑셀을 사용하시는 분들이라면 무조건 한번 이상은 사용을 해봤을 함수인데요. 하지만 VLOOKUP 함수와 비슷하고 더 유용한 XLOOKUP함수도 있는걸 알고 계신가요? XLOOKUP함수가 2019년 버전 엑셀에서부터 도입되었는데요. XLOOKUP 함수는 VLOOKUP 함수가 할 수 있는 모든 작업을 수행할 수 있으며, 심지어 훨씬 더 많은 작업을 수행할 수 있습니다. 저와 함께 XLOOKUP과 VLOOKUP의 장단점을 살펴 보실까요?

 

 

엑셀 XLOOKUP 함수 vs VLOOKUP함수 장단점과 차이 비교

엑셀 XLOOKUP 함수 사용법 썸네일

먼저 VLOOKUP함수 간단히 정리하기

VLOOKUP 함수는 엑셀에서 가장 많이 사용된 함수라고 감히 말할 수 있을정도로 사용도가 높고 유용한 엑셀 함수입니다. VLOOKUP이라는 이름에서 알 수 있듯이 세로형(vertical) 데이터에서 작동하도록 설계되었는데요. 조회 값이 주어지면 VLOOKUP은 테이블의 첫 번째 열을 검색하고 지정된 다른 열의 동일한 행에서 해당 값을 반환합니다. 

엑셀 VLOOKUP 함수 사용법 정리 예제

VLOOKUP 함수 구문

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

VLOOKUP함수구문은 위와 같은데요. lookup_value에는 '검색할 값', table_array에는 검색할 테이블의 범위, col_index_num은 값을 가져올 컬럼의 위치, range_lookup은 FALSE일 경우 정확히 일치하는 것만 찾기, TRUE일 경우 대략 일치하는 것도 찾기입니다. 

 

VLOOKUP 함수의 장점

• 직관적인 조작: VLOOKUP은 테이블의 첫 번째 열을 스캔합니다. 일치하는 항목을 찾으면 테이블을 가로질러 지정된 열 번호로 이동하여 같은 행에 있는 값을 검색합니다. 적은 수의 입력으로도 쉽고 직관적인 조회가 가능합니다.
• 사용성: 많은 사람들이 공통적으로 쓰는 함수이다 보니 엑셀 파일을 공유하며 협업할 경우 VLOOKUP함수를 사용하면 재사용성이 좋고 이해하기도 좋다는 이점을 누릴 수 있습니다.
• 간단한 구성: 첫 번째 열에 조회 값이 있는 데이터 테이블이 있다면 VLOOKUP을 사용하는 데 필요한 거의 모든 것이 갖추어져 있는 것입니다. 조회 값, 테이블 주소, 열 번호만 있으면 VLOOKUP을 사용할 수 있습니다.

 

 

VLOOKUP 함수의 단점

하지만 이렇게 널리 사용되는 VLOOKUP함수도 단점이 있습니다. 

• range_lookup이 비어있을 경우 TRUE로 자동세팅 되는 것이 위험할 수 있음: VLOOKUP 함수의 range_lookup 부분은 optional 항목으로 기재하지 않아도 되는데, 빈칸으로 둘 경우 자동으로 TRUE로 인식합니다. TRUE는 대략적인 일치 항목도 반환하기 때문에, 사용자가 의도치 않은 결과를 보여줄 수도 있습니다. (정확한 매칭만 찾기를 원했는데, 사용이 미숙하여 range_lookup을 비워두었고, 오류가 나지 않아 정상적으로 동작한다고 착각할 수 있음)
• 세로로 구성된 데이터만 검색 가능: VLOOKUP은 세로로만 검색할 수 있으므로 가로로 구성된 데이터에서 조회를 수행하려면 HLOOKUP 또는 INDEX 및 MATCH와 같은 다른 수식을 사용해야 합니다.
• 첫 번째 열의 값만 조회합니다: VLOOKUP에서 검색할 컬럼은 첫 번째 열에 꼭 있어야 합니다. 
• 대략적인 일치가 제대로 수행되지 않을 수 있음: range_lookup을 TRUE로 설정하거나 인수를 완전히 생략하여 대략적인 일치를 수행하도록 VLOOKUP 함수를 사용하는 경우, 올바르게 작동하려면 데이터가 오름차순으로 정렬되어야 합니다. 하지만 오름차순으로 정렬되어 있지 않더라도 VLOOKUP은 값을 반환하므로 의도치 않은 정확하지 않은 데이터가 반환될 수 있습니다.
• 오류 제어 기능이 없음: 조회에 실패했을 때 대체 값을 제공할 수 있는 방법이 없습니다. 조회에 실패하면 VLOOKUP은 #N/A 오류를 반환합니다. 이 오류를 좀 더 디테일하게 처리하려면 IFERROR 또는 IFNA와 같은 다른 함수를 추가적으로 사용해야 합니다.

VLOOKUP 함수의 대체제 - XLOOKUP 함수 알아보기

XLOOKUP은 VLOOKUP이 하는 기능처럼 '검색' 및 '조회' 기능을 하는 함수입니다. 엑셀 2019버전부터 사용이 가능해졌으며 위에서 본 VLOOKUP 함수 사용시의 여러가지 한계를 해결하도록 설계되었습니다. XLOOKUP 함수의 구문과 사용 방법을 함께 살펴보겠습니다.

 

XLOOKUP 함수 구문

XLOOKUP(lookup_value,lookup_array,return_array,[if_not_found],[match_mode],[search_mode])

• lookup value - 검색할 값
• lookup_array - 검색할 값을 찾을 범위
• return_array - 반환할 값의 범위
• if_not_found - [optional] 아무것도 못찾았을 때 반환할 값
• match_mode - [optional] - 일치 모드(0 = 정확히 일치, -1 = 정확히 일치하거나 다음으로 작은 항목, 1 = 정확히 일치하거나 다음으로 큰 항목, 2 = 와일드카드 문자 일치)
• search_mode - [optional] - 검색 모드(1 = 오름차순 검색, -1 = 내림차순 검색, 2 = 이진 검색(오름차순), -2 = 이진 검색(내림차순))

구문만 봐서는 VLOOKUP과 유사한 부분도, 차이점도 보이시죠? 확실한건 예제를 봐야 이해가 잘 되실거예요. 아래 XLOOKUP 함수들의 다양한 활용 예제를 함께 살펴보겠습니다.

 

 

XLOOKUP 함수 활용 예제

XLOOKUP 함수 활용 예제1 - VLOOKUP을 활용하는 케이스에 XLOOKUP 함수를 적용해보기

아래 예제는 위에서 VLOOKUP함수 다룰 때의 예제와 동일한 조회 및 검색을 해본 경우입니다. XLOOKUP 함수를 사용할 때는 VLOOKUP함수를 사용할 때와는 다르게 함수인자를 설정해줘야하는데요. 아래와 같이 두번째 함수인자는 lookup_array 검색할 값을 찾을 범위이기 때문에 B열만 잡아줘야합니다. 그리고 세번째 함수인자는 VLOOKUP함수 사용시에는 컬럼의 열 순서 번호를 넣어줬던 것과는 달리, XLOOKUP 함수에서는 검색반환할 범위를 지정해줘야하기 때문에 E열 해당부분만 잡아주었습니다. 그리고 XLOOKUP 함수는 기본적으로 '정확한 일치'로 설정을 하기 때문에 그 뒤의 함수 인자들은 생략을 해주었습니다.

엑셀 XLOOKUP 함수 사용법 정리 예제

 

XLOOKUP 함수 활용 예제2 - 대략적으로 일치하는 값 검색하여 반환하기

XLOOKUP 함수에서는 대략적으로 일치하는 값 검색할 때 어떤 값을 반환할지 기준을 정할 수가 있습니다. 아래 예시처럼 정확히 일치하거나 다음으로 작은 항목(-1), 정확히 일치하거나 다음으로 큰 항목(1) 중에 어떤걸로 명시를 하냐에 따라 반환되는 결과값이 달라지게 됩니다. 

엑셀 XLOOKUP 함수 사용법 정리 예제

XLOOKUP 함수 활용 예제3 - 여러개의 값을 반환해야할 때 함수 사용 한번만으로 가능

이번에 보여드릴 예제는 XLOOKUP함수의 큰 장점인데요. 아래와 같이 ID를 검색해서 일치하는 경우 First Name, Last Name, Email 총 3개의 컬럼을 모두 채워야할 때 VLOOKUP을 사용했다면 함수를 세번 각각 입력을 해줘야 했죠? 그런데 XLOOKUP 함수는 H6셀에 한번만 함수를 넣어주면, 한번에 세개의 값을 모두 반환하여 알아서 넣어줍니다. 아래 예시는 H6셀에만 XLOOKUP함수를 입력했고, I6, J6셀에는 함수를 입력하지 않았습니다. 이렇게 여러개의 값을 찾아서 반환해야할 때 VLOOKUP보다 XLOOKUP함수를 사용하면 유용합니다.

엑셀 XLOOKUP 함수 사용법 정리 예제3

XLOOKUP 함수 활용 예제4 - 양방향 검색 가능

XLOOKUP 함수는 양방향 검색이 가능하다는 게 정말 큰 장점입니다. 사용방법은 아래와 같이 하나의 XLOOKUP을 다른 XLOOKUP 안에 중첩하여 사용할 수 있습니다. 아래 예제에서 내부 XLOOKUP은 전체 행('유리'의 모든 값)을 검색하고, 이를 반환 배열로 외부 XLOOKUP에 전달합니다. 외부 XLOOKUP은 적절한 그룹(B)을 찾아 해당 값(17.25)을 최종 결과로 반환합니다.

엑셀 XLOOKUP 함수 사용법 정리 예제4

XLOOKUP 함수 활용 예제5 - #N/A 대신 특정 문구로 출력하기

XLOOKUP은 아래와 같이 결과값을 찾지 못했을 때 #N/A 대신 특정 문구로 출력하도록 간단히 설정할 수가 있습니다.

엑셀 XLOOKUP 함수 사용법 정리 예제

XLOOKUP 함수 활용 예제6 - 여러가지 조건으로 검색하기

XLOOKUP을 활용하기 가장 좋은 방법 중 하나는 BOOLEAN 조건으로 여러가지 조건을 만들어 검색하는 방법입니다. 예제를 보면 더 이해하기가 쉽기 때문에 같이 보도록 하겠습니다. 

=XLOOKUP(1,(조건1)*(조건2)*(조건3),data)

여러가지 조건으로 검색을 할 때는 위와 같이 XLOOKUP 함수 구문을 적어주시면 됩니다. 각 조건들은 괄호로 묶어 *로 연결해 주세요.

엑셀 XLOOKUP 함수 사용법 정리 예제6

위의 예제는 Account가 X로 시작하고 Region이 East인 경우에 해당하는 행을 반환한 경우입니다. 이와 같이 여러개의 조건을 만족하는 테이블의 행을 XLOOKUP 함수를 이용하여 한번에 가져올 수 있습니다. 

 

이렇게 VLOOKUP함수와 XLOOKUP함수의 특성과 각각의 장단점을 같이 비교해보았는데요. XLOOKUP함수 정말 유용해보이지 않으신가요? 지금까지 VLOOKUP함수만 사용해보셨다면 앞으로 업무에 XLOOKUP함수를 대신 사용해보시면서 사용법을 익히시고 또 더 확장된 기능에도 점차 익숙해지면서 업무 능률을 높여보시기 바랍니다. 

 

(같이 보면 좋은 글)

 

엑셀 단축키 모음표 250개 (윈도우, 맥) - 한번에 마스터

 

예제와 함께 보는 엑셀 함수 정리 - 한번에 마스터!