이벤트 응모명단에서 연락처에 일치하는 고객명과 고객주소를 찾아오려고 합니다.
이때 XLOOKUP 함수를 사용해보세요.
XLOOKUP 함수는 엑셀 2021 버전 마이크로소프트 365에서 사용할 수 있는 함수로 VLOOKUP 함수보다 인수 사용이 직관적이고 규칙이 까다롭지 않아 사용이 훨씬 쉽습니다.
실습 파일 다운로드
먼저 고객명을 찾아오는 수식부터 작성해 보겠습니다.
첫 번째 인수는 검색할 조건이 되는 값으로 이벤트 당첨 명단에서 연락처를 선택합니다.
=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 함수를 익혀 실무에 유용하게 활용해 보세요.
'기초에서 실무까지 > 엑셀' 카테고리의 다른 글
엑셀 함수] 둘 이상 조건에 맞는 값 표에서 찾아오는 수식 | VLOOKUP 함수로는 구할 수 없네!! (0) | 2024.07.03 |
---|---|
엑셀] 시간 표시 막대와 슬라이서로 대시보드 완성 | 클릭하면 보고서가 변한다고?? (2) | 2024.06.28 |
정수와 실수를 각각 다른 표시 형식으로 나타내는 방법 (0) | 2024.06.17 |
셀을 선택하면 연속된 여러 행/열에 색상이 표시되는 방법 (0) | 2024.06.05 |
엑셀] 데이터 표시 형식 총정리 | 데이터의 가치를 더욱 효과적으로 전달할 수 있어요~ (0) | 2024.05.11 |