팁
이 문서에서 설명하는 함수의 향상된 버전인 새 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 범위(두 번째 인수)의 셀을 검색하고 범위 E열(세 번째 인수)의 세 번째 열에서 가장 유사하게 일치하는 항목을 반환합니다.
네 번째 인수는 비어 있으므로 함수는 유사 일치 항목을 반환합니다. 값이 반환되지 않는다면 C 또는 D 열의 값 중 하나를 입력해야 약간의 값이라도 얻을 수 있습니다.
VLOOKUP에 익숙하다면 HLOOKUP 함수도 똑같이 쉽게 사용할 수 있습니다. 동일한 인수를 입력하지만 열이 아닌 행을 검색합니다.
VLOOKUP 대신 INDEX 및 MATCH 사용
VLOOKUP 사용에는 몇 가지 제한 사항이 있습니다. VLOOKUP 함수는 왼쪽에서 오른쪽으로만 값을 조회할 수 있습니다. 즉, 조회한 값이 포함된 열은 항상 반환 값이 포함된 열의 왼쪽에 위치해야 합니다. 이제 스프레드시트가 이 방법으로 작성되지 않은 경우 VLOOKUP을 사용하지 마세요. 대신 INDEX 함수와 MATCH 함수를 조합하여 사용하세요.
다음 예제는 검색하려는 값(청주시)이 맨 왼쪽 열에 없는 작은 목록입니다. 따라서, VLOOKUP을 사용할 수 없습니다. 대신 MATCH 함수를 사용하여 B1:B11 범위에서 청주시를 찾아보겠습니다. 청주시는 4행에서 검색됩니다. 그런 다음 INDEX가 이 값을 조회 인수로 사용하고 네 번째 열(D열)에서 청주시의 인구를 찾습니다. 사용된 수식은 A14 셀에 나와 있습니다.
사용해 보기
사용자 고유의 데이터로 조회 함수를 사용해 보기 전에 조회 함수를 시험해 보고 싶다면 몇 가지 예제 데이터가 있습니다.
회사에서의 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 |