엑셀 함수 > 찾기 및 참조영역 함수 > XLOOKUP 함수 - 표에서 값찾기(VLOOKUP 단점 해결)

XLOOKUP 함수 - 표에서 값찾기(VLOOKUP 단점 해결)

XLOOKUP 함수는 기존 VLOOKUP, HLOOKUP 함수의 몇 가지 단점(찾는 키값이 항상 1열에 있어야 하고, 가로로 된 표는 VLOOKUP 대신 HLOOKUP 사용 등)을 해결하고 사용하기 쉽게 개선된 엑셀의 새로운 찾기 함수입니다.

예를 들어 VLOOKUP 함수로 값을 찾을 때  아래 그림과 같이 찾는 '키값(상품코드)'이 반드시 표의 첫 번째 열에 있어야 합니다.

VLOOKUP 함수는 항상 키값이 1열에 있어야 함

 

만약, 찾는 키 값이 첫 번째 열이 아니라 다른 열에 있다면 VLOOKUP 함수로는 값을 찾을 수 없습니다. 이런 경우 INDEX, MATCH 함수를 조합해서 값을 찾든지 해야 하는데  XLOOKUP 함수를 이용하면 이런 단점을 한 번에 해결할 수 있습니다.

XLOOKUP함수는 Microsoft 365 또는 엑셀 2021 이상 버전에서만 사용할 수 있습니다. 

INDEX, MATCH 함수를 조합해서 값을 찾는 방법은 다음 링크를 참고하세요.
[ VLOOKUP으로 안될때 INDEX, MATCH 함수 사용하기 ]

 

구문(Syntax)

XLOOKUP

=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 lookup_value와 정확히 일치하는 것을 찾음, 찾을 수 없는 경우 #N/A 를 반환
    -1 lookup_value와 정확히 일치하거나  작은 것을 찾음
    1 lookup_value와 정확히 일치하거나  큰 것을 찾음
    2 와일드 카드 검색(?는 1개 문자, *는 여러문자)
  • search_mode : (생략가능)검색 유형

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

 

1. 기본 사용법

1) 상품코드로 상품명 찾기

아래와 같이 상품 정보가 있다고 할 때 상품코드에 해당하는 상품명을 찾아 보겠습니다.

상품코드 'A005'에 해당하는 상품명 '오피스 수정테이프'를 찾으려면

XLOOKUP 함수 - 상품코드로 상품명 찾기

[C19] 셀에 다음과 같이 수식을 입력합니다.

=XLOOKUP(B18,B6:B12,C6:C12) 

다음 순서로 표에서 상품코드 'A005'에 해당하는 상품명 '오피스 수정테이프'를 찾아 줍니다.

① [B6:B12] 범위(두 번째 인수)를 수직으로 내려가면서 상품코드 'A005'를 찾은 후에
② [C6:C12] 범위(세 번째 인수)에서 같은 행의 '오피스 수정테이프'를 찾는다.

XLOOKUP 함수 - 상품코드로 상품명 찾기

 

 

2) 값을 찾을 키값이 1열이 아닌 다른 열에 있을 때 찾기

다음과 같이 값을 찾을 키값(상품코드)가 1열이 아닌 다른 열(3열)에 있으면 VLOOKUP 함수로는 상품코드에 해당하는 상품명이나 단가를 찾을 수 없지만 XLOOKUP 함수를 이용하면 찾을 수 있습니다.

XLOOKUP 함수 - 값을 찾을 키값이 1열이 아닌 다른 열에 있을 때 찾기

[C17] 셀에 다음 수식을 입력합니다.

=XLOOKUP(B17,D6:D12,B6:B12)

VLOOKUP 함수와 달리 키 값을 찾을 범위와 값을 반환할 범위를 따로 지정하기 때문에 키 값은 1열에 있지 않아도 다음과 같이 정상적으로 값을 찾을 수 있습니다.

XLOOKUP 함수 - 값을 찾을 키값이 1열이 아닌 다른 열에 있을 때 찾기

