엑셀 Lookup함수 - 조회

Excel Lookup 조회

Lookup 조회 - 데이터 테이블에서 지정된 값을 검색합니다.

Lookup Value 조회 값 - 검색할 값입니다.

Return Value 반환 값(일치하는 값 또는 일치) - 조회 값과 동일한 위치에 있지만 다른 열 또는 행에 있는 값(수직 또는 수평 조회를 수행하는지 여부에 따라 다름)입니다.

Lookup Tabel 조회 테이블. 컴퓨터 과학에서 조회 테이블은 일반적으로 입력 값을 출력 값에 매핑하는 데 사용되는 데이터 배열입니다. 

기본 테이블(마스터 테이블) - 일치하는 값을 끌어오는 테이블입니다.

조회 테이블과 기본 테이블은 구조와 크기가 다를 수 있지만 수직 또는 수평 조회를 수행할지 여부에 따라 항상 하나 이상의 공통 고유 식별자(예: 동일한 데이터를 보유하는 열 또는 행)를 포함해야 합니다.

다음 스크린샷은 아래의 많은 예제에서 사용될 샘플 조회 테이블을 보여 줍니다.

 

Excel Lookup 조회 함수

 

LOOKUP 함수

Excel의 LOOKUP 함수는 가장 간단한 유형의 수직 및 수평 조회를 수행 할 수 있습니다.

장점 : 사용하기 쉽습니다.

단점: 제한된 기능, 정렬되지 않은 데이터로 작업할 수 없음(조회 열/행을 오름차순으로 정렬해야 함).

 

VLOOKUP 함수

열에서 세로 조회를 수행하도록 특별히 설계된 LOOKUP 함수의 개선된 버전입니다.

장점 : 비교적 사용하기 쉽고 정확하고 대략적인 일치로 작업 할 수 있습니다.

단점: 왼쪽을 볼 수 없음, 조회 테이블에 열을 삽입하거나 조회 테이블에서 제거할 때 작동을 중지, 조회 값은 255자를 초과할 수 없음, 큰 데이터 세트에서 많은 처리 능력이 필요합니다.

 

HLOOKUP 함수

조회 테이블의 첫 번째 행에서 값을 검색하고 다른 행에서 동일한 위치에 있는 값을 반환하는 VLOOKUP의 가로 버전입니다.

장점 : 사용하기 쉽고 정확하고 대략적인 일치 항목을 반환 할 수 있습니다.

단점 : 조회 테이블의 맨 위 행에서만 검색 할 수 있으며 행 삽입 또는 삭제의 영향을받으며 조회 값은 255 자 미만이어야합니다.

 

VLOOKUP MATCH / HLOOKUP MATCH 

MATCH에서 만든 동적 열 또는 행 참조를 사용하면 이 Excel 조회 수식이 데이터 집합의 변경 내용에 영향을 받지 않습니다. 즉, MATCH의 도움을 받아 VLOOKUP 및 HLOOKUP 함수는 조회 테이블에 삽입되거나 조회 테이블에서 삭제된 열/행 수에 관계없이 올바른 값을 반환할 수 있습니다.

세로 조회 수식

VLOOKUP(lookup_value, lookup_table, MATCH(return_column_name, column_headers, 0), FALSE)

수평 조회 수식

HLOOKUP(lookup_value, lookup_table, MATCH(return_row_name, row_headers, 0), FALSE)

장점 : 일반 Hlookup 및 Vlookup 수식에 비해 데이터 삽입 또는 삭제에 영향을받지 않습니다.

단점 : 매우 유연하지 않고 특정 데이터 구조가 필요하며 (MATCH 함수에 제공된 조회 값은 반환 열의 이름과 정확히 같아야 함) 255자를 초과하는 조회 값으로 작업 할 수 없습니다.

 

오프셋 매치

 

V-Lookup 수식

