Home > 엑셀 함수 > 찾기 및 참조영역 함수 > VLOOKUP의 단점을 해결한 XLOOKUP함수로 표에서 값찾기

VLOOKUP의 단점을 해결한 XLOOKUP함수로 표에서 값찾기

  • by

표에서 값을 찾을 때 가장 많이 사용하는 함수가 VLOOKUP인데, 이 함수는 결정적인  단점이 있다.

아래 그림과 같이 VLOOKUP함수의 첫번째 인수에 해당하는 “키값(품목코드)”이 반드시 “찾는 값(품명,단가)”의 왼쪽에 있어야 한다.

=VLOOKUP(“A003”,품목정보!$A$2:$C$11,2,FALSE)

엑셀함수 XLOOKUP

만약, 아래와 같이  표에서 “키값(품목코드)”이 찾는 값의 오른쪽에 있다면 VLOOKUP함수로는 값을 찾을 수 없다.

엑셀함수 XLOOKUP

이런 경우 INDEX, MATCH함수를 조합해서 값을 찾던지 해야 했다.  그 외 표가 가로로 되어 있으면 VLOOKUP으로 안되고 HLOOKUP을 이용해야 하는 불편한 점도 있었다.

VLOOKUP함수는 다음 링크를 참고한다.
[ 엑셀에서 값을 찾을 때 VLOOKUP, HLOOKUP 함수 사용하기 ]

엑셀 2019버전 또는 Office365 버전부터 제공하는 XLOOKUP함수를 이용하면 이런 단점을 한번에 해결할 수 있다.

구문(Syntax)

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

범위(lookup_array)따라가면서 키값(lookup_value)를 찾은 다음, 반환할 범위(return_array)의 값을 반환한다

인수 :

  • lookup_value : 범위에서 원하는 값을 찾기 위한 키값
  • lookup_array : 값을 찾을 범위
  • return_array: 찾은 값을 반환할 범위
  • if_not_found : (생략가능) 값을 찾지 못했을 때 표시할 텍스트
  • match_mode : (생략가능) 일치하는 키값을 찾을 것인지 근사값을 찾을 것인지 결정

    0 정확히 일치하는 값, 찾을 수 없는 경우 #N/A 를 반환
    -1 정확히 일치 하는 값, 찾을 수 없는 경우 다음으로 작은 항목을 반환
    1 정확히 일치 하는 값, 찾을 수 없는 경우 다음으로 증가 하는 항목을 반환
    2 와일드 카드로 검색
  • search_mode : (생략가능)검색모드

    1 첫 번째 항목부터 검색(기본값)
    -1 마지막 항목부터 역방향 검색
    2 오름차순 으로 정렬 되는 lookup_array에 의존 하는 이진 검색 , 정렬 되지 않은 경우 잘못 된 결과가 반환됨
    -2 내림차순 으로 정렬 되는 lookup_array에 의존 하는 이진 검색, 정렬 되지 않은 경우 잘못 된 결과가 반환됨

 

사용예 :

찾고자 하는 값이 들어 있는 표는 다음과 같이 3가지가 있다고 가정하고 몇 가지 사용예를 알아보자.

 

표 1 : 찾는 키값이 표의 가장 왼쪽에 있는 경우

엑셀함수 XLOOKUP

 

표 2 : 찾는 키값이 표의 가장 왼쪽이 아닌 곳에 있는 경우

엑셀 XLOOKUP함수

 

표 3 : 표가 가로로 구성된 경우

엑셀 XLOOKUP함수

 

1) 찾는 키값이 표의 가장 왼쪽이 아닌 곳에 있는 경우(표2)에 품명 가져오기

엑셀 XLOOKUP함수

“표2″는 키값이 표의 가장 왼쪽이 아닌 곳에 있으므로 다음과 같이 XLOOKUP함수를 이용해서 값을 찾는다.

C22셀에 다음 수식을 입력한다.

=XLOOKUP(B22,품목정보!$H$4:$H$13,품목정보!$F$4:$F$13,”**찾는 값 없음**”)

엑셀 XLOOKUP함수

수식이 정상적으로 입력되었으면 C22셀에 품명 “모나미 볼펜”이 구해진다.

C22셀의 수식을 복사해서 C23셀부터 C26셀까지 붙여 넣으면 다음과 같이 결과가 표시된다.

