XLOOKUP 함수 사용 방법 (Excel 2021~)

포스트 목차.

• 01. XLOOKUP 함수 소개
• 02. XLOOKUP 함수 인자
• 03. XLOOKUP 함수 활용

 

본 포스트에서는 Excel 2021, Office 365 버전에서 사용가능한 XLOOKUP 함수를 소개한다. XLOOKUP 함수는 기존의 LOOKUP 함수의 기능을 강화하여 VLOOKUP/HLOOKUP 기능도 수행할 수 있는 함수이다. 

 

01. XLOOKUP 함수 소개

XLOOKUP 함수는 특정 영역에서 값을 검색하여, 검색된 값 또는 그 옆에 있는 값을 가져오는 역할을 한다.

XLOOKUP과 유사한 기능을 수행하는 함수들에 대한 포스트는 다음과 같다.

 

(1) LOOKUP

 

Excel [지식인 해결 시리즈] 001. lookup 함수를 이용한 양도세 구간 구하기

 

(2) VLOOKUP

 

Excel 매크로 (VBA) - 018. Vlookup으로 데이터 조회하기 (주식 종목 코드 조회)

 

(3) MATCH, INDEX 함수

MATCH, INDEX 함수

 

 

 

02. XLOOKUP 함수 인자

XLOOLUP 함수의 인자는 다음과 같다.

- Loolkup_value: 검색할 값

- Lookup_array: 검색할 값이 있는 배열

- Return_array: 검색할 값을 찾은 후, 반환될 값이 있는 배열 (찾은 값과 같은 인덱스의 값이 반환된다.)

- If_not_found: 검색할 값을 찾지 못한 경우에 반환할 값, 해당 값을 설정하지 않으면, 값이 없을 때 NA에러가 발생한다.

- Match_mode: lookup_array의 일치 여부를 결정할 방법을 지정한다.

   0: 정확히 일치할 경우, 찾을 수 없는 경우 #N/A 반환

   -1: 찾을 수 없는 경우 다음 작은 항목을 반환

   1: 찾을 수 없는 겨우 다음 큰 항목을 반환

   2: * ? ~ 특별한 의미가 없는 와일드카드 일치

- Search_mode:  검색의 방향을 지정한다.

  1: 첫 번째 항목부터 정방향 검색

  -1: 마지막 항목부터 역방향 검색

   2: lookup_array가 오름차순으로 정렬되어 있다고 가정하고 이진검색 수행 (정렬안되어 있는 경우 결과 신뢰 못함)

   -2: lookup_array가 내림차순으로 정렬되어 있다고 가정하고 이진검색 수행 (정렬안되어 있는 경우 결과 신뢰 못함)   

 

03. XLOOKUP 함수 활용

(1) LOOKUP 처럼 사용

다음과 같은 데이터가 있을 때, 업소명으로 소재지 연락처를 찾는 수식과 소재지 연락처로 업소명을 찾는 수식을 만들어보자.

 

G2와 J2에 수식을 넣어서 F2와 I2에 입력된 값을 기준으로 찾고자 하는 데이터를 가져오게 만들어보자.

 

 - 업소명으로 소재지 연락처 찾기

=XLOOKUP(F2,$A$2:$A$51,$B$2:$B$51,,0,1)

 

 

 - 소재지 연락처로 업소명 찾기

=XLOOKUP(I2,$B$2:$B$51,$A$2:$A$51,,0,1)

 

업소명(F2), 소재지 연락처(I2)에 값을 넣으면 제대로 대응되는 값을 찾아오는 것을 확인할 수 있다.

 

(2) 값이 없을 때 처리

이번에는 값이 없을 때의 처리에 대해 알아보자.

업소명, 소재지 연락처에 아무 정보도 입력하지 않으면, 값이 없기 때문에 #N/A가 출력된다.

다음과 같이 수식의 4번째 인자인 If_not_found에 "없음"을 넣어보자

=XLOOKUP(F2,$A$2:$A$51,$B$2:$B$51,"없음",0,1)

=XLOOKUP(I2,$B$2:$B$51,$A$2:$A$51,"없음",0,1)

 

매치되는 값이 없을 경우, If_not_found로 지정한 값이 출력되는 것을 확인할 수 있다.

 

(3) 정확한 일치와 근사값 비교

 

지금까지는 다섯번째 인자인 Match_mode를 0으로 설정하여, 정확하게 일치하는 것만 찾아냈고, 없을 경우에 4번째 인자인 If_not_found로 설정한 값을 반환하는 수식을 사용했었다. 

 

이번에는 근사값 일치 (Match_mode -1 또는 1)을 이용해보자.

=XLOOKUP(F2,$A$2:$A$51,$B$2:$B$51,"없음",-1,1)

 

EXR 찜질방 대신 EXR 찜잘방을 입력해보면, 데이터가 없기 때문에 없음이 출력되어야하지만, 우리는 Match_Mode를 -1로 설정하여 그 다음으로 가까운 GCV 울산대왕암의 소재지 연락처 값이 반환되는 것을 확인할 수 있다.