엑셀 VLOOKUP 다른 시트 - egsel VLOOKUP daleun siteu

팁: 이 문서에서 설명하는 향상된 버전의 새 XLOOKUP 및 XMATCH 함수를 사용해 본다. 이러한 새 함수는 어떤 방향으로든 작동하고 정확한 일치를 기본적으로 반환하여 전임 함수보다 더 쉽고 편리하게 사용할 수 있습니다.

목차 Show

VLOOKUP 대신 INDEX 및 MATCH 사용
사용해 보기
VLOOKUP 예시 직장
HLOOKUP 예제
INDEX 및 MATCH 예제

사무실 위치 번호 목록이 있으며 각 사무실에 있는 직원을 알아야 하다고 가정합니다. 스프레드시트는 방대하기 때문에 어려운 작업이라 생각할 수 있습니다. 실제로는 룩업 함수를 사용하기가 매우 쉽습니다.

INDEX 및 MATCH와함께 VLOOKUP 및 HLOOKUP 함수는 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