엑셀 XLOOKUP, FILTER, SORT로 데이터 관리

엑셀 데이터, 아직도 VLOOKUP과 수동 필터링, 정렬로 씨름하고 계신가요?

엑셀 365의 강력한 최신 함수인 XLOOKUP, FILTER, SORT를 활용하면 데이터 관리 업무의 생산성을 혁신적으로 끌어올릴 수 있습니다. 이 세 함수를 조합하면 이전에는 상상하기 힘들었던 빠르고 유연한 데이터 조회가 가능해집니다.

1. XLOOKUP: VLOOKUP을 능가하는 만능 조회 함수

VLOOKUP의 한계에 답답함을 느끼셨다면, XLOOKUP은 그 해답입니다. 

XLOOKUP은 VLOOKUP의 모든 기능을 포함하면서도 훨씬 더 강력하고 유연합니다.

• 주요 특징:
  양방향 조회: 왼쪽이든 오른쪽이든 원하는 방향으로 데이터를 조회할 수 있습니다. (VLOOKUP은 오른쪽만 가능)
  정확한 일치 기본값: VLOOKUP처럼 정확한 일치를 위해 FALSE를 입력할 필요가 없습니다. 기본값이 정확한 일치입니다.
  찾을 수 없을 때 값 지정: 조회하는 값을 찾지 못했을 때 표시할 값을 직접 지정할 수 있습니다.
  배열 반환: 여러 열의 데이터를 한 번에 반환할 수 있습니다.
• 기본 구문:
  =XLOOKUP(찾을_값, 찾을_범위, 반환_범위, [찾을_수_없을_때], [일치_모드], [검색_모드])

 

실무 예제: 제품 코드로 여러 정보 한 번에 조회하기

제품 코드	제품명	단가	재고
A001	노트북	1,200,000	50
B002	마우스	25,000	200
C003	키보드	80,000	120

위와 같은 데이터에서 제품 코드 'B002'로 제품명, 단가, 재고를 한 번에 가져오고 싶다면:

=XLOOKUP("B002", A:A, B:D, "찾을 수 없음")
이렇게 하면 B002에 해당하는 '마우스', '25000', '200'이 각각의 셀에 자동으로 채워집니다. VLOOKUP이었다면 3번의 수식을 입력해야 했을 것입니다!

2. FILTER: 원하는 데이터만 쏙쏙! 동적 필터링의 마법

FILTER 함수는 조건에 맞는 데이터 행을 동적으로 추출하여 보여주는 함수입니다. 

더 이상 수동 필터링으로 데이터를 숨기거나 복사 붙여넣기 할 필요가 없습니다.

• 주요 특징:
  다중 조건 필터링: 여러 조건을 동시에 적용하여 데이터를 필터링할 수 있습니다.
  동적 업데이트: 원본 데이터가 변경되면 필터링된 결과도 자동으로 업데이트됩니다.
  "없을 때" 값 지정: 필터링된 결과가 없을 때 표시할 메시지를 지정할 수 있습니다.
• 기본 구문:
  =FILTER(배열, 포함, [비어_있을_경우])

 

실무 예제: 특정 조건의 주문 내역만 추출하기

주문 번호	고객명	제품명	수량	금액	상태
ORD001	김영희	노트북	1	1,200,000	배송 완료
ORD002	박철수	마우스	2	50,000	배송 중
ORD003	이지은	키보드	1	80,000	주문 취소
ORD004	김영희	키보드	1	80,000	배송 완료

위 데이터에서 '김영희' 고객의 '배송 완료'된 주문 내역만 추출하고 싶다면:

=FILTER(A:F, (B:B="김영희")*(F:F="배송 완료"), "해당 데이터 없음")
*(AND) 연산자를 사용하여 두 조건을 모두 만족하는 데이터를 필터링합니다. 마치 SQL의 WHERE 절처럼 직관적으로 사용할 수 있습니다.

3. SORT: 원하는 순서대로 정렬! 유연한 동적 정렬

SORT 함수는 배열을 지정된 열을 기준으로 오름차순 또는 내림차순으로 정렬하여 반환하는 함수입니다.

• 주요 특징:
  동적 정렬: 원본 데이터가 변경되면 정렬된 결과도 자동으로 업데이트됩니다.
  여러 열 기준 정렬: 여러 열을 기준으로 우선순위를 두어 정렬할 수 있습니다.
  오름차순/내림차순 선택: 각 열에 대해 오름차순 또는 내림차순을 지정할 수 있습니다.
