VLOOKUP보다 사용하기 편한 XLOOKUP 함수

이번 포스팅에서는 VLOOKUP보다 사용하기 편한 XLOOKUP 함수에 대해서 설명하겠습니다.

VLOOKUP함수는 찾고자 하는 값은 오른쪽에 위치해야 한다든지 사용상의 제한이 있습니다.(오피스팁 링크) 하지만 XLOOKUP의 경우 이러한 제한점이 없기에 보다 편리하게 사용할 수 있습니다.

 

다만 XLOOKUP은 2016, 2019 버전에서는 사용할 수 없고, 2021, office365 버전에서만 사용할 수 있습니다. XLOOKUP 함수를 지원하지 않는 엑셀 하위 버전에서는 XLOOKUP 함수로 구현한 값은 확인할 수 있지만, 함수식은 수정할 수 없습니다.

VLOOKUP보다 사용하기 편한 XLOOKUP 기본

VLOOKUP과 동일하게 테이블 또는 행별 범위에서 항목을 찾는 함수입니다. 차이점은 위에서 설명드린 바와 같이 XLOOKUP을 사용하면 반환 열이 어느 쪽에 있는지와 관계없이 한 열에서 검색어를 찾고 다른 열의 동일한 행에서 결과를 반환할 수 있습니다.

 

=XLOOKUP(기준값, 기준 데이터 범위, 가져올 열 데이터)

• 기준값 : 중복되지 않는 고윳값(중복값이 있으면 가장 먼저 찾은 값)
• 기준 데이터 범위 : 기준값이 포함된 열 데이터
• 추출 데이터 범위 : 가져올 열 데이터

 

예를 들어, 사번이 A00006인 직원의 '이름', '직급', '입사일', '월급여'를 구한다고 할 경우 D14셀에 '=XLOOKUP($C$14,$A$2:$A$11,B2:B11)'을 입력합니다.

• $C$14 : 기준이 되는 사번을 지정하였습니다. 기준은 변하면 안 되기에 절대참조를 지정하였습니다.
• $A$2:$A$11 : 사번이 위치한 범위를 선택합니다.
• B2:B11 : 찾고자 하는 값 '이름'이 위치한 범위를 선택하였습니다. 상대참조를 한 이유는 만약 찾고자 하는 값들이 연속적으로 되어 있다면 자동 채우기로 빠르게 값을 채울 수 있기 때문입니다. 지금은 값이 떨어져 있어 자동 채우기를 할 수 없고 추출 데이터범위를 수정해 주어야 합니다.

VLOOKUP보다 사용하기 편한 XLOOKUP 응용

이 함수는 앞서 설명드린 3가지 인수 외 추가로 인수를 추가할 수 있습니다만, 많이 사용하지 않기에 간략하게만 설명드립니다.

 

=XLOOKUP(기준값, 기준 데이터 범위, 가져올 열 데이터, if_not_found, match_mode, search_mode)

• if_not_found : 일치하는 값이 없을 때 지정한 텍스트를 반환합니다.
• match_mode : 일치 유형을 지정합니다.
  - 0 : 정확히 일치, 찾을 수 없을 경우 #N/A를 반환합니다.
  - -1 : 정확히 일치, 찾을 수 없을 경우 다음 작은 항목을 반환합니다.
  - 1 : 정확히 일치, 찾을 수 없을 경우 다음 큰 항목을 반환합니다.
• search_mode : 사용할 검색 모드를 지정합니다.
  - 1 : 첫 번째 항목부터 검색을 수행합니다.
  - -1 : 마지막 항목부터 역방향 검색을 수행합니다.
  - 2(or -2) : 오름차순(내림차순)으로 정렬되는 lookup_array 사용하는 이진 검색을 수행합니다. 정렬하지 않으면 잘못된 결과가 반환됩니다.

예를 들어 아래와 같이 응용하여 사용할 수 있습니다.

 

1. 해당 셀에 해당하는 값이 없는 경우 별도의 텍스트를 표시할 수 있습니다.

D14셀에 '=XLOOKUP($C$14,$A$2:$A$11,B2:B11,"해당값이 없음")을 입력하면 범위 내 사번이 없을 경우 '해당값이 없음'을 반환합니다.

 

2. 월급여에 따른 인센티브율을 찾을 수 있고, 일치하는 값이 없을 경우에는 조건에 맞는 가장 적합한 값을 찾을 수 있습니다.

E2셀에 '=XLOOKUP(D2,B2:B6,C2:C6,0,1,1)'을 입력하면 30%를 구할 수 있습니다.

 

• 0 : if_not_found 인수를 찾을 수 없는 경우 0을 반환합니다. 다만, 여기서는 조건이 추가되어 있어 0을 반환하지 않았습니다.
• 1 : match_mode 인수를 1로 설정하였습니다. 즉, 함수가 정확히 일치하는 항목을 찾고, 찾을 수 없는 경우 다음으로 큰 항목을 반환합니다. 즉, 3,750,000원의 값이 없기에 다음을 큰 값 4,000,000원을 반환하였습니다.
• 1 : search_mode 인수를 1로 설정하였습니다. 즉, 함수가 첫 번째 항목에서 마지막 항목으로 검색합니다. 첫 번째 항목인 30%가 반환되었습니다.

이상 VLOOKUP보다 사용하기 편한 XLOOKUP 함수에 대해서 설명하였습니다. 실제 VLOOKUP, HLOOKUP보다 사용하기는 편하지만, 버전에 따라 사용을 못 할 수 있는 치명적인 단점이 있습니다. 그렇기에 VLOOKUP함수를 먼저 완벽하게 이해하신 다음에 XLOOKUP함수를 사용하시기를 추천합니다.

감사합니다.