엑셀 함수] XLOOKUP 함수 안벽 마스터 | VLOOKUP 아니고 XLOOKUP??

이벤트 응모명단에서 연락처에 일치하는 고객명과 고객주소를 찾아오려고 합니다.

이때 XLOOKUP 함수를 사용해보세요.

XLOOKUP 함수는 엑셀 2021 버전 마이크로소프트 365에서 사용할 수 있는 함수로 VLOOKUP 함수보다 인수 사용이 직관적이고 규칙이 까다롭지 않아 사용이 훨씬 쉽습니다.

 

실습 파일 다운로드

5-실습파일.xlsx

0.02MB

 

 

먼저 고객명을 찾아오는 수식부터 작성해 보겠습니다.

첫 번째 인수는 검색할 조건이 되는 값으로 이벤트 당첨 명단에서 연락처를 선택합니다.

=XLOOKUP 함수를 입력합니다. 첫 번째 인수는 검색할 조건이 되는 값으로 이벤트 당첨 명단에서 연락처를 선택합니다.

 

두 번째 인수는 조건에 해당하는 값을 검색할 범위로 이벤트 응모 명단에서 연락처 범위를 지정하고 절대 참조합니다.

 

이렇게 인수를 지정하면 이벤트 당첨 명단의 연락처를 이벤트 응모 명단의 연락처 범위에서 검색합니다.

 

세 번째 인수는 반환할 값이 있는 데이터 범위로 고객명 범위를 지정하고 절대 참조합니다.

 

그러면 연락처와 일치하는 고객명을 찾아옵니다. 인수 작성이 직관적이죠?

 

남아있는 인수들은 생락할 수 있습니다. 다음 내용에서 이 인수들의 사용법도 소개하겠습니다.

수식을 복사해 나머지 연락처의 고객명도 찾아옵니다.

 

그런데 #N/A 오류가 발생했네요.

 

#N/A 오류는 찾으려는 값이 없으면 발생하는 오류입니다. 010-838*-*431 연락처는 이벤트 응모 명단의 연락처 범위에서 찾을 수 없어 오류가 발생합니다.

 

그럼 #N/A 오류를 오류가 아닌 '명단에 없음'으로 표시해 보겠습니다.

XLOOKUP 함수의 네 번째 인수가 찾으려는 값이 없을 때 오류대신 반환할 값을 지정하는 인수입니다.

수식을 수정하겠습니다.

[H5] 셀을 더블클릭하고 세 번째 인수 다음에 인수 사이를 구분하는 쉼표를 먼저 입력합니다. 그리고

'명단에 없음'을 입력합니다. 인수가 문자열이면 큰따옴표로 묶어서 입력해야 합니다.

 

<Enter>를 누르고 수식을 복사합니다. 오류가 아닌 '명단에 없음'으로 표시됩니다.

 

같은 방법으로 고객주소도 구하겠습니다.

=XLOOKUP 함수를 입력합니다. 첫 번째 인수에는 찾으려는 값, 연락처 셀을 지정합니다.

 

두 번째 인수에는 이벤트 응모 명단에서 연락처 범위를 지정하고 절대 참조합니다. 그럼 이벤트 응모 명단의 연락처 범위에서 찾으려는 값을 검색합니다.

세 번째 인수에는 이벤트 응모 명단에서 고객주소 범위를 지정하고 절대 참조합니다.

 

그리고 그 다음 인수에는 '명단에 없음'을 입력하고 <Enter>를 누릅니다.

 

수식을 복사해 결과를 구합니다. 오류가 발생하면 오류 대신 '명단에 없음'으로 표시됩니다.

유사 일치 값을 검색해 값 반환

 

이번에는 '교육평가결과' 시트를 선택합니다.

평가점수 구간에 해당하는 학점을 찾아오는 수식을 작성하겠습니다.

오른쪽에는 점수구간 표가 있습니다. 하지만 이 표를 함수에서 참조하면 #N/A 오류가 발생합니다.

 

90~100으로 입력된 값은 엑셀에서 문자로 인식됩니다.

하나의 셀에 숫자와 문자를 함께 입력하면 엑셀은 이 값을 문자로 인식합니다.

그래서 표를 다시 작성해야 합니다.

 

[H9] 셀부터 100에서 59까지 입력합니다. 그리고 [I9] 셀부터 A에서 F까지 입력합니다.

 

[I9:I13] 셀 범위에는 물결 무늬를 붙여 90에서 100점까지로 점수 구간이 표시되도록 표시 형식을 적용하겠습니다.

범위를 지정하고 단축키 <Ctrl + 1>을 눌러 [셀 서식]을 실행합니다. '사용자 지정' 범주를 선택하고 형식 입력창에 물결 무늬를 입력한 후 서식 0을 입력합니다. 여기서 0은 숫자 값을 표시하는 형식이 됩니다. [확인]을 누릅니다.

 

이제 결과를 구해보겠습니다.

[F4] 셀을 선택하고 =XLOOKUP 함수를 입력하고 첫 번째 인수에는 평가점수 셀을 지정합니다.

 

두 번째 인수에는 작성한 학점표에서 점수 구간 중 최소값으로 입력된 90에서 0 범위를 지정합니다. 그리고 절대 참조합니다.

 

세 번째 인수에는 학점 범위를 지정하고 절대 참조합니다.

 

현재 예시에서는 #N/A 오류가 발생하지 않기 때문에 네 번째 인수는 생략합니다. 인수를 생략하더라도 인수 사이의 쉼표는 입력해야 합니다.

 

그리고 다섯 번째 인수를 입력할 때 신경써야 하는데 정확하게 일치하는 값을 검색하려면 0을 입력하거나 생락하면 되지만 현재 예시처럼 구간에서 일치하는 값을 찾아올 때에는 값을 구분해서 입력해야 합니다.

구간의 최소값에서 값을 검색하려면 -1을 입력합니.

 

최대값에서 값을 검색하려면 1을 입력하면 됩니다.

 

두 번째 인수로 지정한 범위는 점수 구간의 최소값을 입력한 범위이므로 이 범위에서 검색해 학점을 찾아오는 경우에는 -1을 입력해야 합니다.

 

마지막 인수에는 1 또는 생략하면 기본값으로 위에서 아래로 값을 검색합니다.

 

일반적으로 정방향으로 값을 검색하지만 역방향으로 값을 검색하려면 -1을 입력하면 됩니다.

 

수식을 복사해 결과를 구합니다. 정확하게 학점이 구해졌죠?

 

엑셀 2021버전, M365를 사용 중이라면 XLOOKUP 함수를 익혀 실무에 유용하게 활용해 보세요.