엑셀 조회 함수 완벽 마스터: 상황별 최적 전략을 고르는 검증된 프레임워크

조회(Lookup) 함수를 활용하면 데이터 검색 작업이 훨씬 효율적으로 바뀝니다. 그런데 엑셀은 다양한 조회 방식을 제공하기 때문에 어떤 전략을 선택해야 할지 고민되기 마련입니다. 상황에 맞는 최적의 방법을 골라야 가장 신뢰할 수 있는 결과를 얻을 수 있습니다. 이번 튜토리얼에서는 올바른 조회 전략을 선택할 수 있도록 돕는 엑셀 조회 함수 프레임워크를 만들어 보겠습니다. 이 프레임워크는 데이터의 구조, 규모, 활용 목적에 따라 숙련된 사용자가 가장 적합한 방식을 판단하는 데 큰 도움이 됩니다.

아래 흐름도를 따라가면 자신에게 맞는 조회 전략을 손쉽게 선택할 수 있습니다.

XLOOKUP: 일대일 조회에 최적 (최신 엑셀)

먼저 무엇을 찾으려는지 명확히 해야 합니다. 조건에 맞는 단 하나의 레코드를 가져오고 싶은가요, 아니면 조건에 일치하는 모든 레코드가 필요한가요?

결과가 하나뿐이고, 읽기 쉬운 수식과 '값 없음' 처리 옵션까지 원한다면 XLOOKUP을 사용하세요.

주문, 제품 정보, 고객 정보가 각각 별도의 테이블로 나뉘어 있는 샘플 영업 데이터를 예로 들어 보겠습니다. 여러 테이블에서 영업 정보를 가져오는 다양한 조회 전략을 살펴봅니다.

Orders 테이블에 UnitPrice 조회하기:

=XLOOKUP(D2, Products!$A$2:$A$7, Products!$D$2:$D$7, "Not found")

테이블 참조 방식으로 작성한 동일한 수식:

=XLOOKUP(Orders[@SKU], Products[SKU], Products[UnitPrice], "SKU not found")

Orders 테이블에서 CustomerName 찾기:

=XLOOKUP(Orders[@CustomerID], Customers[CustomerID], Customers[CustomerName], "Customer not found")

이 수식들은 서로 다른 테이블에서 값을 찾아 일치하는 레코드 하나를 반환합니다. 최신 엑셀 환경에서 일대일 조회라면 XLOOKUP이 기본 선택지가 될 수 있습니다. 그 이유는 다음과 같습니다.

• if_not_found 인수를 통한 기본 오류 처리 기능을 갖춘 깔끔하고 읽기 쉬운 문법
• 왼쪽 방향 조회 및 양방향 조회 네이티브 지원
• 복잡한 match-type 매개변수 없이 정확히 일치와 근사 일치 모두 사용 가능

Microsoft 365 또는 Excel 2021 이상을 사용 중이고, 간단한 단일 값 조회가 필요하다면 XLOOKUP을 활용하세요.

INDEX/MATCH: 호환성이 필요할 때 여전히 강력한 선택

파일이 구버전 엑셀에서도 동작해야 하거나, 팀 내 표준이 INDEX/MATCH로 정해져 있다면 INDEX/MATCH를 선택하세요.

INDEX/MATCH로 UnitPrice 조회하기:

=INDEX(Products[UnitPrice], MATCH(Orders[@SKU], Products[SKU], 0))

이 수식은 구버전 환경에서 여전히 확실한 선택지입니다. 다만 많은 팀 입장에서는 XLOOKUP에 비해 수식을 해독하기 어렵고, 대규모 유지보수도 더 까다롭습니다.

FILTER: 일대다 결과에 최적 (동적 배열 목록)

자동으로 갱신되는 동적 레코드 목록이 필요하다면 FILTER를 선택하세요. FILTER는 조건에 따라 데이터의 부분 집합을 추출하고, 결과를 자동으로 최신 상태로 유지합니다. 일대다 조회를 위해 특화된 함수로서 다음과 같은 장점이 있습니다.

• 일치하는 모든 행을 동적 스플 배열(spill array)로 반환
• 부울 AND/OR 논리로 복수 조건 처리 가능
• 원본 데이터가 변경되면 결과 범위가 자동으로 늘어나거나 줄어듦
• 다른 동적 배열 함수와 매끄럽게 연동

특정 CustomerID의 모든 주문 표시:

=FILTER(Orders, Orders[CustomerID]=H2, "No orders found")

SKU P-101의 서부(West) 지역 주문 전체 조회:

=FILTER(Orders, (Orders[Region]=H9)*(Orders[SKU]=H7), "No matches")

