4-63. 스프레드시트 XLOOKUP 함수

스프레드시트에서 XLOOKUP 함수는 표나 범위에서 특정 값을 찾아 대응하는 값을 반환하는 강력한 함수입니다. VLOOKUP, HLOOKUP의 단점을 보완하며 가독성 높고 오류 가능성이 적은 검색을 지원합니다.

 

🔗 지금 바로 사용해보세요!

📥 XLOOKUP 함수 스프레드시트 바로가기

클릭 한 번으로 바로 접속해서 복사하여 사용하실 수 있습니다

 

주요 특징

·        세로·가로 모두 검색 가능

·        찾는 값이 없는 경우 대체값 지정 가능

·        열 위치 변경에도 수식이 깨지지 않음

 

 

1. 주제 설명: XLOOKUP 개념과 필요성

 

스프레드시트에서 데이터를 관리할 때 흔히 필요한 작업은 키(예: 상품 코드, 학생 번호)를 기준으로 교차 참조하여 이름이나 점수 등 연관된 정보를 찾아오는 것입니다.
과거에는 VLOOKUP(찾을값, 범위, 열번호, 정렬여부)를 주로 사용했지만,

·        검색 대상이 뒤쪽 열로만 제한됨

·        열 삽입·삭제 시 열번호 조정 필요

·        결과값이 없으면 #N/A 오류 발생

등 여러 불편함이 있었습니다.
XLOOKUP은

"XLOOKUP"("찾을값","검색범위","반환범위",["없을경우"])

형태로 간단히 사용하며, 위 문제를 모두 해결합니다.

 

 

2. 실습 방법: 단계별 적용법

아래 예시 데이터를 활용하여 단계별로 따라 해 보세요.

 

1.      매핑 테이블(Lookup) 생성
– 시트명: Lookup
– A열: Key(번호)
– B열: Value(이름/값)

 

2.     원본 데이터(RawData) 준비
– 시트명: RawData
– A열: 번호
– B열: 예시데이터1(입력값)
– C열: 예시데이터2(검색 키)

 

3.      XLOOKUP 수식 입력
– D2셀에 다음 수식을 입력 후, D51까지 채우기:
\n=XLOOKUP(C2,Lookup!A2:A51,Lookup!B2:B51,\"Not found\")\n
– 찾는 키가 없으면 “Not found” 표시

 

4.     결과 확인
– D열에 매칭된 값이 표시됩니다.

 

 

3. CSV 예시 데이터

아래 xlookup_example.csv 파일을 다운로드하여 실습해 보세요.

·        제목행: 번호, 예시데이터1, 예시데이터2, 결과값

·        50개 RawData 포함

·        결과값은 수식 텍스트와 실제 반환값 함께 표시

번호,예시데이터1,예시데이터2,결과값
1,A,12,'=XLOOKUP(C2,Lookup!A2:A51,Lookup!B2:B51,"Not found") -> Value12
2,D,34,'=XLOOKUP(C3,Lookup!A2:A51,Lookup!B2:B51,"Not found") -> Value34
3,C,5,'=XLOOKUP(C4,Lookup!A2:A51,Lookup!B2:B51,"Not found") -> Value5
4,B,50,'=XLOOKUP(C5,Lookup!A2:A51,Lookup!B2:B51,"Not found") -> Value50
5,E,27,'=XLOOKUP(C6,Lookup!A2:A51,Lookup!B2:B51,"Not found") -> Value27

 

 

4. XLSX 예시 템플릿 갤러리

다음 xlookup_template.xlsx 파일은

·        RawData, Lookup, Dashboard 3개 시트 구성

·        RawData 시트에 수식과 결과 자동 계산

·        깔끔한 갤러리 스타일 디자인 적용

 

 

5. 실무 및 교육 현장 활용 사례

 

사례 1: 학생 성적 관리

중학교 교사가 학생 학번(Key)을 입력하면, 학생 이름과 성적을 자동 조회해 점수표를 완성할 수 있습니다.

·        성적이 없는 학생은 “Not found”로 표시되어 데이터 누락 방지

·        학번 변경 시 자동 반영

 

사례 2: 재고 관리 시스템

상품 코드(Key)를 입력하면 상품명과 현재 재고량을 즉시 조회하여 발주 목록을 작성할 수 있습니다.

·        코드 간 누락이 있으면 대체값으로 가이드 제공

·        신규 상품 추가 시 Lookup 시트에만 등록하면 자동 적용

 

사례 3: 교육용 실습 자료

스프레드시트 교육 수업에서 학생들이 직접 RawData에 값을 입력하고 Lookup 시트를 관찰하도록 합니다.

·        XLOOKUP 수식을 입력해 보며 함수 구조 이해

·        Dashboard 시트에서 요약 결과 확인하며 데이터 분석 능력 향상

 

모든 과정이 중학교 2학년 학생도 따라 할 수 있도록 템플릿과 CSV 파일을 제공하였습니다.