※ 중요 – 키값을 찾을 범위와 값을 반환할 범위를 따로 지정할 수 있는 점이 XLOOKUP 함수의 핵심입니다.
VLOOKUP 함수는 키값을 찾을 범위만 지정 가능하고 그 범위 안에서 몇 번째 열에서 값을 반환할 것인가를 결정했기 때문에 키값의 위치가 항상 1열에 있어야 한다는 제약이 있었습니다.

 

 

3) 상품코드로 단가 찾기

이번에는 상품코드 'A005'에 해당하는 단가를 찾아보겠습니다.

XLOOKUP 함수 - 상품코드로 단가 찾기

[C22] 셀에 다음과 같이 수식을 입력합니다.

=XLOOKUP(B22,B6:B12,D6:D12)

다음 순서로 표에서 상품코드 'A005'에 해당하는 단가 20,000원을 찾아 줍니다.

① [B6:B12] 범위(두 번째 인수)를 수직으로 내려가면서 상품코드 'A005'를 찾은 후에
② [D6:D12] 범위(세 번째 인수)에서 같은 행의 20,00원을 찾는다.

XLOOKUP 함수 - 상품코드로 단가 찾기

 

 

4) 찾는 값이 없을 때 처리

XLOOKUP 함수로 값을 찾을 때 찾는 값이 없다면 #N/A오류가 표시됩니다. #N/A오류는 '찾는 값이 없다'는 것을 뜻합니다.

다음과 같이 함수의 세 번째 인수를 쓰면 찾는 값이 없을 때 좀 더 알아보기 쉽도록 별도의 '결과 표시 텍스트를 지정'할 수 있습니다.

XLOOKUP 함수 - 찾는 값이 없을 때 처리

=XLOOKUP(B26,B6:B12,C6:C12,"**찾는 값 없음**")

 

XLOOKUP 함수 - 찾는 값이 없을 때 처리

상품코드 'X005'는 표에 없어서 상품명을 찾지 못하므로 '**찾는 값 없음**'이 표시됩니다.

 

 

5) 표에서 상품명과 단가를 한 번에 찾기

XLOOKUP 함수의 세 번째 인수 return_array(값을 반환할 범위)를 여러 열을 지정하면 한 번에 여러 개의 결과를 가져올 수 있습니다.

수식에서 세 번째 인수를 다음과 같이 C 열만 입력하면  상품명만 가져오지만
=XLOOKUP(B30,B6:B12,C6:C12) => 상품명만 가져옴

XLOOKUP 함수 사용법

XLOOKUP 함수 사용법

 

수식에서 세 번째 인수를 다음과 같이 C, D 열을 입력하면  상품명과 단가를 동시에 가져옵니다.
=XLOOKUP(B30,B6:B12,C6:D12)

XLOOKUP 함수 - 표에서 상품명과 단가를 한 번에 찾기

XLOOKUP 함수 - 표에서 상품명과 단가를 한 번에 찾기

 

※ 주의 : 값을 여러 개 가져올 때 가져올 범위에 이미 값이 있으면(D열에 이미 값이 있음) #SPILL! 오류가 발생하므로 값을 가져올 범위가 비어 있어야 합니다.

XLOOKUP 함수 - #SPILL! 오류

 

 

2. 실무 예제

지금까지는 함수의 원리 이해를 위한 간단한 예를 살펴 보았는데 이번에는 실무 예제를 다루어 보겠습니다.

다음과 같이 판매 실적 자료를 만들어야 하는데 '상품코드'는 이미 입력되어 있고 상품정보에서 '상품명'과 '단가'를 가져오는 경우입니다.

XLOOKUP 함수 실무 예제

 

1) 상품, 단가를 동시에 가져오기

상품명을 가져오기 위해 [D40] 셀에 다음 수식을 입력합니다.

=XLOOKUP(C40,$B$6:$B$12,$C$6:$D$12,"**찾는 값 없음**")