OFFSET(lookup_table, MATCH(lookup_value, OFFSET(lookup_table, 0, n, ROWS(lookup_table), 1) ,0) -1, m, 1, 1)

 

• n - 조회 컬럼 오프셋, 즉 시작점에서 조회 컬럼으로 이동할 컬럼 수입니다.
• m - 반환 열 오프셋, 즉 시작점에서 반환 열로 이동할 열 수입니다.

H-Lookup 공식

OFFSET(lookup_table, m, MATCH(lookup_value, OFFSET(lookup_table, n, 0, 1, COLUMNS(lookup_table)), 0) -1, 1, 1)

 

• n - 조회 행 오프셋, 즉 시작점에서 조회 행으로 이동할 행 수입니다.
• m - 리턴 행 오프셋, 즉 시작점에서 리턴 행으로 이동할 행 수입니다.

 

행렬 조회 수식(행 및 열 기준)

{=OFFSET (starting_point, MATCH (vertical_lookup_value, lookup_column, 0), MATCH (horizontal_lookup_value, lookup_row, 0))}

Ctrl + Shift + Enter 키를 동시에 누릅니다.

장점: 데이터 집합의 변경 내용에 영향을 받지 않고 왼쪽 Vlookup, 위쪽 Hlookup 및 양방향 조회(열 및 행 값별)를 수행할 수 있습니다.

단점 : 복잡하고 기억하기 어려운 구문.

 

INDEX MATCH 인덱스 일치

위의 수식 대부분을 대체할 수 있는 Excel에서 세로 또는 가로 조회를 수행하는 가장 좋은 방법입니다. 인덱스 일치 공식은 제 개인적인 취향이며 거의 모든 Excel 조회에 사용합니다.

 

V-Lookup 수식

INDEX (return_column, MATCH (lookup_value, lookup_column, 0))

H-Lookup 공식

INDEX (return_row, MATCH (lookup_value, lookup_row, 0))

행렬 조회 수식

특정 열과 행의 교차점에서 값을 반환하는 기존 인덱스 일치 공식의 확장입니다:

INDEX (lookup_table, MATCH (vertical_lookup_value, lookup_column, 0), MATCH (horizontal_lookup_value, lookup_row, 0))

단점 : 단 하나 - 수식의 구문을 기억해야합니다.

장점 : Excel에서 가장 다재다능한 조회 수식으로 여러 측면에서 Vlookup, Hlookup 및 Lookup 기능보다 우수합니다.

• 왼쪽 및 위쪽 조회를 수행할 수 있습니다.
• 열과 행을 삽입하거나 삭제하여 조회 테이블을 안전하게 확장하거나 축소할 수 있습니다.
• 조회 값의 크기에는 제한이 없습니다.
• 더 빠르게 작동합니다. 인덱스 일치 수식은 전체 테이블이 아닌 열/행을 참조하기 때문에 처리 능력이 덜 필요하고 Excel 속도가 느려지지 않습니다.

 

Excel Lookup 비교표

모든 Excel 조회 수식이 동일한 것은 아니며 일부는 여러 가지 조회를 처리 할 수 있고 다른 수식은 특정 상황에서만 사용할 수 있습니다. 아래 표에는 Excel의 각 조회 수식 기능이 요약되어 있습니다.

식	세로 조회	왼쪽 조회	수평 조회	위쪽 조회	행렬 조회	데이터 삽입/삭제 허용
조회	✓		✓
Vlookup)	✓
Hlookup			✓
Vlookup Match	✓					✓
Hlookup Match			✓			✓
Offset Match	✓	✓	✓	✓		✓
Offset Match					✓	✓
Index Match	✓	✓	✓	✓		✓
Index Match Match					✓	✓

 

Excel 조회 수식 예제

 

열의 세로 조회

세로 조회 또는 Vlookup은 한 열에서 조회 값을 찾고 다른 열에서 동일한 행의 값을 반환하는 프로세스입니다. Excel의 Vlookup은 다음과 같은 다양한 방법으로 수행 할 수 있습니다.

 

