번개처럼 빠른 조회를 할 수 있는 4가지 Excel 함수(또는 수식)

들어가기 전에

대용량 데이터로 작업을 할 때 느린 조회 수식은 생산성을 저하시킬 수 있습니다. 방대한 스프레드시트도 손쉽게 처리할 수 있는 초고속 Excel 수식을 만드는 방법을 소개합니다.

권현욱(엑셀러) | 아이엑셀러 닷컴 대표 · Microsoft MVP · 엑셀 솔루션 프로바이더 · 작가

이미지: 아이엑셀러 닷컴

---

이 글은 아래 기사 내용을 토대로 작성되었습니다만, 필자의 개인 의견이나 추가 자료들이 다수 포함되어 있습니다.

• 원문: I Build Lightning-Fast Lookup Formulas With These Excel Functions
• URL: https://www.makeuseof.com/excel-fast-lookup-functions/

---

1. XLOOKUP

오랫동안 VLOOKUP을 사용해 왔지만, 복잡한 데이터에는 제약이 있었습니다. 반면 XLOOKUP은 이러한 문제 대부분을 해결하면서 더 나은 성능과 유연성을 제공합니다. XLOOKUP을 사용하면 조회 열과 반환 열을 각각 따로 선택할 수 있습니다. 즉, 데이터를 다시 정렬하지 않고도 조회 열의 왼쪽을 볼 수 있습니다. 이것이 XLOOKUP이 VLOOKUP보다 나은 이유 중 하나입니다.

XLOOKUP의 구문은 다음과 같습니다.

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

 

• lookup_value: 찾는 값
• lookup_array: 검색할 범위 또는 배열
• return_array: 검색하려는 값을 보관

 

선택적 매개변수를 사용하면 더 많은 제어가 가능합니다.

• [if_not_found]: #N/A와 같은 일반적인 Excel 오류를 표시하는 대신 사용자 지정 오류 메시지 지정(IFERROR가 필요 없음)
• [match_mode]: 정확한 일치, 와일드카드 또는 대략적인 일치를 제공
• [search_mode]: Excel에서 처음부터 끝까지 검색할지, 마지막부터 첫 번째까지 검색할지 결정합니다.

 

사무실 관련 예를 들어 보겠습니다. 직원의 이메일 주소가 있고 전체 이름을 찾으려면 왼쪽을 찾아야 합니다. VLOOKUP으로는 "전체 이름" 열이 "광고 이메일" 열 왼쪽에 있기 때문에 쉽게 찾을 수 없습니다. 하지만 XLOOKUP을 사용하면 가능합니다.

 

예를 들어, J2 셀에 uriah.bridges@bilearner.com이라는 이메일 주소가 있는 경우 다음 수식을 사용하여 전체 이름을 찾을 수 있습니다.

=XLOOKUP(J2, J:J, D:D, "Employee Not Found")

 

이 수식은 J열에서 이메일을 검색하여 D열에서 해당 이름을 반환합니다. 이메일을 찾지 못하면 #N/A 오류 대신 "Employee Not Found"를 반환합니다. 이러한 기본 제공 오류 처리 덕분에 수식이 더욱 강력하고 사용자 친화적으로 작동합니다.

 

이미지: muo

 

2 LET과 XLOOKUP 결합

수식은 때때로 길고 복잡해질 수 있는데, 특히 하나의 수식 내에서 동일한 계산을 여러 번 수행해야 할 때 더욱 그렇습니다. 이렇게 되면 수식을 읽기 어려울 뿐만 아니라 Excel에서 동일한 결과를 반복적으로 계산해야 하므로 성능에도 악영향을 미칩니다.

이 문제를 해결하는 방법은 LET 함수를 사용하는 것입니다. 이 함수를 사용하면 수식 안에 변수를 직접 선언할 수 있습니다. 한 번 계산하고 이름을 지정한 후, 결과가 필요할 때마다 해당 이름을 사용하면 됩니다. 구문은 간단합니다.

=LET(name1, name_value1, [name2, name_value2], calculation)

 

• name1: 첫 번째 계산의 이름
• name_value1: 계산 자체
• calculation: 방금 정의한 이름을 사용하는 최종 수식

 

직원의 현재 등급에 따라 상태를 할당하는 작업이 있다고 가정해 보겠습니다. 직원 ID로 찾은 등급이 5이면 "최상위" 등급이고, 1이면 "관심 목록" 등급이며, 그렇지 않으면 "일반" 등급입니다.

 

LET이 없다면 다음과 같이 XLOOKUP 함수를 두 번 작성해야 합니다.

=IF(XLOOKUP(A2, A:A, AC:AC)=5, "최상위", IF(XLOOKUP(A2, A:A, AC:AC)=1, "관심목록", "표준"))

 

하지만 LET을 사용하면 수식이 훨씬 깔끔해집니다. XLOOKUP 함수를 한 번만 계산하여 "rating"이라는 이름에 할당합니다.

=LET(등급, XLOOKUP(A2, A:A, AC:AC), IF(등급=5, "최상위", IF(등급=1, "관심종목", "표준")))

 

이 수식은 A2 셀에서 직원 ID를 조회하고 , 열 AC 에서 숫자 등급을 검색한 다음 성과를 분류합니다.

 