엑셀 XLOOKUP함수

 

위 수식이 어떻게 작동되었는지 다음 그림을 보고 이해해 보자.

엑셀 XLOOKUP함수

정리해 보면 위 수식은 다음의 순서로 실행된다.

  1. B22셀의 품목코드”A003″에 해당하는 값을 C22셀에 가져와야 하는데
  2. “품목정보”시트에서 품목코드가 있는 범위 (품목정보!$H$4:$H$13)를 찾아 A003이 있으면
  3. 품명이 있는 범위(품목정보!$F$4:$F$13)의 같은 행에서 “모나미 볼펜”을 가져온다.
  4. 만약에 찾는 값이 없으면 “**찾는 값 없음**”이라고 표시한다.

 

2) 찾는 키값이 표의 가장 왼쪽에 있는 경우(표1)에 품명 가져오기

엑셀함수 XLOOKUP

“표1″은 찾을 키값이 찾는 값의 왼쪽에 있으므로  VLOOKUP함수를 이용하여 표에서 값을 가져올 수도 있다.

C6셀에 다음 수식을 입력하고, C6셀의 수식을 복사해서 C7셀부터 C10셀까지 붙여 넣으면 다음과 같이 결과가 표시된다.

=VLOOKUP(B6,품목정보!$B$4:$D$13,2,FALSE)

엑셀 XLOOKUP함수

 

XLOOKUP함수를 이용하여 품목코드에 해당하는 품명을 “표1″에서 가져오기 위해서는 다음과 같이 수식을 입력한다.

=XLOOKUP(B14,품목정보!$B$4:$B$13,품목정보!$C$4:$C$13,”**찾는 값 없음**”)

엑셀 XLOOKUP함수

“표1″과 같은 경우에는 찾는 값이 없을 때 별도의 “결과 텍스트를 지정”할 수 있는 점 이외에는 VLOOKUP이나 XLOOKUP이나 차이가 없다.

 

3) 표2에서 품명 가져오기(한번에 여러 열의 값을 가져오기)

“표2″와 같이 찾는 값이 F,G열에 연속해서 있을 경우에는

엑셀 XLOOKUP함수

C30셀에 XLOOKUP함수의 세번째 인수(return_array)에 F,G열을 범위(배열)로 한번에  지정하도록 수식을 입력하면

=XLOOKUP(B30,품목정보!$H$4:$H$13,품목정보!$F$4:$G$13,”**찾는 값 없음**”)

아래와 같이 C, D열에 결과값을 한번에 가져온다.

엑셀 XLOOKUP함수

※ 주의 : 값을 범위로 가져오도록 지정했는데 가져올 범위에 이미 값이 있으면(D열에 이미 값이 있음) 다음과 같이 #SPILL! 오류가 발생하므로 값을 가져올 범위가 비어 있어야 한다.

엑셀 XLOOKUP함수

 

4) 가로로 된 표3에서 품명 가져오기

아래와 같이 표가 가로로 되어 있으면  이전에는 HLOOKUP을 사용했는데 이제는 XLOOKUP함수 하나로 세로로 된 표, 가로로 된 표 상관없이 값을 가져올 수 있다.

엑셀 XLOOKUP함수

C46셀에 다음 수식을 입력한다.

=XLOOKUP(B46,품목정보!$K$3:$T$3,품목정보!$K$4:$T$4,”**찾는 값 없음**”)

엑셀 XLOOKUP함수

위 수식이 어떻게 작동되었는지 다음 그림을 보고 이해해 보자.

엑셀 XLOOKUP함수

정리해 보면 위 수식은 다음의 순서로 실행된다.

  1. B46셀의 품목코드”A003″에 해당하는 값을 C46셀에 가져와야 하는데
  2. “품목정보”시트에서 품목코드가 있는 범위 (품목정보!$K$3:$T$3)를 찾아 A003이 있으면
  3. 품명이 있는 범위(품목정보!$K$4:$T$4)의 같은 열에서 “모나미 볼펜”을 가져온다.
  4. 만약에 찾는 값이 없으면 “**찾는 값 없음**”이라고 표시한다.

 

[ 엑셀 찾기 및 참조영역 함수 목록 ]

댓글 남기기

이메일은 공개되지 않습니다.