Vlookup 다중조건 다른시트 - vlookup dajungjogeon daleunsiteu

안녕하세요!

목차 Show

데이터를 가공하여 조건을 하나로 만든 후 vlookup을 복수 조건처럼 사용하는 방법.
VLOOKUP 대신 INDEX 및 MATCH 사용
사용해 보기
VLOOKUP 예시 직장
HLOOKUP 예제
INDEX 및 MATCH 예제

지난 시간에 이어 복수조건으로 vlookup을 사용하는 방법을 알아볼 차례입니다.

복수조건으로 특정값을 가져올때는 두가지의 방법이 있습니다.

1. 데이터를 가공하여 조건을 하나로 만든다.

2. vlookup 대신 index와 match 조합으로 함수를 만든다.

이 두가지 모두 사용해보았을때 각각의 장단점이 있습니다.

1. 데이터를 가공하여 조건을 하나로 만들었을때는 데이터자체를 가공해야 한다는 단점이 있어요. 따라서 어떠한 데이터를 어떠한 프로그램에서 다운받아 붙여넣기 하여 사용하고자 할때는 한번 데이터를 가공해야 한다는 단점이 있습니다.

2. index와 match를 사용했을때는 데이터의 가공없이 함수로 사용이 가능하지만 데이터 량이 많아질 경우 엑셀 자체에 부하를 초래하여 엑셀 파일이 느려질 수 있다는 단점이 있습니다.

따라서 1. 데이터를 가공하여 속도를 올릴것인지, 2. 데이터 량이 많지않아 부하가 없으니 index와 match를 사용할 것인지를 각자에 맞게 사용하시면 되겠습니다.

일단은 첫번째 방법은 데이터를 가공하여 조건을 하나로 만드는 방법을 설명드릴께요.

데이터를 가공하여 조건을 하나로 만든 후 vlookup을 복수 조건처럼 사용하는 방법.

1. 아래 그림처럼 종류와 세분류 두가지의 조건을 가지고 있습니다.

- 따라서 한가지 조건밖에 인식하지 못하는 vlookup을 쓸 수 없기에 & 함수를 사용하여 조건을 하나로 만들어 줍니다.

[두개 조건의 vlookup 예시]

2. 아래 그림처럼 단가표와 가져와야 할 표에 모두 병합열을 제작하고 &함수로 여러개의조건을 하나로 만들어 줍니다.

[병합 형태의 복수조건]

① 단가표에 함수를 사용하여 병합열을 만들어줍니다. 반드시 단가의 왼쪽에 있어야 합니다.

※ vlookup 함수는 왼쪽에서 오른쪽방향으로 찾기 때문에 찾아야할 것의 왼쪽에 병합을 만들어 주세요.

② 찾아야할 표에도 조건을 함수를 이용하여 병합해주세요.

③ 함수는 &입니다. 예시에 보이는 것은 J3과 K3을 병합하는 형태인데요. 중간에 "/"를 넣어줘서 구분이 될 수 있도록 해습니다. 함수는 =$J3&"/"&$K3 입니다. 이번에도 함수 내의 셀 주소에 알파벳 앞에만 $를 넣어줘서 가로를 고정했습니다.

※ 주의 하실 점은 vlookup은 ~ 특수문자를 찾지 못한다는 것입니다.

※ 모두 정상적으로 vlookup을 사용했는데 값을 찾지 못할때는 ~ 특수문자가 있어서입니다. 무엇을 하시든 ~ 특수문자의 사용은 자제해주세요. 만약 ~ 표시가 있다면 SUBSTITUTE 함수를 써서 ~을 - 나 다른 특수문자로 변경해서 사용하시면 vlookup에서 찾을 수 있습니다.

3. 완성된 형태입니다.

- 그럼 아래처럼 병합을 조건으로 단가를 찾을 수 있겠네요!

[병합 완성 형태]

4. vlookup을 다시 해보면 복수의 조건을 일치 시키는 형태로 단가를 불러올 수 있습니다.

- 함수식은 =VLOOKUP($M3,$E$3:$F$9,2,FALSE) 입니다.

- 이번에도 조건에가로를 고정하고 범위를 고정한 형태로 수식을 작성했습니다.

[복수 조건 vlookup 함수식]

5. 단가가 정상적으로 나온것을 확인할 수 있습니다.

- 완료되었다면 볼 필요가 없는 병합 열은 숨기기하여 가려주세요. 그럼 완벽한 형태로 볼 수 있습니다.

[완성형태]

이렇게 & 함수로 병합하여 복수조건의 vlookup을 해보았구요. 다음 글에서는 index와 match를 사용한 복수 조건의 값을 찾은 것을 알아보도록 하겠습니다!

여러분도 언제나 화이팅입니다!

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

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

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