이미지: muo

 

LET 함수는 평점 조회를 한 번만 저장하므로 Excel에서 중첩된 IF 문에서 동일한 XLOOKUP 계산을 반복하지 않습니다. 이 수식은 읽고 디버깅하기가 훨씬 쉽습니다.

 

3. INDEX와 MATCH 조합

XLOOKUP이 등장하기 전에는 INDEX와 MATCH가 빠른 조회를 위한 유일한 방법이었습니다. 지금도 이 조합은 최신 함수보다 훨씬 빠른 속도를 자랑합니다. 수만 개의 행이 있는 스프레드시트에서는 이 조합이 전체 테이블이 아닌 참조하는 특정 열만 처리하기 때문에 눈에 띄게 빠릅니다.

두 함수 모두 자체적으로 완전한 조회를 수행하지 않습니다. MATCH 함수는 한 가지 작업에 유용합니다. 열 내에서 값의 상대적 위치(행 번호)를 찾는 것입니다. 그런 다음 INDEX 함수는 해당 번호를 사용하여 다른 열에서 해당 값을 가져옵니다.

먼저 MATCH 구문을 살펴보겠습니다. 이 구문은 데이터가 어디에 있는지 알려줍니다.

=MATCH(lookup_value, lookup_array, [match_type])

 

• lookup_value: 찾으려는 값
• lookup_array: 검색할 단일 열 또는 행
• [match_type]: 정확한 일치를 원하면 0을 사용

 

다음은 MATCH가 제공하는 위치를 기반으로 원하는 것을 검색하는 INDEX 구문입니다.

=INDEX(array, row_num, [column_num])

 

• array: 값을 검색할 셀 범위 또는 배열
• row_num: 값을 검색할 행 번호

 

A2 셀의 ID에 해당하는 직원의 급여 구역을 찾아야 한다고 가정해 보겠습니다. 다음 수식을 사용하면 됩니다.

=INDEX(F:F, MATCH(A2, A:A, 0))

 

이 예에서 MATCH 부분은 먼저 A2 셀에 있는 직원 ID의 행 번호를 찾습니다. 그런 다음 INDEX 부분은 해당 번호를 가져와 급여 구역 열(F열)의 해당 행에서 값을 반환합니다. 이는 가볍고 효율적인 방법입니다.

 

이미지: muo

 

4. FILTER

XLOOKUP을 포함한 표준 조회에는 근본적인 한계가 있습니다. 바로 검색을 중단하고 첫 번째로 일치하는 항목을 반환한다는 것입니다. 하지만 일치하는 모든 레코드 목록이 필요한 경우는 어떻게 해야 할까요? Excel 2021 이전 버전에서는 복잡한 배열 수식이 필요했습니다. 이제는 FILTER 함수를 사용합니다. 조건에 맞는 모든 레코드를 추출해 줍니다.

Excel의 FILTER 함수를 한 번도 사용해 본 적이 없다면 정말 중요한 것을 놓치고 있는 것입니다. 이 함수는 수식 아래 셀에 값 배열을 동적으로 반환합니다. 즉, 더 이상 결과 개수를 추측할 필요가 없습니다. 출력 크기를 자동으로 처리해 주므로 작업 시간을 크게 절약할 수 있습니다.

 

FILTER 함수의 구문은 다음과 같습니다.

=FILTER(array, include, [if_empty])

 

• array: 결과를 얻고자 하는 전체 데이터 범위. 열은 하나일 수도 있고 여러 개일 수도 있음
• include: 논리적 테스트. 셀 범위 뒤에 조건이 붙는 방식
• [if_empty]: 결과가 없을 때 표시할 내용에 대한 선택적 인수

 

예를 들어, Perter Oneill이라는 이름의 관리자가 직속 부하직원 전체 목록을 원한다고 가정해 보겠습니다. FILTER 함수를 사용하면 여러 번 개별적으로 조회할 필요 없이 단 하나의 수식만으로 전체 목록을 조회할 수 있습니다.

이 문제를 해결하는 수식은 다음과 같습니다.

=FILTER(D:F, I:I="Peter Oneill", "No Employees Found")

 

이 수식은 Excel에서 I열의 값이 "Peter O'Neill"인 모든 행에 대해 D열부터 F열까지의 데이터를 반환하도록 지시합니다.

 

이미지: muo

 

결과는 자동으로 아래 셀에 입력되어 깔끔하고 역동적인 표를 만듭니다. 이러한 역동적인 동작 덕분에 이 Excel 팁을 사용하면 표 크기를 조정하는 번거로움을 없앨 수 있습니다.

 

마치며

이상의 4가지 접근 방식은 마주치게 되는 모든 조회 시나리오에 적용할 수 있습니다. XLOOKUP은 VLOOKUP의 최신 대안입니다. 최적화를 위해 LET 조합을 사용하고, 속도를 위해 INDEX/MATCH를 사용하며, 여러 결과를 얻으려면 FILTER를 사용하세요.

멤버십 안내 "최고" 가성비로 "최신" Office 활용 정보를 보내드립니다.

추천 도서 심혈을 기울여 집필한 책들입니다. 자신있게 추천합니다.

강의 & 개발 최고 전문가의 실무 노하우를 아낌 없이 전수해 드립니다.

---