고급 Excel 조회 마스터:XLOOKUP 및 INDEX-MATCH-MATCH를 사용한 VLOOKUP 이상

Excel은 데이터 관리 및 데이터 분석을 위한 광범위한 조회 기술을 제공합니다. VLOOKUP은 일반적으로 데이터 검색에 사용되지만 왼쪽에 조회 열이 필요하고 유연성이 없는 오류 처리와 같은 제한 사항이 있습니다. 이를 극복하기 위해 XLOOKUP 및 INDEX-MATCH-MATCH와 같은 고급 기능은 더 많은 유연성, 제어 및 효율성을 제공합니다. XLOOKUP 및 INDEX-MATCH-MATCH를 사용하여 VLOOKUP 이상의 고급 조회 기술을 효과적으로 활용하는 방법을 보여드리겠습니다.

실제 사례를 사용하여 고급 조회 기술을 적용하기 위한 판매 데이터 세트를 고려해 보겠습니다.

XLOOKUP 고급 조회 기술

XLOOKUP은 단일 또는 다중 열 조회를 위한 Excel의 다양한 기능입니다. 모든 방향(왼쪽에서 오른쪽, 오른쪽에서 왼쪽, 수직, 수평)으로 검색할 수 있고 사용자 정의 오류 메시지를 제공하며 조회를 위해 정렬된 데이터가 필요하지 않습니다. XlOOKUP 기능은 Excel 2021 및 MS Office 365 사용자만 사용할 수 있습니다. XLOOKUP 기능은 조회 값의 데이터 변경 사항을 자동으로 업데이트합니다.

구문:

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

• 조회_값: 검색할 값입니다.
• 조회_배열: 검색할 범위 또는 배열입니다.
• return_array: 결과를 반환할 범위 또는 배열입니다.
• [if_not_found]; [선택사항]: 일치하는 항목이 없을 경우 반환할 값입니다.
• [경기_모드]; [선택사항]: 일치 유형을 지정합니다. 정확한 일치, 와일드카드 또는 대략적인 일치를 선택할 수 있습니다.
  • 0   – 정확히 일치(기본값).
  • 1   – 정확하거나 그 다음으로 큰 것.
  • -1 – 정확하거나 그 다음으로 작습니다.
  • 2   – 와일드카드 일치.
• [검색_모드]; [선택사항]: 처음에서 마지막 또는 마지막에서 처음 등 검색 방향을 결정합니다
  • 1 – 처음부터 끝까지 검색하세요.
  • -1 – 마지막부터 처음까지 검색하세요.
  • 2 – 이진 검색(오름차순).
  • -2 – 이진 검색(내림차순).

1. 단일 기준에 XLOOKUP 사용

주문에 대해 거의 $100를 지출하는 고객을 살펴보기 위해 판매 데이터 세트에서 $100에 가장 가까운 판매 금액을 찾아보겠습니다. 다음 수식을 삽입하세요.

수식:

=XLOOKUP(100, G2:G71, A2:G71, “찾을 수 없음”, 1)

이 수식은 G2:G71 범위에서 100을 검색하고 A2:G71에서 해당 행을 반환합니다. 100에 가장 가까운 값을 찾지 못하면 '찾을 수 없음'으로 표시됩니다.

출력:

1007     2024년 1월 4일            다니엘 마르티네즈             동부      39.99    3             119.97

2. 여러 기준으로 XLOOKUP 사용

XLOOKUP을 사용하여 여러 기준을 연결하면 더 복잡한 조회를 수행할 수 있습니다. 공식을 살펴보겠습니다.

수식:

=XLOOKUP(“멜리사 로페즈” &“서부”, C2:C71 &D2:D71, A2:G71)

이 수식은 고객의 이름과 지역을 연결하고 연결된 조회 배열 내에서 결합된 값을 찾습니다. 선택한 범위에서 해당 항목을 검색합니다.

출력:

1012     2024년 1월 6일             멜리사 로페즈  웨스트     79.99    2             159.98

고급 조회 기술을 위한 INDEX-MATCH-MATCH

INDEX-MATCH-MATCH는 행과 열 기준을 모두 기반으로 값을 조회해야 할 때 사용되며 2D 데이터 테이블에 이상적입니다.

구문:

