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

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

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

 

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

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

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

 

구문(Syntax)

 

1. 기본 사용법

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

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

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

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

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

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

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

 

 

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

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

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

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

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

 

 

3) 상품코드로 단가 찾기

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

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

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

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

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

 

 

4) 찾는 값이 없을 때 처리

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

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

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

 

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

 

 

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

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

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

 

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

 

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

 

 

2. 실무 예제

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

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

 

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

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

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

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

 

2) 수식 복사/붙여넣기

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

 

 

---

관련 글