Vlookup 다중조건 - vlookup dajungjogeon

엑셀 VLOOKUP 함수 기본 이해

VLOOKUP 함수는 기준이 되는 값을 다른 셀에서 불러올 수 있습니다.

=vlookup(기준값, 불러올셀범위지정,기준항목위치,0)

이런 형식으로 함수를 지정해 줍니다.

​

VLOOKUP 함수로 단가표에서

각 품목에 맞는 가격을 불러오는 엑셀 함수 예제입니다.

=VLOOKUP(C5,$B$13:$C$16,2,FALSE)

(품목셀,단가표전체범위,가격위치,false)

=VLOOKUP("사과",$B$13:$C$16,2,0)

이렇게 하면 사과의 가격을 불러옵니다.

​

단, 품목셀이 단가표의 맨 앞에 위치해야 합니다.

가격의 위치가 해당 품목을 기준으로 2번째에서 불러오기 때문입니다.

단가표 전체 범위에서 품목셀이 첫번째에 위치하면 됩니다.

​

​엑셀 VLOOKUP 함수 다중조건 값 불러오기

VLOOKUP 함수는 기준 조건이 여러개 일 때는 직접 사용하지 못하고

데이터를 일부 가공해주면 됩니다.

방식은 기본과 동일하며, vlookup 함수의 기본 구조를 이해하면 어렵지 않습니다.

​

아래 표는 품목, 규격의 기준 값이 여러개입니다.

이러한 경우 단가표에 기준이 될 품목&규격 항목을 맨 앞에 만들어주면 됩니다.

​

=VLOOKUP($C22&$D22,$B$30:$E$34,4,0)

단가표의 품목&규격은

=품목셀&규격셀

이렇게 지정해 주면 쉽게 만들 수 있습니다.

&로 셀이나 텍스트를 합칠 수 있습니다.

​엑셀 VLOOKUP 함수로 성명에 맞는 값 불러오기

VLOOKUP 함수로 아래표에서 각 성명에 맞는 항목을 불러오는 예제입니다.

=VLOOKUP($F40,$B$40:$D$45,COLUMN(B38),0)

이런 식으로 홍길동의 휴대폰 번호를 불러올 수 있으나

위 표에서 홍길동이 2명인데, 위에 있는 홍길동의 데이터만 불러오게 되는 문제가 생깁니다.

​

VLOOKUP 함수의 경우 기준값이 유니크값이어야 하며

중복된 기준 값이 있는 경우 정확한 값을 불러올 수 있습니다.

휴대폰 번호는 중복이 없기에 휴대폰번호를 기준으로

불러올 수 있으나 VLOOKUP 함수는 휴대폰번호가 항목이 맨 앞에 있어야 합니다.

​

이런 경우에는 INDEX와 MATCH 함수를 이용해서 불러올 수 있습니다.

=INDEX($B$40:$B$45,MATCH($F46,$C$40:$C$45),0)

=INDEX(불러올값범위,MATCH(기준값,기준값범위),0)

이런 식으로 지정을 해주면 값을 불러옵니다 (0은 일치하는 값)

​

INDEX와 MATCH 함수도 기준의 중복이 없어야 값을 불러오며

중복값이 있는 경우 맨 마지막 값을 불러옵니다.

​

엑셀 VLOOKUP 함수 예제 파일 첨부했으니 참조하시고

다른 엑셀 포스팅도 참조해보세요.

엑셀VLOOKUP함수.xlsx

0.01MB

안녕하세요!

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

3. 완성된 형태입니다.

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

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

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

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

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

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

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

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