VLOOKUP 함수

조회 값이 테이블의 왼쪽 열에 있고 데이터 집합에 대한 구조를 변경하지 않으려는 경우(열을 추가하거나 삭제하지 않음) 일반 Vlookup 수식을 안전하게 사용할 수 있습니다.

=VLOOKUP(G2, $A$2:$E$6, 5, FALSE)

 

여기서 G2는 조회 값이고, 조회 테이블의 A2:E6이며, E는 반환 열입니다.

 

VLOOKUP Match

언제든지 열을 삽입하고 삭제할 수 있는 "변수" Excel 조회 테이블을 사용하는 경우 "하드 코딩된" 인덱스 번호 대신 동적 열 참조를 만드는 Match 함수를 포함하여 Vlookup 수식에 이러한 변경 내용을 적용하지 않도록 합니다.

=VLOOKUP(F2,$A$1:$D$6, MATCH($G$1,$A$1:$D$1, 0), FALSE)

 

INDEX MATCH - 왼쪽 조회

오른쪽에서 왼쪽으로의 조회를 쉽게 처리하고 추가하거나 삭제하는 열 수에 관계없이 완벽하게 작동하는 제가 가장 좋아하는 수식입니다.

예를 들어 B 열에서 H2의 값을 검색하고 F 열에서 일치 항목을 반환하려면 다음 수식을 사용합니다.

=INDEX($F$2:$F$6,(MATCH(H2,$B$2:$B$6,0)))

 

행의 가로 조회

수평 조회는 수평으로 정렬된 데이터 세트를 검색하는 수직 조회의 "전치된" 버전입니다. 즉, 한 행에서 조회 값을 검색하고 다른 행에서 같은 위치에 있는 값을 반환합니다.

조회 값이 B9에 있고 조회 테이블이 B1:F5이고 행 5에서 일치하는 값을 반환한다고 가정하면 다음 수식 중 하나를 사용합니다.

 

HLOOKUP 함수

데이터 집합의 맨 위 행에서만 조회할 수 있습니다.

=HLOOKUP(B8, $B$1:$F$5, 5, FALSE)

 

HLOOKUP MATCH

순수 Hlookup과 마찬가지로이 수식은 맨 위 행에서만 검색 할 수 있지만 조회 테이블에서 행을 안전하게 삽입하거나 삭제할 수 있습니다.

=HLOOKUP(B8, $B$1:$F$5, MATCH($A$9, $A$1:$A$5, 0), FALSE)

 

여기서 A1:A5는 행 머리글이고 A9는 일치 항목을 반환할 행의 이름입니다.

 

인덱스 일치

모든 행에서 조회 할 수 있으며 위 수식의 제한이 없습니다.

=INDEX($B$5:$F$5,(MATCH(B8,$B$1:$F$1,0)))

 

2차원 조회(행 및 열 값 기반)

2차원 룩업(매트릭스 룩업, 이중 룩업 또는 이원 룩업)은 행과 열의 일치 항목을 기준으로 값을 반환합니다. 즉, 2차원 조회 공식은 지정된 행과 열의 교차점에서 값을 검색합니다.

조회 테이블이 A1:E6이고 H2 셀에는 행에서 일치시킬 값이 포함되어 있고 H3에는 열에서 일치시킬 값이 있다고 가정하면 다음 수식이 효과적입니다.

 

INDEX MATCH MATCH 공식:

=INDEX($A$1:$E$6, MATCH(H2,$A$1:$A$6,0), MATCH(H3,$A$1:$E$1,0))

 

OFFSET MATCH MATCH 공식:

=OFFSET($A$1,MATCH(H2,$A$2:$A$6,0),MATCH(H3,$B$1:$E$1,0))

 

3차원 조회

