팁: 이 문서에 설명된 향상된 버전의 새로운 XLOOKUP 및 XMATCH 함수를 사용해 보세요. 이러한 새로운 함수는 어떤 방향으로든 작동하며 기본적으로 정확한 일치 항목을 반환하므로 이전 함수보다 더 쉽고 편리하게 사용할 수 있습니다.
사무실 위치 번호 목록이 있고 각 사무실에 있는 직원을 알아야 한다고 가정해 보겠습니다. 스프레드시트는 엄청나기 때문에 어려운 작업이라고 생각할 수 있습니다. 실제로 조회 함수를 사용하는 것은 매우 쉽습니다.
VLOOKUP 및 HLOOKUP 함수는 INDEX 및 MATCH와 함께 Excel에서 가장 유용한 함수 중 일부입니다.
참고: Excel에서는 조회 마법사 기능을 더 이상 사용할 수 없습니다.
다음은 VLOOKUP을 사용하는 방법의 예입니다.
=VLOOKUP(B2,C2:E7,3,TRUE)
이 예제에서 B2는 함수가 작동해야 하는 데이터의 요소인 첫 번째 인수입니다. VLOOKUP의 경우 이 첫 번째 인수는 찾으려는 값입니다. 이 인수는 셀 참조이거나 "smith" 또는 21,000과 같은 고정 값일 수 있습니다. 두 번째 인수는 찾으려는 값을 검색할 셀 범위인 C2-:E7입니다. 세 번째 인수는 원하는 값을 포함하는 셀 범위의 열입니다.
네 번째 인수는 선택 사항입니다. TRUE 또는 FALSE를 입력합니다. TRUE를 입력하거나 인수를 비워 두면 함수는 첫 번째 인수에서 지정한 값의 대략적인 일치 항목을 반환합니다. FALSE를 입력하면 함수가 첫 번째 인수에서 제공하는 값과 일치합니다. 즉, 네 번째 인수를 비워 두거나 TRUE를 입력하면 유연성을 높일 수 있습니다.
이 예제에서는 함수의 작동 방식을 확인할 수 있습니다. 셀 B2(첫 번째 인수)에 값을 입력하면 VLOOKUP은 C2:E7(2번째 인수) 범위의 셀을 검색하고 범위의 세 번째 열인 E 열(3번째 인수)에서 가장 가까운 근사치를 반환합니다.
네 번째 인수는 비어 있으므로 함수는 대략적인 일치 항목을 반환합니다. 값이 반환되지 않는다면 C 또는 D 열의 값 중 하나를 입력해야 약간의 값이라도 얻을 수 있습니다.
VLOOKUP에 익숙한 경우 HLOOKUP 함수도 똑같이 쉽게 사용할 수 있습니다. 동일한 인수를 입력하지만 열 대신 행을 검색합니다.
VLOOKUP 대신 INDEX 및 MATCH 사용
VLOOKUP 사용에는 특정 제한 사항이 있습니다. VLOOKUP 함수는 왼쪽에서 오른쪽으로만 값을 조회할 수 있습니다. 즉, 조회하는 값이 포함된 열은 항상 반환 값이 포함된 열의 왼쪽에 있어야 합니다. 이제 스프레드시트가 이런 방식으로 빌드되지 않은 경우 VLOOKUP을 사용하지 마세요. 대신 INDEX 및 MATCH 함수의 조합을 사용합니다.
다음 예제는 검색하려는 값(청주시)이 맨 왼쪽 열에 없는 작은 목록입니다. 따라서, VLOOKUP을 사용할 수 없습니다. 대신 MATCH 함수를 사용하여 B1:B11 범위에서 청주시를 찾아보겠습니다. 청주시는 4행에서 검색됩니다. 그런 다음 INDEX가 이 값을 조회 인수로 사용하고 네 번째 열(D열)에서 청주시의 인구를 찾습니다. 사용된 수식은 A14 셀에 나와 있습니다.
VLOOKUP 대신 INDEX 및 MATCH를 사용하는 방법에 대한 자세한 예는 Microsoft MVP인 Bill Jelen의 https://www.mrexcel.com/excel-tips/excel-vlookup-index-match/ 문서를 참조하세요.
사용해 보기
사용자 고유의 데이터를 사용해 보기 전에 조회 함수를 실험하려는 경우 몇 가지 샘플 데이터는 다음과 같습니다.
VLOOKUP 회사 예제
다음 데이터를 빈 스프레드시트에 복사합니다.
팁: Excel에 데이터를 붙여 넣기 전에 A~C 열의 열 너비를 250픽셀로 설정하고 텍스트 줄 바꿈(홈 탭, 맞춤 그룹)을 클릭합니다.
|
밀도 |
점도 |
온도 |
|
0.457 |
3.55 |
500 |
|
0.525 |
3.25 |
400 |
|
0.606 |
2.93 |
300 |
|
0.675 |
2.75 |
250 |
|
0.746 |
2.57 |
200 |
|
0.835 |
2.38 |
150 |
|
0.946 |
2.17 |
100 |
|
1.09 |
1.95 |
50 |
|
1.29 |
1.71 |
0 |
|
수식 |
설명 |
결과 |
|
=VLOOKUP(1,A2:C10,2) |
근사값을 사용하여 A열에서 값 1을 검색하고, A열에서 1보다 작거나 같은 값 중 최대값(즉, 0.946)을 찾은 다음, 같은 행에 있는 B열에서 값을 구합니다. |
2.17 |
|
=VLOOKUP(1,A2:C10,3,TRUE) |
근사값을 사용하여 A열에서 값 1을 검색하고, A열에서 1보다 작거나 같은 값 중 최대값(즉, 0.946)을 찾은 다음, 같은 행에 있는 C열에서 값을 구합니다. |
100 |
|
=VLOOKUP(0.7,A2:C10,3,FALSE) |
정확하게 일치하는 값을 사용하여 A열에서 값 0.7을 검색합니다. A열에는 정확하게 일치하는 값이 없기 때문에 오류가 반환됩니다. |
#N/A |
|
=VLOOKUP(0.1,A2:C10,2,TRUE) |
정확하게 일치하는 값을 사용하여 A열에서 값 0.1을 검색합니다. 0.1은 A열의 최소값보다 작기 때문에 오류가 반환됩니다. |
#N/A |
|
=VLOOKUP(2,A2:C10,2,TRUE) |
근사값을 사용하여 A열에서 값 2를 검색하고, A열에서 2보다 작거나 같은 값 중 최대값(즉, 1.29)을 찾은 다음, 같은 행에 있는 B열에서 값을 구합니다. |
1.71 |
HLOOKUP 예제
이 표에 있는 셀을 모두 복사해 빈 Excel 워크시트의 A1 셀에 붙여 넣습니다.
팁: Excel에 데이터를 붙여 넣기 전에 A~C 열의 열 너비를 250픽셀로 설정하고 텍스트 줄 바꿈(홈 탭, 맞춤 그룹)을 클릭합니다.
|
굴대 |
베어링 |
볼트 |
|
4 |
4 |
9 |
|
5 |
7 |
10 |
|
6 |
8 |
11 |
|
수식 |
설명 |
결과 |
|
=HLOOKUP("굴대", A1:C4, 2, TRUE) |
행 1에서 "굴대"를 찾고 같은 열(열 A)에 있는 행 2의 값을 반환합니다. |
4 |
|
=HLOOKUP("베어링", A1:C4, 3, FALSE) |
행 1에서 "베어링"을 찾고 같은 열(열 B)에 있는 행 3의 값을 반환합니다. |
7 |
|
=HLOOKUP("바", A1:C4, 3, TRUE) |
행 1에서 "바"를 찾고 같은 열에 있는 행 3의 값을 반환합니다. "바"와 정확히 일치하는 값이 없으므로 행 1에서 "바"보다 작은 값 중 가장 큰 값(열 A의 "굴대")이 사용됩니다. |
5 |
|
=HLOOKUP("볼트", A1:C4, 4) |
행 1에서 "볼트"를 찾고 같은 열(열 C)에 있는 행 4의 값을 반환합니다. |
11 |
|
=HLOOKUP(3, {1,2,3;"a","b","c";"d","e","f"}, 2, TRUE) |
3행으로 이루어진 배열 상수에서 3을 찾고 같은 열(세 번째 열)의 행 2에 있는 값을 반환합니다. 배열 상수에 3행의 값이 있으며 각 행은 세미콜론(;)으로 구분되어 있습니다. "c"를 행 2에서 찾았고 3과 같은 열에 있으므로 "c"가 반환됩니다. |
c |
INDEX 및 MATCH 예제
이 마지막 예제에서는 INDEX 및 MATCH 함수를 함께 사용하여 5개 도시 각각에 대해 가장 빠른 청구서 번호와 해당 날짜를 반환합니다. 날짜가 숫자로 반환되기 때문에 TEXT 함수를 사용하여 날짜로 서식을 지정합니다. INDEX 함수는 실제로 MATCH 함수의 결과를 인수로 사용합니다. INDEX 및 MATCH 함수의 조합은 각 수식에서 두 번 사용되는데, 먼저 청구서 번호를 반환한 후에 날짜를 반환합니다.
이 표에 있는 셀을 모두 복사해 빈 Excel 워크시트의 A1 셀에 붙여 넣습니다.
팁: Excel에 데이터를 붙여 넣기 전에 A~D 열의 열 너비를 250픽셀로 설정하고 텍스트 줄 바꿈(홈 탭, 맞춤 그룹)을 클릭합니다.
|
청구서 |
도시 |
청구 날짜 |
날짜가 가장 빠른 도시별 청구서 |
|
3115 |
서울특별시 |
12-04-07 |
="서울특별시 = "&INDEX($A$2:$C$33,MATCH("서울특별시",$B$2:$B$33,0),1)& ", 청구 날짜: " & TEXT(INDEX($A$2:$C$33,MATCH("서울특별시",$B$2:$B$33,0),3),"m/d/yy") |
|
3137 |
서울특별시 |
12-04-09 |
="부산광역시 = "&INDEX($A$2:$C$33,MATCH("부산광역시",$B$2:$B$33,0),1)& ", 청구 날짜: " & TEXT(INDEX($A$2:$C$33,MATCH("부산광역시",$B$2:$B$33,0),3),"m/d/yy") |
|
3154 |
서울특별시 |
12-04-11 |
="대구광역시 = "&INDEX($A$2:$C$33,MATCH("대구광역시",$B$2:$B$33,0),1)& ", 청구 날짜: " & TEXT(INDEX($A$2:$C$33,MATCH("대구광역시",$B$2:$B$33,0),3),"m/d/yy") |
|
3191 |
서울특별시 |
12-04-21 |
="울산광역시 = "&INDEX($A$2:$C$33,MATCH("울산광역시",$B$2:$B$33,0),1)& ", 청구 날짜: " & TEXT(INDEX($A$2:$C$33,MATCH("울산광역시",$B$2:$B$33,0),3),"m/d/yy") |
|
3293 |
서울특별시 |
12-04-25 |
="대전광역시 = "&INDEX($A$2:$C$33,MATCH("대전광역시",$B$2:$B$33,0),1)& ", 청구 날짜: " & TEXT(INDEX($A$2:$C$33,MATCH("대전광역시",$B$2:$B$33,0),3),"m/d/yy") |
|
3331 |
서울특별시 |
12-04-27 |
|
|
3350 |
서울특별시 |
12-04-28 |
|
|
3390 |
서울특별시 |
12-05-01 |
|
|
3441 |
서울특별시 |
12-05-02 |
|
|
3517 |
서울특별시 |
12-05-08 |
|
|
3124 |
부산광역시 |
12-04-09 |
|
|
3155 |
부산광역시 |
12-04-11 |
|
|
3177 |
부산광역시 |
12-04-19 |
|
|
3357 |
부산광역시 |
12-04-28 |
|
|
3492 |
부산광역시 |
12-05-06 |
|
|
3316 |
대구광역시 |
12-04-25 |
|
|
3346 |
대구광역시 |
12-04-28 |
|
|
3372 |
대구광역시 |
12-05-01 |
|
|
3414 |
대구광역시 |
12-05-01 |
|
|
3451 |
대구광역시 |
12-05-02 |
|
|
3467 |
대구광역시 |
12-05-02 |
|
|
3474 |
대구광역시 |
12-05-04 |
|
|
3490 |
대구광역시 |
12-05-05 |
|
|
3503 |
대구광역시 |
12-05-08 |
|
|
3151 |
울산광역시 |
12-04-09 |
|
|
3438 |
울산광역시 |
12-05-02 |
|
|
3471 |
울산광역시 |
12-05-04 |
|
|
3160 |
대전광역시 |
12-04-18 |
|
|
3328 |
대전광역시 |
12-04-26 |
|
|
3368 |
대전광역시 |
12-04-29 |
|
|
3420 |
대전광역시 |
12-05-01 |
|
|
3501 |
대전광역시 |
12-05-06 |
