엑셀 공부 : XLOOKUP 사용법 예제 주의사항

XLOOKUP 사용법

엑셀 공부 : XLOOKUP 사용법

 

엑셀 XLOOKUP 함수는 VLOOKUP과 HLOOKUP의 한계를 극복한 강력한 검색 함수로, 데이터를 더욱 유연하고 직관적으로 처리할 수 있습니다. 이 함수는 Excel 2021 및 Microsoft 365 이상에서 사용할 수 있으며, 아래와 같은 구문을 가집니다:

 

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

 

• lookup_value: 찾으려는 값
  
  
• lookup_array: 검색할 범위 (열 또는 행)
  
  
• return_array: 반환할 값이 있는 범위
  
  
• [if_not_found]: 찾는 값이 없을 때 반환할 값 (선택사항)
  
  
• [match_mode]: 일치 방식 지정 (선택사항)
  
  
  • 0 또는 생략: 정확히 일치
    
    
  • -1: 정확히 일치 또는 작은 값
    
    
  • 1 : 정확히 일치 또는 큰 값
    
    
  • 2 : 와일드카드(*, ?) 사용
• [search_mode]: 검색 방향 지정 (선택사항)
  
  
  • 1 또는 생략: 위에서 아래로 검색
  • -1: 아래에서 위로 검색
  • 2: 오름차순 정렬 후 이진 검색
  • -2: 내림차순 정렬 후 이진 검색

---

XLOOKUP 함수의 주요 특징

1. 양방향 검색 가능
   VLOOKUP은 오른쪽으로만 검색이 가능하지만, XLOOKUP은 왼쪽과 오른쪽 모두 검색할 수 있습니다.
   
   
2. 결과 배열 반환
   여러 값을 한 번에 반환할 수 있어 효율적입니다.
   
   
3. 오류 처리 기능
   찾는 값이 없을 경우 기본적으로 #N/A를 반환하지만, [if_not_found] 인수를 사용해 사용자 지정 메시지를 출력할 수 있습니다.
   
   
4. 가로 및 세로 방향 지원
   데이터가 행 방향으로 배치된 경우에도 사용할 수 있습니다.
   
   
5. 와일드카드 지원
   *(모든 문자)와 ?(한 글자)를 사용해 부분 일치 검색이 가능합니다.
   
   

---

사용 예제

기본 사용법

아래 표에서 "홍길동"의 점수를 찾고자 할 때:

 

	A	B
1	이름	점수
2	홍길동	90
3	김철수	85
4	이영희	95

=XLOOKUP("홍길동", A2:A4, B2:B4)

 

결과: 90

---

값이 없을 때 오류 처리

찾으려는 값이 없을 경우 "데이터 없음"을 출력하도록 설정:

=XLOOKUP("박지성", A2:A4, B2:B4, "데이터 없음")

결과: "데이터 없음"

---

근사값 찾기

점수가 가장 가까운 작은 값을 반환:

=XLOOKUP(88, B2:B4, A2:A4, "데이터 없음", -1)

 

결과: "김철수"

---

여러 조건 활용

조건을 조합해 특정 데이터를 찾기:

=XLOOKUP(1, (A2:A4="홍길동")*(B2:B4>80), B2:B4)

 

결과: 90

---

사용 시 주의사항

1. 버전 제한
   XLOOKUP은 Excel 2021 및 Microsoft 365 이상에서만 사용할 수 있습니다. 이전 버전에서는 #NAME? 오류가 발생합니다.
2. 범위 크기 일치 필요
   lookup_array와 return_array의 크기가 다르면 #VALUE! 오류가 발생합니다.
3. 배열 충돌 방지
   결과가 배열로 반환될 경우 출력 범위에 다른 데이터가 있으면 #SPILL! 오류가 나타납니다.
4. 호환성 문제
   파일을 이전 버전 사용자와 공유할 경우 VLOOKUP 또는 INDEX/MATCH를 사용하는 것이 더 적합할 수 있습니다.

---

문제와 답안

문제:

아래 데이터를 참고하여 "이영희"의 이메일 주소를 찾아보세요.

 

	A	B	C
1	이름	부서	이메일
2	홍길동	영업팀	hong@company.com
3	김철수	개발팀	kim@company.com
4	이영희	디자인팀	lee@company.com

답안:

=XLOOKUP("이영희", A2:A4, C2:C4)

 

결과: lee@company.com

XLOOKUP 함수는 실무에서 매우 유용하며, 특히 데이터 정리가 복잡하거나 동적 참조가 필요한 경우 큰 장점을 제공합니다!