=INDEX(배열, MATCH(row_lookup_value, row_lookup_array, 0), =MATCH(column_lookup_value, column_lookup_array, 0))

• 배열: 셀 범위에는 검색하려는 값이 포함되어 있습니다.
• MATCH(row_lookup_value, row_lookup_array, 0): 행 번호를 반환합니다.
  • row_lookup_value: 행에서 검색할 값입니다.
  • row_lookup_array: 검색할 행 범위입니다.
  • 0 – 정확히 일치

• MATCH(column_lookup_value, column_lookup_array, 0): 열 번호를 반환합니다.
  • column_lookup_value: 열에서 검색할 값입니다.
  • column_lookup_array: 검색할 열 범위입니다.
  • 0 – 정확히 일치

1. 행과 열에 대한 2D 조회

INDEX-MATCH-MATCH 공식을 이용하여 특정 매출 금액의 고객에 대한 매출 금액을 살펴보겠습니다. 다음 수식을 삽입하세요.

=INDEX(A2:G71, MATCH(“John Smith”, C2:C71, 0), MATCH(“판매액”, A1:G1, 0))

이 수식은 C2:C71 범위에서 John Smith를 검색하고 A1:G1에서 "Sales Amount"를 검색합니다. 그런 다음 A2:G71의 교차 값으로 돌아갑니다.

출력:

99.98

2. 3D 조회를 위한 고급 INDEX-MATCH-MATCH 공식

INDEX-MATCH-MATCH는 일반적으로 2차원 조회에 사용되므로 여러 MATCH 함수가 포함된 배열 수식을 사용하여 3차원 조회로 확장할 수 있습니다. 대규모 데이터 세트와 복잡한 기준 일치에 이상적이며 유연성과 기능 면에서 VLOOKUP보다 뛰어납니다.

수식:

=INDEX(G2:G71, MATCH(1, (C2:C71=”존 스미스”) * (D2:D71=”남부”) * (F2:F71=4), 0))

이 수식은 세 가지 기준(C열의 고객 이름, D열의 지역, F열의 수량)과 일치하여 판매액을 검색합니다. 조건은 곱셈을 AND 함수로 사용하여 결합되므로 MATCH는 모든 기준이 충족되는 행을 찾을 수 있으며 INDEX는 G2:G71에서 해당 값을 반환합니다.

출력:

129.99

XLOOKUP 및 INDEX-MATCH 사용의 장점

VLOOKUP 대신 XLOOKUP을 선택하는 이유는 무엇인가요?

• 양방향 검색 :XLOOKUP은 양방향으로 값을 조회할 수 있는 반면, VLOOKUP은 왼쪽에서 오른쪽으로만 검색할 수 있습니다.
• 열 색인 번호 없음 :열 인덱스 번호를 지정할 필요가 없으므로 열 변경이 영향을 미치지 않습니다.
• 기본 일치검색 :기본적으로 XLOOKUP은 정확하게 일치하는 항목을 검색하여 오류를 최소화합니다.
• XLOOKUP의 와일드카드 검색 :* 및?와 같은 와일드카드를 사용할 수 있습니다. 텍스트 검색을 수행할 때 XLOOKUP을 사용합니다.
• 오류 처리 :일치하는 항목이 없을 경우 무엇을 반환할지 언급할 수 있습니다.

VLOOKUP에 비해 INDEX-MATCH의 이점

• 유연한 표 구조 :INDEX-MATCH는 열 삽입이나 삭제에 영향을 받지 않습니다.
• 왼쪽 조회 :VLOOKUP과 달리 INDEX-MATCH는 왼쪽을 볼 수 있습니다.
• 성능 :어떤 경우에는 VLOOKUP을 다시 계산하는 것보다 MATCH가 더 빠르기 때문에 대규모 데이터 세트에서 더 효율적입니다.

결론

XLOOKUP 및 INDEX-MATCH-MATCH와 같은 고급 조회 기술은 Excel의 고급 데이터 처리에 필수적입니다. 이러한 기능은 데이터 변경 사항을 자동으로 업데이트합니다. 이러한 기능은 기존 VLOOKUP을 능가하는 유연성, 정확성 및 성능을 제공하므로 Excel 기술 수준을 높이려는 모든 사람에게 매우 중요합니다.