수식에서 세 번째 인수를 다음과 같이 C, D 열을 입력하면  상품명과 단가를 동시에 가져옵니다.

XLOOKUP 함수 실무 예제

 

2) 수식 복사/붙여넣기

마지막으로 [D40] 셀에 입력된 수식을 복사하여 아래의 범위에 붙여 넣으면 판매 실적 자료가 완성됩니다.

XLOOKUP 함수 실무 예제

 

 


관련 글

VLOOKUP 함수 사용법 VLOOKUP 함수 - 범위에서 값 찾기 - 엑셀 VLOOKUP 함수는 셀 범위에서 원하는 값을 찾을 때 사용하는 함수입니다. VLOOKUP이라는 함수 이름은 Vertical Lookup을 줄여서 쓴 것으로 다음과…

VLOOKUP 함수 - 범위에서 값 찾기 더 보기 »

HLOOKUP 함수 사용법 HLOOKUP 함수 - 표를 수평으로 따라가면서 값찾기 - 엑셀의 HLOOKUP 함수는 셀 범위에서 원하는 값을 찾을 때 사용하는 함수입니다. HLOOKUP이라는 함수 이름은 Horizontal Lookup을 줄여서 쓴 것으로 다음과…

HLOOKUP 함수 - 표를 수평으로 따라가면서 값찾기 더 보기 »

LOOKUP 함수 사용법 LOOKUP 함수 - 범위에서 값 찾기 - 엑셀 LOOKUP 함수는 이름 그대로 뭔가를 찾는 함수입니다. 비슷한 함수로 VLOOKUP, HLOOKUP이 있습니다. 이들 함수와 사용법도 비슷하고 기능도 비슷하지만 LOOKUP…

LOOKUP 함수 - 범위에서 값 찾기 더 보기 »

MATCH 함수 사용법 MATCH 함수 - 범위에서 값의 위치 찾기 - 엑셀의 MATCH 함수는 지정된 범위에서 찾고자 하는 값의 위치를 반환합니다. 단순히 위치만 찾아 주므로 위치만 가지고는 쓸 일이 많이 없습니다.…

MATCH 함수 - 범위에서 값의 위치 찾기 더 보기 »

INDEX 함수 - 범위에서 행과 열을 이용하여 값 찾기 - 엑셀의 INDEX 함수는 테이블이나 범위에서 행과 열에 해당하는 값을 찾아줍니다. INDEX 함수에는 배열형과 참조형이라는 두 가지 형식이 있습니다. INDEX 함수는…

INDEX 함수 - 범위에서 행과 열을 이용하여 값 찾기 더 보기 »

VLOOKUP으로 안될 때 INDEX,MATCH 함수 사용 VLOOKUP으로 안될때 INDEX, MATCH 함수 사용하기 - 다음과 같은 표에서 상품코드 'A005'에 해당하는 상품 '마우스패드'를 찾아야 한다면 어떻게 해야 할까요? 찾는 키값이 첫 번째 열이 아니라 다른…

VLOOKUP으로 안될때 INDEX, MATCH 함수 사용하기 더 보기 »

INDEX, MATCH를 이용한 다중조건으로 찾기 INDEX, MATCH 함수를 이용한 다중조건으로 값찾기 - 다음 그림과 같이 상품정보에서 단가를 연도별로 관리한다고 할때 단가를 찾으려면 상품코드에 연도 조건을 추가해서 찾아야 합니다. 이 경우에 VLOOKUP 함수나…

INDEX, MATCH 함수를 이용한 다중조건으로 값찾기 더 보기 »

 

엑셀웍스 책 출간 안내

그동안 엑셀웍스에 올린 '엑셀 강좌', '함수 사용법', 여러 '실무 예제'에서 핵심만 뽑아 '된다! 엑셀 수식 & 함수' 책으로 출간하였습니다.

엑셀웍스 책 출간안내  자세히 보기

온라인 구매처: 예스24   교보문고   알라딘 

댓글 남기기

Scroll to Top