이 수식들은 XLOOKUP과 결정적으로 다릅니다. FILTER는 단일 값을 반환하지 않고, 테이블 형태로 펼쳐지는(spill) 결과를 반환하기 때문입니다. 즉, XLOOKUP이나 INDEX/MATCH와는 역할 자체가 다른 것입니다.

파워 쿼리(Power Query): 조회가 사실상 '조인'이라면 최선 (데이터 모델링)

필터링된 결과에 추가 변환 작업까지 필요하다면 파워 쿼리가 더 나은 선택입니다. 다음과 같은 경우에 활용하세요.

• 데이터 용량이 클 때
• 외부 소스에서 데이터를 가져오며 주기적으로 새로 고침이 필요할 때
• 반복 실행 가능한 변환 파이프라인을 만들고 싶을 때
• 피벗 테이블·대시보드용으로 깔끔하게 병합된 테이블을 구축하고 싶을 때

이번에는 제품과 고객 정보(UnitPrice, Product, CustomerName, Segment)를 포함하는 테이블을 만들어 보겠습니다.

병합(Merge/Join) 절차:

• 범위를 테이블로 변환합니다(CTRL+T). 예제 데이터는 이미 테이블 형식입니다.
• 데이터 탭 >> 데이터 가져오기 >> 테이블/범위에서를 선택합니다(Orders부터 시작).

• 파워 쿼리 편집기에서 홈 >> 쿼리 병합을 선택합니다.
  • Orders 테이블과 Products 테이블을 병합합니다.
  • Orders[SKU]와 Products[SKU]를 선택합니다.
  • 조인 종류: 왼쪽 외부(Left Outer)를 선택합니다(모든 Orders 유지).
  • 확인을 클릭합니다.

• • 확장 아이콘을 클릭한 후 Product, Category, UnitPrice를 선택합니다.
  • 확인을 클릭합니다.

• Orders 테이블과 Customers 테이블을 병합합니다.
  • Orders[CustomerID]를 Customers[CustomerID]와 연결합니다.

• • 확장 아이콘을 클릭한 후 CustomerName, Segment를 선택합니다.
  • 확인을 클릭합니다.

• 닫기 및 로드(Close & Load)를 클릭해 데이터를 엑셀로 불러옵니다.

그러면 제품과 고객 세부 정보가 포함된 새로운 Orders 강화 테이블이 완성됩니다.

한 번만 병합해 두면 언제든 새로 고침만 하면 되므로, 수천 개의 행과 여러 열에 걸쳐 조회 수식을 반복해서 작성할 필요가 없습니다.

일대다 조회: FILTER vs 파워 쿼리 선택 기준

FILTER와 파워 쿼리는 모두 고급스럽고 자동화된 결과를 제공합니다. 하지만 일대다 조회에서 어느 쪽을 선택할지 고민될 때가 있습니다.

• 대화형 시트 경험이 필요하다면 FILTER를 선택하세요. 조건 셀만 바꾸면 목록이 즉시 갱신되므로 인터랙티브 대시보드에 적합합니다.
• 피벗 테이블, 차트, 모델 테이블 등 보고용 데이터 소스로 활용할 새로 고침 가능한 데이터셋이 필요하다면, 특히 대용량 데이터에서는 파워 쿼리를 선택하세요.

실전 팁과 요령

• 결과가 한 셀이면 XLOOKUP을 선택하세요(호환성이 필요하면 INDEX/MATCH).
• 결과가 자동으로 늘어나야 하는 목록/테이블이라면 FILTER를 선택하세요.
• 작업의 본질이 데이터셋 결합이고, 새로 고침과 확장이 필요하다면 파워 쿼리를 사용하세요.
• 여러 열(가격, 카테고리, 이름, 세그먼트 등)에 걸쳐 동일한 조회를 반복해야 한다면, 수식을 반복하기보다 파워 쿼리 병합을 사용하는 것을 강력히 권장합니다.

마무리

이번 튜토리얼의 흐름도는 상황에 맞는 조회 전략을 선택하는 데 도움을 줍니다. 가장 먼저 데이터가 요구하는 조회 유형을 판단하세요. 모든 상황에 통하는 '최고의' 조회 함수는 존재하지 않으며, 최적의 방법은 항상 맥락에 따라 달라집니다. 함수 자체가 아니라 프레임워크를 마스터하면, 조회 전략도 요구 사항과 함께 성장합니다. 올바른 조회 방식을 고르는 전략적 감각을 키우면 더 빠르게 구축하고, 더 쉽게 유지보수하며, 요구 사항이 변해도 흔들리지 않는 견고한 솔루션을 만들 수 있습니다.