INDEX+MATCH 함수와 VLOOKUP 함수를 대체하는 XLOOKUP 함수

 

 

 

VLOOKUP 함수는 찾기/참조 영역 함수 중 가장 많이 사용되는 함수입니다.

VLOOKUP 함수는 모든 경우에 사용할 수 있는 함수가 아니라, 데이터를 검색하고 찾을 참조표가 일정한 형식으로 만들어져 있어야 사용할 수 있는 함수입니다.

그래서 VLOOKUP 함수를 쓸 수 없는 경우에는 INDEX 함수와 MATCH 함수를 중첩해서 사용합니다.

INDEX 함수와 MATCH 함수를 중첩해 사용하면 아무래도 하나의 함수를 사용하는 것보다 사용방법이 불편합니다.

 

Excel 2021 버전과 MS 365에 XLOOKUP 함수가 추가되어 INDEX+MATCH 함수 대신 사용할 수 있습니다.

 

 

실습 파일 다운로드

INDEX+MATCH함수_실습데이터.xlsx

0.02MB

 

완성 파일 다운로드

XLOOKUP 함수_완성.xlsx

0.02MB

 

실습 파일은 INDEX+MATCH 함수에서 사용하던 파일을 사용합니다.

 

파견 업무 직원 명단을 작성하려고 합니다.

직원명부 시트에 직원 상세 정보가 입력되어 있습니다.

직원명부 시트를 보면 VLOOKUP 함수는 사용할 수 없다는 것을 알 수 있습니다.

주민등록번호를 이용해 이름을 찾으려고 하는데, 직원명부 시트에서 주민등록번호 왼쪽 열에 이름이 있기 때문입니다.

VLOOKUP 함수를 이용하려면 찾기 기준이 되는 열 오른쪽에 찾으려는 값이 있어야만 합니다.

 

INDEX 함수와 MATCH 함수를 중첩해 구하는 방법은 다른 강좌를 참조하세요.

 

엑셀 함수] INDEX 함수에 MATCH 함수 중첩하기

XLOOKUP 함수를 이용해 보겠습니다.

 

[D4] 셀에 커서를 두고 =xl을 입력하면 함수 목록이 나타납니다.

더블클릭해 XLOOKUP 함수 이름을 나타냅니다.

<Shift + F3>을 눌러 [함수 인수] 대화상자를 나타냅니다.

XLOOKUP 함수는 6개 인수가 있지만 필수 인수는 3개입니다.

 

Lookup_value 인수에 찾으려는 주민등록번호 [C4] 셀을 선택합니다.

Lookup_array 인수에 참조표에서 주민등록번호가 입력되어 있는 열 범위 [직원명부!F3:F52]를 선택하고 절대 참조합니다.

Return_array 인수에 참조표에서 이름이 입력되어 있는 열 범위 [직원명부!C3:C52]를 선택하고 절대 참조합니다.

 

여기까지 입력하면 됩니다.

[확인]을 누릅니다.

XLOOKUP 함수의 나머지 3개 인수는 If_not_found, Match_mode, Search_mode 입니다.

 

If_not_found 인수는 일치하는 값을 찾을 수 없는 경우 나타낼 값을 입력합니다. 따로 값을 입력하지 않으면 VLOOKUP 함수처럼 #N/A 오류메시지를 반환합니다.

Match_mode 인수는 0, -1, 1, 2 중 선택할 수 있고, 기본값은 0 입니다. 0은 정확하게 일치하는 값을 찾습니다. -1은 정확한 값을 찾을 수 없는 경우 작은 값을 반환하고, 1은 정확한 값을 찾을 수 없는 경우 큰 값을 반환합니다. 2는 와일드카드(*, ?, ~)를 사용해 찾는데, 물음표(?)는 한 문자를 의미하고, 별표(*)는 개수에 상관없는 문자, 물결표(~)는 찾는 글자에 물음표(?)나 별표(*), 물결표(~)가 있는 경우 해당 문자 앞에 적어 와일드카드 역할을 하지 않도록 만듭니다.

Search_mode 인수는 검색 모드를 결정하는데, 1, -1, 2, -2 네가지 방법이 있고, 기본값은 1입니다. 1은 첫번째부터 순서대로 검색하고, -1은 마지막부터 역방향으로 검색합니다. 2와 -2는 Lookup_array 항목이 정렬되어 있을 때 빠르게 검색하는 방법입니다.

 

앞에서는 INDEX+MATCH 함수를 대체하는 용도로 수식을 작성했는데, XLOOKUP 함수의 새 기능을 써 보겠습니다.

 

파견명단 시트에 찾아올 항목이 이름, 소속, 직위 순서대로 있고, 직원명부 시트에도 항목이 이름, 소속, 직위 순서로 나열되어 있습니다.

이런 경우 XLOOKUP 함수 세번째 인수 Return_array를 셀 범위로 지정해 한꺼번에 가져올 수 있습니다.

 

[D4] 셀 수식을 수정하겠습니다.

[D4] 셀을 선택하고 <Shift + F3>을 누릅니다.

Return_array 인수에 [직원명부!C3:E52] 셀 범위를 선택하고 절대 참조합니다.

[확인]을 눌러 결과를 확인합니다.

배열이 반환되어 [D4] 셀 뿐만 아니라 [E4], [F4] 셀에도 값이 구해져 있습니다.

수식은 [D4] 셀에 입력되어 있기 때문에 아래로 채우기 할 때는 [D4] 셀에서 채워야 합니다.

입사일은 따로 떨어져 있기 때문에 한꺼번에 구할 수 없어 다시 구합니다.

VLOOKUP 함수를 이용할 수도 있지만, XLOOKUP 함수로 구하겠습니다.

[확인]을 눌러 결과를 봅니다.

날짜가 아니라 숫자로 나타난 이유는 표시 형식 때문입니다.

[G4] 셀에 커서를 두고 [홈]-[표시 형식]-[간단한 날짜]를 선택합니다.

날짜 형식으로 나타납니다.

 

XLOOKUP 함수는 INDEX 함수와 MATCH 함수를 중첩해 사용하는 것보다 휠씬 쉽게 원하는 결과를 나타낼 수 있는 함수이고, INDEX 함수와 MATCH 함수를 중첩해 사용하는 것으로는 할 수 없는 다양한 기능도 있습니다.

 

반면에 결정적인 단점도 있는데, 바로 XLOOKUP 함수를 쓸 수 있는 엑셀 버전이 제한적이라는 것입니다.

Excel 2021 버전과  MS 365에서 사용할 수 있습니다.

 

필요하시는 분들께 도움이 되길 바랍니다.