VLOOKUP을 대체하는 수식 Top 11 -Excel 및 Google 스프레드시트
이 튜토리얼에서는 Excel 및 Google 스프레드시트에서 VLOOKUP을 대체하여 사용할 수 있는 수식을 보여드립니다.
1. XLOOKUP 함수
최신 버전의 Excel을 사용 중이라면 XLOOKUP 함수가 VLOOKUP 함수의 가장 좋은 대안입니다.
XLOOKUP: 왼쪽 값 조회하기
XLOOKUP 함수의 조회 배열과 반환 배열은 별도의 인수입니다. 이를 통해 다음과 같은 이점을 얻을 수 있습니다:
- 열을 삽입하거나 삭제할 때 수식을 조정할 필요가 없습니다.
- 조회 열과 반환 열이 인접하지 않은 경우 여러 열 범위/배열을 입력할 필요가 없습니다. 처리할 데이터의 양이 줄어듭니다.
- 왼쪽에 입력된 값을 조회할 수 있습니다 (아래 예제를 참고하세요):
=XLOOKUP(F3,D3:D7,C3:C7)
XLOOKUP: 더 많은 열의 값들 반환하기
XLOOKUP 함수의 기능 중 하나는 둘 이상의 열을 반환하는 것입니다.
=XLOOKUP(F3,B3:B7,C3:D7)
참고: 반환 배열(열)은 연속적이어야 합니다.
XLOOKUP: #N/A 오류 처리하기
N/A 오류를 처리하기 위해 IFERROR 또는 IFNA 함수를 사용해야 하는 VLOOKUP 함수와 달리, XLOOKUP 함수에는 #N/A 오류 처리기가 4번째 인수(if_not_found)로 내장되어 있습니다.
=XLOOKUP(E3,B3:B7,C3:C7,"발견된 항목 없음")
XLOOKUP: Match 모드 옵션
XLOOKUP 함수의 또 다른 큰 특징은 match_mode(5번째 인수)를 선택할 수 있다는 것입니다: 0 – 정확히 일치, -1 – 정확히 일치 또는 그 다음으로 작은 값, 1 – 정확히 일치 또는 다음으로 큰 항목, 2 – 와일드카드 문자 일치.
=XLOOKUP(E3,B3:B7,C3:C7,"할인 없음",-1)
참고: 데이터를 정렬해야 하는 VLOOKUP, LOOKUP 및 MATCH 함수의 근사치 일치와 달리 XLOOKUP 함수의 match_mode는 기본적으로 데이터 정렬이 필요하지 않습니다(예제 참조).
XLOOKUP: 마지막 일치 항목 조회하기
이전 섹션의 match_mode 예제에서는 search_mode(6번째 인수)의 기본값이 선형 검색(첫 번째에서 마지막까지)이기 때문에 정렬된 데이터 세트가 필요하지 않았습니다.
조회의 검색 프로세스(선형 검색 및 이진 검색)를 선택할 수 있으며, 이 중 하나는 마지막 일치 항목을 쉽게 반환할 수 있게 하는 ‘마지막 항목에서 첫 번째로 검색’ 옵션(즉, -1)입니다.
=XLOOKUP(E3,B3:B7,C3:C7,,,-1)
XLOOKUP: 이진검색과 동적 배열
VLOOKUP 함수 또는 INDEX-MATCH 수식과 비교하여 XLOOKUP의 가장 중요한 업그레이드는 이진 검색 프로세스를 통해 정확히 일치하는 항목을 찾을 수 있게 된 것입니다. 마지막 인수인 search_mode를 2(오름차순) 또는 -2(내림차순)로 설정하기만 하면 정확히 일치하면서 이진 검색의 속도를 활용할 수 있습니다.
백만 개의 데이터를 조회하는 예제를 살펴보겠습니다:
=XLOOKUP(C3:C1000002,B3:B1000002,B3:B1000002,,,2)
참고: 기본적으로 match_mode 인수는 0이며, 완전히 일치하는 것을 의미합니다.
바이너리 검색을 사용하면 이러한 조회를 5초 이내에 수행할 수 있습니다. 또 다른 한 가지는 XLOOKUP 함수를 동적 배열 수식으로 변환할 수 있다는 것입니다. 이를 수행하기 위한 방법은 조회 값에 배열 값을 입력하는 것입니다(예: C3:C1000002).
2. INDEX-MATCH
INDEX-MATCH 수식은 이전 버전의 Excel에서 VLOOKUP 함수에 대한 가장 잘 알려진 대안입니다.
INDEX-MATCH: 왼쪽 값 조회하기
XLOOKUP 함수와 마찬가지로 조회 열과 반환 열은 INDEX-MATCH 수식에서 분리되어 있습니다. 조회 열은 검색 프로세스를 수행하는 MATCH 함수에 입력되고, 반환 열은 MATCH 함수의 결과에 해당하는 값을 반환하는 INDEX 함수에 입력합니다. INDEX-MATCH 함수는 열 삽입 및 삭제에 대한 자동 조정, 열 배열 입력이 적어서 수식이 더 빠르고 효율적으로 계산될 수 있고 왼쪽 값 조회를 수행할 수 있는 등 XLOOKUP 함수에서 언급한 것과 동일한 이점을 제공합니다(아래 예제 참조).
=INDEX(C3:C7,MATCH(F3,D3:D7,0))
수식을 자세히 살펴보겠습니다:
MATCH 함수
MATCH 함수를 사용하여 조회 대상 배열(예: D3:D7)에서 조회 값(예: 학생 ID)에 해당되는 인덱스 번호를 찾는 것부터 시작해 보겠습니다.
=MATCH(F3,D3:D7,0)
참고: MATCH 함수의 match_type(세 번째 인수)은 검색 유형을 정의합니다. 0은 정확한 일치를 의미하며 1과 -1은 둘 다 근사 일치를 의미합니다.
INDEX 함수
MATCH 함수를 통해 조회 대상 배열의 인덱스 번호를 찾았으므로 이제 INDEX 함수를 사용하여 해당 반환 배열(예: C3:C7)에서 값을 반환할 수 있습니다.
=INDEX(C3:C7,H3)
참고: INDEX 함수는 입력한 배열에서 상대 행과 상대 열(2D 배열의 경우) 좌표를 인수로 하여 해당되는 값을 반환합니다.
두 수식을 결합하면 원래 수식이 됩니다:
=INDEX(C3:C7,MATCH(F3,D3:D7,0))
INDEX-MATCH: 수평 배열에서 검색하기
INDEX-MATCH 수식의 유연성을 보여주는 예제를 보여드리겠습니다. 다음은 조회 배열과 반환 배열의 방향이 반대인 경우입니다:
=INDEX(B3:B7,MATCH(D7,E2:I2,0))
3. HLOOKUP 함수
가로 방향 배열에 대한 검색을 위해 만들어진 HLOOKUP 함수도 있습니다.
=HLOOKUP(I3,C2:G3,2,FALSE)
참고: HLOOKUP 함수는 VLOOKUP 함수와 동일한 방식으로 작동하지만 방향은 반대입니다. 첫 번째 행에서 정확히 일치하는 항목을 검색하고 row_index_num에 해당되는 행에서 해당 값을 반환합니다.
4. OFFSET-MATCH: 동적 열 참조하기
VLOOKUP의 또 다른 대안은 INDEX-MATCH 수식과 유사하게 작동하는 OFFSET-MATCH 수식입니다.
동적 열을 참조하는 예제를 살펴보고 OFFSET이 INDEX-MATCH와 어떤 부분에서 다른지 알아보겠습니다.
=OFFSET(B2,MATCH(F3,B3:B7,0),MATCH(G3,C2:D2,0))
수식을 자세히 살펴보겠습니다:
행과 열 좌표
먼저 MATCH 함수를 사용하여 상대 행과 상대 열 좌표를 구해야 합니다.
OFFSET 함수
INDEX 함수를 사용하여 값을 반환하는 대신 OFFSET 함수로 대체하겠습니다. 기본적으로 OFFSET 함수는 참조 범위(예: B2)에서 정의된 상대 행 및 열 좌표를 사용하여 값을 반환합니다.
=OFFSET(B2,I3,J3)
참고: OFFSET 함수는 휘발성 함수이므로 시트가 다시 계산될 때마다 항상 다시 계산되어 시트의 속도에 영향을 줄 수 있습니다.
모든 함수를 결합하면 원래 수식이 됩니다:
=OFFSET(B2,MATCH(F3,B3:B7,0),MATCH(G3,C2:D2,0))
5. INDIRECT-ADDRESS-MATCH: 동적 열 참조
VLOOKUP에 대한 또 다른 대안은 INDIRECT-ADDRESS-MATCH 수식입니다. 동적 열을 참조하는 예제에 수식을 적용해 보겠습니다.
=INDIRECT(ADDRESS(MATCH(F3,B1:B7,0),MATCH(G3,A2:D2,0)))
수식을 자세히 살펴보겠습니다:
행 좌표
INDIRECT-ADDRESS-MATCH 수식에서는 상대 좌표를 사용하는 INDEX 및 OFFSET 수식과 달리 셀 자체의 행 좌표를 결정해야 합니다.
=MATCH(F3,B1:B7,0)
열 좌표
다음으로 셀의 열 좌표도 결정해야 합니다.
=MATCH(G3,A2:D2,0)
ADDRESS 함수
다음으로 행과 열 좌표를 사용하여 ADDRESS 함수를 사용하여 텍스트 형식의 셀 참조를 반환합니다. 기본적으로 텍스트 셀 참조는 절대 참조 형식입니다(예: $C$4).
=ADDRESS(I3,J3)
INDIRECT 함수
마지막으로 INDIRECT 함수를 사용하여 텍스트 셀 참조를 실제 셀 참조로 변환합니다.
=INDIRECT(K3)
참고: INDIRECT 함수는 휘발성 함수이므로 시트가 다시 계산될 때마다 항상 다시 계산되므로 시트 속도에 영향을 줄 수 있습니다.
모든 함수를 결합하면 원래의 수식이 됩니다:
=INDIRECT(ADDRESS(MATCH(F3,B1:B7,0),MATCH(G3,A2:D2,0)))
6. LOOKUP 함수: 마치막 일치 항목 반환하기
조회 배열 내에 중복 항목이 있는 경우, VLOOKUP 함수를 사용할 경우 마지막 일치 항목을 반환하기가 어렵습니다. XLOOKUP 함수를 사용하는 것이 가장 좋지만, 불가할 경우(최신 버전의 Excel이 아닐 경우) LOOKUP 함수를 사용하는 것이 가장 좋은 대안입니다.
=LOOKUP(2,1/(B3:B7=E3),C3:C7)
수식을 자세히 살펴보겠습니다:
조회 조건
먼저 조회 열의 값(예: B3:B7)을 조회 값(예: E3)과 비교해 보겠습니다.
=B3=$E$3
불리언 값의 역수
다음으로 불리언 값의 역수를 구합니다. 여기서 TRUE는 1이고 FALSE는 0입니다.
=1/G3
역수 배열은 오류를 무시하는 LOOKUP 함수의 조회 배열로 사용됩니다. 따라서 실제로는 1의 배열을 갖게 됩니다.
LOOKUP 함수
LOOKUP 함수는 데이터가 오름차순으로 정렬되어 있다는 가정 하에 근사 일치 항목만 검색 할 수 있으며, 이는 조회 배열에서 조회 값보다 작거나 같은 항목 중 가장 큰 값을 찾는 것을 의미합니다.
조회 값으로 2를 사용하여 1 배열에서 근사 일치하는 값을 찾도록 하였습니다. 예제처럼 중복이 있는 경우 마지막 항목의 위치가 반환됩니다.
=LOOKUP(2,H3:H7,C3:C7)
모든 수식을 결합하면 원래 수식이 됩니다:
=LOOKUP(2,1/(B3:B7=E3),C3:C7)
7. FILTER 함수: 모든 중복 값 조회하기
모든 중복 항목을 조회하려는 경우, 최신 버전의 Excel에서는 FILTER 함수를 사용할 수 있습니다. 이 함수는 간단하며 도우미 열을 추가할 필요가 없습니다.
=FILTER(C3:C7,B3:B7=E2)
참고: 첫 번째 인수는 필터링할 배열(예: C3:C7)이고, 두 번째 인수는 필터 기준(예: B3:B7=E2)입니다.
8. FILTER-INDEX: n번째 일치 항목 조회하기
모든 중복 항목을 반환하는 대신 FILTER-INDEX 수식을 사용하여 n번째 일치 항목만 반환할 수 있습니다. 이 방법은 고유 ID – VLOOKUP 수식보다 더 편리한 대안입니다(VLOOKUP으로 중복 값 조회하기 튜토리얼 참조).
=INDEX(FILTER(C3:C7,B3:B7=E3),F3)
수식을 자세히 살펴보겠습니다:
FILTER 함수
먼저 FILTER 함수를 사용하여 모든 중복 항목을 반환하겠습니다. 필터 조건으로 조회 배열 = 조회 값을 사용했습니다.
=FILTER(C3:C7,B3:B7=E3)
INDEX 함수와 N번째 일치 항목
다음으로 INDEX 함수를 사용하여 FILTER 함수의 결과에서 n번째 일치 항목을 반환합니다.
=INDEX(H3:H5,F3)
모든 함수를 결합하면 원래의 수식이 됩니다:
=INDEX(FILTER(C3:C7,B3:B7=E3),F3)
9. SUMPRODUCT: 숫자 조회하기
숫자만 조회하는 경우, VLOOKUP 함수의 대안으로 SUMPRODUCT 함수를 사용할 수 있습니다. SUMPRODUCT 함수의 장점은 여러 기준을 편리하게 적용할 수 있다는 것입니다. 배열 수식을 사용할 수 없는 Excel 버전 사용자의 경우 배열을 처리하기 위해 Ctrl+Shift+Enter를 사용해야 합니다. 아래 예제를 살펴보겠습니다:
=SUMPRODUCT((B3:B7=F3)*(C3:C7=G3)*D3:D7)
수식을 자세히 살펴보겠습니다:
조건 1
먼저 해당 열에 적절한 조건을 적용해 보겠습니다. 첫 번째 조건은 다음과 같습니다:
=B3=$E$3
조건 2
두 번째 조건은 다음과 같습니다:
=C3=$G$3
AND 논리함수
다음으로 두 개의 불리언 배열을 곱하여 두 조건이 모두 충족되는지 확인합니다(TRUE = 1, FALSE = 0).
=I3*J3
참고: 불리언 배열 두개를 곱하는 것은 AND 함수를 사용하는 것과 동일합니다. 두 조건이 모두 만족되면 결과는 1이 되고, 조건 중 하나가 FALSE이면 결과는 0이 됩니다.
반환 배열
중복이 없다면 위 과정을 통해 목록에 1 하나가 생성되고 나머지는 0이 됩니다. 이 배열에 반환 배열을 곱하여 결과값을 계산합니다.
=K3*D3
SUMPRODUCT 함수
SUMPRODUCT 함수는 배열 곱셈을 수행한 후 생성된 배열의 합을 구합니다. 0보다 큰 값은 하나만 있고 나머지는 0이므로 SUMPRODUCT 수식의 결과 값은 이전 단계를 통해 계산된 값과 동일합니다.
=SUMPRODUCT(L3:L7)
참고: 불리언 배열을 숫자로 변환하기 위해 SUMPRODUCT 함수를 사용하기 전에 배열을 곱했습니다. SUMPRODUCT 함수는 숫자 계산만 가능하기 때문에 다른 데이터 유형(예: 불리언, 문자열)을 인수로 사용할 수 없습니다.
모든 수식을 결합하면 원래 수식이 됩니다:
=SUMPRODUCT((B3:B7=F3)*(C3:C7=G3)*D3:D7)
10. SUMIF 함수: 숫자 조회하기
SUMPRODUCT 함수 대신 SUMIFS 함수를 사용하여 숫자에 대한 여러 기준을 적용하여 조회를 수행할 수도 있습니다. SUMPRODUCT 함수에 비해 사용하기가 더 간단하고 편리하지만, SUMPRODUCT 함수와 달리 다른 함수의 배열 결과와 같은 배열 입력을 인수로 입력할 수 없습니다. sum_range와 criteria_ranges는 엄밀히 말해 범위입니다.
=SUMIFS(D3:D7,B3:B7,F3,C3:C7,G3)
참고: 첫 번째 인수는 합산할 배열인 sum_range입니다. 다음 인수는 기준 범위와 기준이 페어로 입력됩니다. 기준 개수만큼 인수가 늘어날 수 있습니다.
11. Google 스프레드 시트: QUERY 함수
Google 스프레드시트에는 존재하지 않는 XLOOKUP 함수를 제외하고, 앞서 언급한 모든 함수는 Google 스프레드시트에서 사용할 수 있으며 동일한 방식으로 작동합니다.
이 외에도 Google 스프레드시트에서 사용할 수 있는 더 강력한 대안인 QUERY 함수가 있습니다.
=QUERY(B3:C7,"SELECT C WHERE B='"&E3&"'")
참고: QUERY 함수의 두 번째 인수는 조회, 정렬, 필터링 및 서식 지정과 같은 데이터 조작을 수행하는 데 도움이 되는 쿼리 구문(텍스트 형식)입니다.
수식을 자세히 살펴보겠습니다:
SELECT
SELECT 절은 반환하려는 열을 필터링합니다(예: C열).
=QUERY(B3:C7,"SELECT C")
WHERE
WHERE 절은 행을 필터링합니다. 조회 값은 텍스트이므로 작은따옴표로 묶어야 합니다.
참고: SELECT 절을 제거하면 모든 열이 반환됩니다.
두 절을 결합하면 원래 수식이 됩니다:
=QUERY(B3:C7,"SELECT C WHERE B='"&E3&"'")