• 기본 구문:
  =SORT(배열, [정렬_인덱스], [정렬_순서], [기준_열_별_정렬])

 

실무 예제: 제품별 재고 현황을 재고가 많은 순서대로 정렬하기

제품 코드	제품명	재고
A001	노트북	50
B002	마우스	200
C003	키보드	120
D004	모니터	80

위 데이터를 재고가 많은 순서대로(내림차순) 정렬하고 싶다면:

=SORT(A:C, 3, -1)
여기서 3은 세 번째 열(재고)을 기준으로, -1은 내림차순을 의미합니다. 1은 오름차순입니다.

4. XLOOKUP + FILTER + SORT: 고급 실무 예제로 데이터 관리 마스터하기

이 세 함수를 조합하면 훨씬 더 복잡하고 유연한 데이터 관리 시나리오를 해결할 수 있습니다.

실무 예제: 특정 고객의 최신 주문 내역을 금액이 높은 순서대로 조회하기

우리의 주문 데이터에서 '김영희' 고객의 주문 내역 중 '배송 완료' 상태인 주문만을 필터링하고, 그 결과를 가장 최신 주문(주문 번호가 높은 순서)을 우선으로 하여 금액이 높은 순서대로 정렬하고 싶습니다.

이때, 주문 번호가 문자열이라 최신을 구분하기 어렵다면, 날짜 열이 있다고 가정하거나, 번호에 날짜 정보가 포함되어 있다고 가정합니다. 

여기서는 편의상 "주문 번호" 자체가 최신성을 의미한다고 가정하고, 주문 번호를 내림차순으로 먼저 정렬한 다음, 금액을 내림차순으로 정렬합니다.

=SORT(FILTER(A:F, (B:B="김영희")*(F:F="배송 완료")), {1,5}, {-1,-1})

• FILTER(A:F, (B:B="김영희")*(F:F="배송 완료")): 먼저 전체 데이터 A:F에서 고객명이 '김영희'이고 상태가 '배송 완료'인 행들만 필터링합니다.
• SORT(..., {1,5}, {-1,-1}): 필터링된 결과를 정렬합니다.
• {1,5}: 첫 번째 열(주문 번호)을 1차 정렬 기준으로, 다섯 번째 열(금액)을 2차 정렬 기준으로 사용합니다.
• {-1,-1}: 두 기준 모두 내림차순(-1)으로 정렬합니다. (주문 번호가 높은 것이 최신이라고 가정)
  
  

이렇게 하면 '김영희' 고객의 배송 완료된 주문 중 최신 주문이 상단에 위치하고, 같은 최신 주문 내에서는 금액이 높은 순서대로 정렬된 결과를 얻을 수 있습니다. XLOOKUP은 이 결과를 바탕으로 특정 필드를 조회할 때 사용될 수 있습니다.

XLOOKUP과 조합 예시:

만약 위에서 필터링되고 정렬된 데이터에서 가장 첫 번째(즉, 가장 최신 주문의 금액)를 알고 싶다면, 다음과 같이 XLOOKUP을 활용할 수 있습니다. 

다만, FILTER와 SORT는 배열을 반환하기 때문에 XLOOKUP과 조합하는 방식보다는 INDEX나 TAKE 함수를 활용하는 것이 더 일반적입니다.

예를 들어, 필터링되고 정렬된 결과의 첫 번째 행에서 금액(5번째 열)을 가져오려면:

=INDEX(SORT(FILTER(A:F, (B:B="김영희")*(F:F="배송 완료")), {1,5}, {-1,-1}), 1, 5)

 

이처럼 XLOOKUP, FILTER, SORT는 단독으로도 강력하지만, 서로 조합될 때 진정한 시너지를 발휘하여 데이터 관리의 복잡성을 크게 줄여줍니다.


엑셀 365의 XLOOKUP, FILTER, SORT 함수는 단순한 기능 개선을 넘어 데이터 관리 방식을 근본적으로 변화시키는 핵심 도구입니다. 

이 함수들을 숙달한다면 반복적인 수작업을 줄이고, 데이터를 더욱 빠르고 정확하게 분석하며, 궁극적으로는 업무 생산성을 크게 향상시킬 수 있습니다.