XLOOKUP - 찾아오기 종합 함수

 

XLOOKUP() 함수는 VLOOKUP, HLOOKUP, LOOKUP 함수의 기능을 다 할 수 있습니다.

 

1.   XLOOKUP  기본 형태는  LOOKUP 과 같다

 

XLOOKUP 함수의 프로토타입은

 

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

 

입니다.

 

옵션 인수들을 제외하면, XLOOKUP 함수는 LOOKUP 함수와 같습니다.

아래 데이터에 대하여, "홍길동4"의 "몸무게"값을 가져오는 식을 만들어봅시다.

=XLOOKUP( "홍길동4", $A$2:$A$7, $C$2:$C$7)
이렇게 하면 됩니다.

그런데, LOOKUP()으로 표현해도 인수가 똑같이 들어갑니다.
=LOOKUP( "홍길동4", $A$2:$A$7, $C$2:$C$7)

 

2.  LOOKUP, VLOOKUP, HLOOKUP은 모두 XLOOKUP으로 표현 가능

LOOKUP 함수는  VLOOKUP 함수와 HLOOKUP 함수를 표현할 수 있지만,
XLOOKUP 함수는 LOOKUP/ VLOOKUP / HLOOKUP 3가지를 모두 표현할 수 있습니다.


1)  VLOOKUP 대신, XLOOKUP

데이터가 이렇게 되어 있을때, "홍길동4"의 "몸무게"값을 가져오는 수식은,

 

VLOOKUP( "홍길동4", $A$2:$D$7, 3, FALSE) 이렇게 해도 되지만,

XLOOKUP( "홍길동4", $A$2:$A$7, $C$2:$C$7)  이렇게 해도 됩니다.


2)  HLOOKUP 대신, XLOOKUP  

데이터가 이렇게 되어 있을때, "홍길동4"의 "몸무게"값을 가져오는 수식은,

HLOOKUP( "홍길동4", $B$1:$G$4, 3, FALSE)   이렇게 해도 되지만
XLOOKUP( "홍길동4", $B$1:$G$1, $B$4:$G$4) 이렇게 해도 됩니다.


3)  LOOKUP 대신, XLOOKUP

데이터가 아래와 같이 되어 있다고 합시다.

"홍길동4"의 "몸무게"값을 가져오고 싶은데, key열의 왼쪽에 있다는 점을 주목하세요.

수식은, LOOKUP 으로 만들 수도 있고, XLOOKUP으로 만들 수도 있습니다. 

(VLOOKUP은 안됩니다.)

LOOKUP( "홍길동4", $D$2:$D$7, $C$2:$C$7)   이렇게 해도 되지만

XLOOKUP( "홍길동4", $D$2:$D$7, $C$2:$C$7)  이렇게 해도 됩니다.

 

3.  XLOOKUP 함수의 특별함

XLOOUP의 인수 목록을 다시 확인해 봅시다.

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

1번째 인수:  lookup_value : 찾을 값
2번째 인수:  lookup_array  :  찾을 값으로 검색할  1차원 데이터 범위
3번째 인수:  return_array   :   반환값을 포함되어 있는 1차원 데이터 범위

4번째 인수:  [if_not_found]  : 찾을 값이 lookup_array에 없을 때, #N/A 오류 대신 출력할 값
5번째 인수:  [match_mode]  :  
      0 : 정확히 일치 (기본값)
      -1 : 찾을 값이 없을 때, 찾을 값보다 작은 값 
      1 : 찾을 값이 없을 때, 찾을 값보다 큰 값 
       2 : 와일드카드 사용

6번째 인수:  [search_mode]  : 
       1 : 위에서 아래로 (기본값)
      -1 : 아래에서 위로
       2 : 오름차순 정렬된 범위에서 이진검색
      -2  : 내림차순 정렬된 범위에서 이진검색

 

앞의 3개 인수는 필수이고, 4번째, 5번째, 6번째 인수는 선택항목입니다.
선택항목이 xlookup의 특징입니다.


1)  lookup_value가 lookup_array에 없는 경우,  "#N/A" 에러값 이외의 출력값을 지정할 수 있습니다.

 

"홍길동7"을 검색한 경우, 에러 이외의 출력값을 지정하지 않은 경우와 지정한 경우입니다.

 

2)  [ match_mode ] 사용예

지정하지 않으면 0이 기본으로 지정됩니다. 0은 "정확히 일치"입니다.

-1 을 지정한 사례가 아래 사례입니다.

-1은 lookup_value를 찾지 못하면, lookup_value보다 작은 값 중에 가장 큰 값을 선택하라는 의미입니다.

아래 사례에서, "홍길동7"은 D2:D7에 없지만, match_mode가 -1로 지정되어 있으므로,
에러를 출력하지 않고, "홍길동7" 보다 작은 값 중에 가장 큰 값인 "홍길동6"을 선택하게 됩니다.
그래서, "홍길동6"과 같은 행에 있는 600이 반환됩니다.

 

 

3)  [ search_mode ] 사용예

지정하지 않으면 1이 기본으로 지정됩니다. 1은 "위에서 아래로 검색"한다는 의미입니다.

-1 을 지정하면, "아래에서 위로 검색"하라는 의미입니다.  어떤 경우에 의미가 있을까요?

아래 데이터를 보면, "홍길동1"이 중복됩니다. 
이 경우, "홍길동1"을 위에서 아래로 검색하면 위쪽에 있는  "홍길동1"이 선택됩니다.
아래에서 위로 검색하면 아래에 있는 "홍길동1"이 선택됩니다.

위쪽의 "홍길동1"이 선택되면, 400이 반환되고,
아래쪽의  "홍길동1"이 선택되면 600이 반환됩니다.

이와같이, 아래/위 어느쪽에서 검색을 시작하냐에 따라 결과값이 달라질 때 사용합니다.