3차원 룩업은 3개의 서로 다른 룩업 값으로 검색하는 것을 의미합니다. 아래 데이터 집합에서 특정 연도(H2)를 검색한 다음 해당 연도 데이터(H3) 내의 특정 이름을 검색한 다음 특정 월(H4)에 대한 값을 반환한다고 가정합니다.

 

작업은 다음 배열 공식을 사용하여 수행할 수 있습니다(정확하게 완료하려면 Ctrl + Shift + Enter  키를 누르십시오):

=INDEX($A$1:$E$12,MIN(IF((ROW($A$1:$A$12)>MATCH(H2,$A$1:$A$12,0))*($A$1:$A$12=H3),ROW($A$1:$A$12),"")),MATCH(H4,$A$1:$E$1,0))

 

여러 조건으로 조회

여러 기준을 평가할 수 있으려면 클래식 인덱스 일치 공식을 수정하여 배열 수식으로 변환해야 합니다.

INDEX(lookup_table, MATCH(1, (lookup_value1=lookup_column1) * (lookup_value2=lookup_column2)*…, 0), return_column_number)

조회 테이블이 A1:C11에 있는 경우 2가지 기준(A열에서 F1 셀의 값, B열에서 F2 셀의 값)으로 일치하는 항목을 찾습니다.

=INDEX($A$1:$C$11, MATCH(1, (F1=$A$1:$A$11) * (F2=$B$1:$B$11),0), 3)

 

늘 그렇듯이 Ctrl + Shift + Enter 배열 수식으로 계산할 수식입니다.

 

여러 값을 반환하기 위한 조회

어떤 Excel 조회 함수(LOOKUP, VLOOKUP 또는 HLOOKUP)를 사용하든 일치하는 항목만 반환할 수 있습니다. 발견된 모든 일치 항목을 얻으려면 배열 수식에 결합된 6개의 다른 함수를 사용해야 합니다.

IFERROR(INDEX( return_range, SMALL(IF( lookup_value=lookup_range, ROW( return_range )- m ,""), ROW() - n )),"")

 

• m은 반환 범위에서 첫 번째 셀의 행 번호에서 1을 뺀 값입니다.
• n은 첫 번째 수식 셀의 행 번호에서 1을 뺀 값입니다.

E2 셀에 조회 값, A2:A11에 조회 범위, B2:B11에 반환 범위, 2행에 첫 번째 수식 셀이 있는 경우 조회 수식의 모양은 다음과 같습니다.

=IFERROR(INDEX($B$2:$B$11, SMALL(IF($E$2 =$A$2:$A$11, ROW($B$2:$B$11 )- 1,""), ROW() - 1 )),"")

 

수식이 여러 일치 항목을 반환하려면 첫 번째 셀(F2)에 입력하고 Ctrl + Shift + Enter를 클릭한 다음 수식을 열 아래의 다른 셀에 복사합니다.

 

중첩된 조회(2개의 조회 테이블)

기본 테이블과 데이터를 가져오려는 조회 테이블에 공통 열이 없는 경우 다음과 같이 추가 조회 테이블을 사용하여 일치 항목을 설정할 수 있습니다.

Lookup_table2의 Amount 열에서 값을 검색하려면 다음 수식을 사용합니다.

=VLOOKUP(VLOOKUP(A2, Lookup_table1!$A$1:$B$6, 2, FALSE), Lookup_table2!$A$1:$B$6, 2, FALSE)

아래 스크린 샷에서 볼 수 있듯이 중첩 된 조회 수식은 완벽하게 작동합니다.

 

여러 시트의 순차적 Vlookup

이전 조회의 성공 또는 실패 여부에 따라 순차적 Vlookup을 수행하려면 중첩된 IFERROR 함수를 VLOOKUP과 함께 사용하여 여러 조건을 하나씩 평가합니다.

IFERROR(VLOOKUP(…), IFERROR(VLOOKUP(…), IFERROR(VLOOKUP(…),"Not found")))

