들어가기 전에
Excel에서 조회 값을 기반으로 테이블이나 범위에서 데이터를 찾고 검색하는 방법은 여러 가지가 있습니다. 이런 기능을 수행하기 위한 Excel 함수는 많으며, 그 중 3가지 함수(및 함수 조합)를 소개합니다.
이 글은 아래 기사 내용을 토대로 작성되었습니다만, 필자의 개인 의견이나 추가 자료들이 다수 포함되어 있습니다.
- 원문: The Best Lookup Functions in Excel: Examples, Pros, and Con
- URL: https://www.howtogeek.com/microsoft-excel-lookup-functions-pros-cons/
1. XLOOKUP: VLOOKUP 및 HLOOKUP의 현대적 해석
XLOOKUP 은 아마도 Excel의 모든 조회 함수의 어머니라고 할 수 있습니다. Microsoft에서 이 함수를 개발하는 데 30년 이상이 걸렸다는 점을 고려하면 놀라운 일입니다. 2021년 이전에 출시된 Excel 버전을 사용하는 경우 XLOOKUP 함수를 사용할 수 없습니다.
범위 또는 배열에서 첫 번째 또는 마지막 일치 항목을 기준으로 하나 이상의 항목을 반환합니다. 가로 및 세로 방향으로 작동하고, 왼쪽, 오른쪽, 위 또는 아래에서 항목을 검색하며, 전체 열이나 행을 반환할 수 있으므로 이전 함수인 VLOOKUP 및 HLOOKUP을 완전히 대체합니다. 일치하는 항목이 없을 경우 수식이 반환하는 값을 지정할 수도 있습니다.
XLOOKUP 구문
XLOOKUP에는 6개의 인수가 있습니다.
=XLOOKUP(a, b, c, d, e, f)
- a(필수): 조회 값
- b(필수): 조회 배열
- c(필수): 반환 배열
- d(선택 사항): 조회 값(a)이 조회 배열(b)에서 발견되지 않을 경우 반환할 텍스트
- e(선택 사항): 일치 모드
- 0: 정확한 일치(기본값)
- -1: 정확한 일치 또는 다음으로 작은 항목
- 1: 정확한 일치 또는 다음으로 큰 항목
- 2: 와일드카드 일치
- f(선택 사항): 검색 모드
- 1: 처음부터 끝까지(기본값)
- -1: 마지막에서 처음으로
- 2: b가 오름차순인 이진 검색
- -2: b가 내림차순인 이진 검색
인수 a 와 c는 수직 조회의 경우 높이가 같아야 하고, 수평 조회의 경우 너비가 같아야 합니다.
XLOOKUP 함수의 실제 활용
XLOOKUP은 단일 기준에 따라 단일 값을 반환할 수 있습니다. H3 셀의 수식은 H1 셀의 ID에 따라 플레이어의 점수를 반환합니다.
=XLOOKUP(H1, Players[ID], Players[Score], "No ID")
이 경우 기본 옵션(정확한 일치 및 위에서 아래로 검색)이 필요하므로 인수 e(일치 모드) 또는 f(검색 모드)는 입력할 필요가 없습니다.
XLOOKUP은 인접한 여러 셀의 결과를 반환하는 데에도 사용할 수 있습니다. 여기서 G4 셀의 수식은 ID를 입력하면 선수의 성별, 국가, 팀, 점수를 반환합니다. 조회 값(인수 c )은 첫 번째 열, 콜론, 마지막 열로 구성됩니다.
=XLOOKUP(H1, Players[ID], Players[[Gender]:[Score]])
위 예처럼 반환 배열(인수 c )이 두 개 이상의 셀인 경우, XLOOKUP 함수는 동적 배열 함수 로 전환됩니다 . 즉, 수식을 입력한 셀의 결과가 인접 셀로 넘어오므로, 수식을 입력하기 전에 해당 셀을 비워두어야 합니다. 그렇지 않으면 #SPILL! 오류가 발생합니다.
XLOOKUP 함수를 사용하여 인접하지 않은 열이나 행의 값을 반환하려면 반환 배열 인수에 FILTER 함수를 중첩해야 합니다. 이때 중괄호 안에 0과 1을 사용하여 Excel에서 반환할 열을 지정합니다. 이 수식은 H1 셀에서 ID를 조회하여 해당하는 Country(세 번째 열)와 Score(다섯 번째 열)를 반환합니다.
=XLOOKUP(H1, Players[ID], FILTER(Players, {0,0,1,0,1}))
위의 수식은 열 인덱스 번호를 사용하므로 테이블에 열이 추가되거나 제거 되면 올바른 반환 배열을 선택하도록 공식을 조정해야 합니다.
마지막으로, XLOOKUP을 논리 연산자와 결합하여 여러 기준에 따라 값을 반환할 수 있습니다. 이 수식은 조회 배열(인수 b)의 각 기준이 참인지 여부를 테스트하여, 참이면 1을, 거짓이면 0을 반환합니다. 모든 기준이 참이면 첫 번째 일치 항목이 반환됩니다. 즉, 조회 배열 계산 결과가 조회 값(1)과 같습니다.
=XLOOKUP(1, (Players[Gender]=H1) * (Players[Country]=H2), Players[ID])
점수 열은 내림차순으로 정렬되므로 결과는 가장 높은 점수를 받은 캐나다 여성의 ID입니다.
가장 낮은 점수를 받은 캐나다 여성을 찾으려면 점수 열의 정렬 순서를 반대로 바꾸거나 검색 모드(인수 f )에 -1을 입력합니다.
요약: XLOOKUP의 장단점
XLOOKUP 함수의 장점과 단점은 다음과 같습니다.
| 장점 | 단점 |
| 수직 및 수평 데이터 세트를 처리합니다. | 2021년 이전에 출시된 Excel 버전과의 하위 호환성이 없습니다. |
| 반환 배열은 조회 배열의 왼쪽, 오른쪽, 위 또는 아래에 있을 수 있습니다. | 단 하나의 일치 항목(첫 번째 또는 마지막)만 반환합니다. |
| 단일 결과나 동적 배열을 반환할 수 있습니다. | FILTER 함수와 함께 사용하지 않으면 인접하지 않은 열이나 행을 반환할 수 없습니다. |
| 하나 또는 여러 개의 조회 값을 사용합니다. | 동적 배열을 반환하는 경우 서식이 지정된 Excel 표에서 사용할 수 없습니다. |
| 유연한 매치 유형과 검색 모드를 지원합니다. | |
| 기본적으로 오류를 처리할 수 있습니다. | |
| 부분 일치에 대한 와일드카드 검색을 지원합니다. | |
| 중첩하여 양방향 조회를 실행할 수 있습니다. |
2. INDEX With XMATCH: INDEX With MATCH보다 더 강력한 대안
수년간 Excel을 사용해 온 많은 사람들이 여전히 INDEX-MATCH 함수 조합을 사용하여 조회를 수행합니다. VLOOKUP이나 XLOOKUP보다 더 유연하기 때문입니다. 하지만 INDEX와 MATCH의 업데이트된 버전인 XMATCH를 함께 사용하면 더 많은 옵션을 사용할 수 있습니다.
XMATCH를 사용한 INDEX는 범위 또는 배열에서 처음 또는 마지막 일치 항목을 기준으로 항목을 반환합니다. XLOOKUP과 마찬가지로, 세로 및 가로 데이터 집합을 지원하고, 모든 방향으로 검색할 수 있으며, 전체 열 또는 행을 반환하는 데 사용할 수 있습니다.
하지만 XMATCH는 MATCH보다 최신 버전이기 때문에 2021년 이후에 출시된 웹용 Excel이나 데스크톱 버전의 Excel을 사용하는 경우에만 사용할 수 있습니다. 이 두 함수는 일반적으로 함께 사용되는데, 그 이유는 INDEX가 조회 열을 식별하고 XMATCH가 조회 행을 식별하기 때문입니다.
INDEX-MATCH 구문
이 함수 조합에 대한 가장 일반적인 사용 구문은 다음과 같습니다.
=INDEX(a, XMATCH(b, c, d, e), f)
- a(필수): 반환 값이 있는 테이블의 배열 또는 이름
- b(필수): 조회 값
- c(필수): 조회 값이 포함된 열
- d(선택 사항): 일치 모드
- 0: 정확한 일치(기본값)
- -1: 정확한 일치 또는 다음으로 가장 작은 항목
- 1: 정확한 일치 또는 다음으로 가장 큰 항목
- 2: 와일드카드 일치
- 3: 정규식 일치
- e(선택 사항): 검색 모드
- 1: 처음부터 끝까지(기본값)
- -1: 마지막에서 처음으로
- 2: b가 오름차순인 이진 검색
- -2: b 가 내림차순인 이진 검색
- f(필수): 반환 값의 열 번호
XMATCH 인수(b, c, d, e)는 INDEX 함수가 배열이나 표의 어느 행을 찾을지 알려주고, INDEX 함수의 마지막 인수(f)는 열을 식별합니다. 이 두 인수는 Excel이 올바른 값을 반환하기 위해 어느 셀을 찾을지 알려줍니다.
INDEX-XMATCH 함수 조합의 실제 사용
INDEX와 XMATCH를 함께 사용하면 단일 기준에 따라 단일 값을 반환할 수 있습니다. 이 예에서는 INDEX와 XMATCH를 함께 사용하여 H1 셀에 ID가 있는 플레이어의 점수(Players 테이블의 5열)를 반환합니다.
=INDEX(Players, XMATCH(H1, Players[ID]), 5)
구문이 처음에는 꽤 복잡해 보이지만 위에서 아래로 검색하여 정확한 일치 항목을 반환하려는 경우 인수 두 개를 생략할 수 있습니다.
일치하는 값이 없으면 Excel에서 #N/A 오류가 반환됩니다. 이를 방지하려면 데이터 유효성 검사를 사용하여 조회 값에 대한 옵션 드롭다운 목록을 만듭니다. 또는 IFERROR 수식 안에 전체 수식을 포함시킵니다.
=IFERROR(INDEX(Players, XMATCH(H1,Players[ID]), 5), "No match")
위 예제의 문제점은 열 번호가 수식에 하드코딩되어 있다는 것입니다. 대신, 두 번째 XMATCH 인수 집합을 임베드하여 양방향 조회를 통해 이 값을 반환할 수 있습니다.
=INDEX(Players, XMATCH(H1, Players[ID]), XMATCH(G3, Players[#Headers]))
여기서 열 번호는 G3의 변수를 Players 테이블의 열 머리글과 일치시켜 식별됩니다.
INDEX와 XMATCH를 함께 사용하면 논리 연산자를 사용하여 여러 기준을 결합하여 값을 반환할 수도 있습니다. E열을 내림차순으로 정렬한 후, 다음 수식을 사용하여 C팀에서 가장 높은 점수를 받은 여성의 ID를 반환할 수 있습니다.
=INDEX(Players[ID], XMATCH(1, (Players[Gender]=H1) * (Players[Team]=H2)))
팀 C에서 가장 낮은 점수를 받은 여성을 찾으려면 열 E의 정렬 순서를 반대로 하거나 검색 모드(인수 e )에 -1을 입력합니다.
요약: INDEX와 XMATCH의 장단점
다음은 이 함수 조합의 장단점을 요약한 것입니다.
| INDEX / XMATCH의 장점 | INDEX / XMATCH 단점 |
| 수직 및 수평 데이터 세트를 처리합니다. | 2021년 이전에 출시된 Excel 버전과의 하위 호환성이 없으며 Excel 모바일 앱에서는 사용할 수 없습니다. |
| 반환 배열은 조회 배열의 왼쪽, 오른쪽, 위 또는 아래에 있을 수 있습니다. | 단일 결과만 반환합니다. |
| 하나 또는 여러 개의 조회 값을 사용합니다. | 단 하나의 일치 항목(첫 번째 또는 마지막)만 반환합니다. |
| 유연한 매치 유형과 검색 모드를 지원합니다. | IFERROR 함수와 함께 사용하지 않는 한, 일치하는 항목이 없으면 대체 값을 지정할 수 없습니다. |
| XMATCH는 중첩되어 양방향 조회를 수행할 수 있습니다. | 두 가지 기능을 동시에 사용하는 것은 단일 기능 조회보다 학습 곡선이 더 가파릅니다. |
| 선택적 인수에 대한 기본값은 MATCH보다 XMATCH에서 더 논리적입니다. | |
| 동적 배열을 반환하지 않으므로 Excel 표에서 사용할 수 있습니다. | |
| 부분 일치에 대한 와일드카드 검색을 지원합니다. |
3. FILTER: 일치하는 모든 값을 반환하는 간편한 함수
XLOOKUP과 INDEX 함수와 XMATCH 함수는 단일 일치 항목을 반환하는 반면, Excel의 FILTER 함수는 모든 일치 항목을 반환합니다. 따라서 다른 함수보다 FILTER 함수를 선택하는 것이 좋습니다. 단, Excel 2021 이상, 웹용 Excel 또는 Excel 모바일 앱을 사용하는 경우에만 이 함수를 사용할 수 있습니다.
FILTER 함수 구문
FILTER 함수의 작동 방식은 다음과 같습니다.
=FILTER(a, b, c)
- a(필수): 반환하려는 값이 포함된 배열
- b(필수): 필터를 결정하는 포함 기준
- c(선택 사항): 포함 기준(b)과 일치하는 값이 없는 경우 반환할 텍스트
인수 a와 b는 서로 같은 크기의 배열을 참조해야 합니다.
FILTER 함수의 실제 사용
FILTER 함수를 사용하면 단일 기준에 따라 열에서 배열을 반환할 수 있습니다. H3 셀에 다음 수식을 입력하면 H1 셀의 값과 일치하는 국가를 가진 모든 플레이어의 ID가 반환되며, 일치하는 항목이 없는 경우 오류 대신 "일치하지 않음"이 반환됩니다.
=FILTER(Players[ID], Players[Country]=H1, "No match")
필터링된 값은 소스 데이터에 나타나는 순서대로 반환됩니다. FILTER는 동적 배열 함수입니다. 즉, 수식이 포함된 셀의 결과가 주변 셀로 넘어갑니다. 따라서 해당 셀을 비워야 하며, 그렇지 않으면 #SPILL! 오류가 발생합니다.
위 예에서는 Players 테이블의 ID 열만 반환 배열로 선택되었습니다. 하지만 FILTER 함수를 사용하여 모든 열에서 해당 데이터를 반환할 수도 있습니다. G3부터 K3 셀까지 열 머리글을 직접 입력한 후, G4 셀에 다음과 같이 입력했습니다.
=FILTER(Players, Players[Country]=H1, "No match")
여기서 Players (인수 a )는 테이블 이름이며, 이는 필터링된 결과에 모든 열이 반환됨을 의미합니다.
인접하지 않은 열에서 데이터를 반환하는 방법은 여러 가지가 있지만 가장 간단한 방법은 반환하려는 각 열에 대해 FILTER 함수를 반복하는 것입니다.
마지막으로, FILTER를 사용하여 논리 연산자를 사용하여 두 개 이상의 조건에 맞는 값을 반환할 수 있습니다. 다음 수식은 C팀의 모든 여성에 대한 데이터를 반환합니다.
=FILTER(Players, (Players[Gender]=H1) * (Players[Team]=H2), "No match")
요약: FILTER 함수의 장단점
Excel의 FILTER 함수의 장점과 단점을 정리하면 다음과 같습니다.
| 장점 | 단점 |
| 일치하는 모든 값을 반환합니다. | 2021년 이전에 출시된 Excel 버전에서는 사용할 수 없습니다. |
| 반환 배열은 필터링된 배열의 왼쪽, 오른쪽, 위 또는 아래에 있을 수 있습니다. | 동적 배열을 반환합니다. 즉, Excel 표에서 사용할 수 없습니다. |
| 하나 또는 여러 개의 필터링 기준으로 작동합니다. | 소스 셀이 비어 있거나 null이면 0을 반환합니다. |
| 검색이나 일치 유형이나 모드가 필요하지 않으므로 구문이 간단합니다. | |
| 수직 및 수평 데이터 세트를 처리합니다. | |
| 일치하지 않는 값을 지정할 수 있습니다. |
마치며
최고의 조회 함수(또는 함수 조합) 3가지에 대해 소개했습니다. 엄밀히 말하면 조회 함수는 아니지만 Excel의 CHOOSECOLS와 CHOOSEROWS 함수에 대해서도 알아두시기 바랍니다. 특정 행이나 열을 빠르게 추출하려는 경우 편리합니다.
'Excel' 카테고리의 다른 글
| [강의 예고] 오타 내고 싶어도 못 냅니다. 엑셀 입력 실수 '원천 봉쇄' 수식 (0) | 2025.12.07 |
|---|---|
| 번개처럼 빠른 조회를 할 수 있는 4가지 Excel 함수(또는 수식) (0) | 2025.12.04 |
| [강의 예고] 엑셀 수식 안에 "데이터 보관함" 넣는 방법 (0) | 2025.11.30 |
| Excel 표 크기가 자동으로 조정되도록 하는 방법 (0) | 2025.11.28 |
| 엑셀 수식의 복잡성을 절반으로 줄여주는 트릭 (0) | 2025.11.26 |