첫 번째 Vlookup이 실패하면 IFERROR는 오류를 트래핑하고 다른 Vlookup을 실행합니다. 두 번째 Vlookup에서도 아무 것도 찾지 못하면 두 번째 IFERROR가 오류를 catch하고 세 번째 Vlookup을 실행하는 식입니다.

 

모든 Vlookup이 실패하면 마지막 IFERROR는 "찾을 수 없음" 또는 수식에 제공한 다른 메시지를 반환합니다.

예를 들어, 3개의 다른 시트에서 양을 가져오려고 합니다.

=IFERROR(VLOOKUP(B1,A6:B9,2,0), IFERROR(VLOOKUP(B1,D6:E9,2,0), IFERROR(VLOOKUP(B1,G6:H9,2,0), "Not found")))

 

결과는 다음과 유사합니다.

 

대/소문자 구분 조회

아시다시피 모든 Excel 조회 함수는 본질적으로 대/소문자를 구분하지 않습니다. 조회 수식에서 소문자와 대문자를 구분하도록 하려면 EXACT 함수와 함께 LOOKUP 또는 INDEX MATCH를 사용합니다.  INDEX MATCH를 선택한 이유는 LOOKUP 함수처럼 조회 열의 값을 정렬할 필요가 없고, 왼쪽에서 오른쪽 및 오른쪽에서 왼쪽 조회를 모두 수행할 수 있고, 모든 데이터 유형에 대해 완벽하게 작동하기 때문입니다.

INDEX(return_column, MATCH(TRUE, EXACT(lookup_column, lookup_value),0))

G2가 조회 값이고 조회할 A 열과 일치 항목을 반환할 E 열인 경우 대/소문자 구분 조회 수식은 다음과 같습니다.

=INDEX($E$2:$E$6, MATCH(TRUE, EXACT($A$2:$A$6,G2),0))

 

배열 공식이므로 Ctrl + Shift + Enter 키를 눌러 올바르게 완료해야 합니다.

 

부분 문자열 일치 조회

부분 일치로 조회하는 것은 보편적인 솔루션이 없는 Excel에서 가장 어려운 작업 중 하나입니다. 사용할 수식은 조회 값과 검색할 열의 값 간에 어떤 종류의 차이가 있는지에 따라 달라집니다.

대부분의 경우 LEFT, RIGHT 또는 MID 함수를 사용하여 값의 공통 부분을 추출한 다음 다음 수식에서와 같이 해당 부분을 Vlookup 함수의 lookup_value 인수에 제공합니다.

=VLOOKUP(RIGHT(D2,4), $A$2:$B$6, 2, FALSE)

 

여기서 D2는 조회 값이고, A2:B6은 조회 테이블이며, 일치 항목을 반환할 열의 인덱스 번호에 2가 있습니다.

 

Excel에서 수식없이 조회하는 방법

단일 수식 없이 테이블을 조회, 일치 및 병합할 수 있는 특수 도구인 테이블 병합 마법사를 만들었습니다. 

• 여러 기준에 의한 조회, 즉 하나 또는 여러 개의 열을 고유 식별자로 사용합니다.
• 기존 열의 값을 업데이트하고 조회 테이블에서 새 열을 추가합니다.
• 여러 일치 항목을 별도의 행으로 반환합니다. 행 결합 마법사와 함께 사용하면 여러 결과를 단일 셀, 쉼표 또는 구분된 상태로 반환할 수도 있습니다

 

테이블 병합 마법사는 쉽고 직관적으로 작업할 수 있습니다. 

1. 일치하는 값을 가져올 기본 테이블을 선택합니다.
2. 일치 항목을 가져올 조회 테이블을 선택합니다.
3. 하나 이상의 공통 컬럼을 정의합니다.
4. 업데이트하거나 테이블 끝에 추가할 열을 선택합니다.
5. 필요에 따라 하나 이상의 추가 병합 옵션을 선택합니다.
6. 마침을 클릭하면 잠시 후에 결과를 얻을 수 있습니다!