직장인들이 엑셀에서 가장 많이 쓰는 함수인
VLOOKUP 함수에 대해 설명을 드리도록 하겠습니다.
VLOOKUP 함수는 다양한 상황에서 사용이 가능하지만
보통 2개의 데이터를 하나의 데이터로 합칠 때 가장 많이 사용하지 않을까 싶습니다.
#VLOOKUP함수사용법
예를 들어서 설명을 드리도록 하겠습니다.
* 코로나확진자 데이터 활용(https://ncov.kdca.go.kr/)
위와 같이 좌측에는 2020-01-20부터 2023-06-02까지
코로나확진자수에 대한 데이터가 있습니다.
(다운로드 시점에 따라 데이터는 더 많을 겁니다)
우측의 특정일에 대한 데이터,
즉 노란색으로 음영을 색칠한 부분의 데이터를 가지고 오고 싶을 때
“필터 기능”이나 “찾기 기능”을 써도 되지만
VLOOKUP 함수를 이용해서 찾는 방법을 알아보도록 하겠습니다.
VLOOKUP 함수를 사용하는데 기본적인 조건은
두개의 데이터에서 동일한 데이터가 있어야지 사용 가능합니다.
위의 데이터에서는 주황색의 데이터가 같은 값이라는 것을 알 수 있습니다.
우리는 이제 이 값을 “기준값(KEY값)”라고 하겠습니다.
VLOOKUP 함수는 이 기준값을 기준으로 가지고 옵니다.
VLOOKUP 함수를 쓰면 위와 같이 예시가 나옵니다.
즉, VLOOKUP(기준값, 가져올데이터의 범위, 범위에서 가져올 열(번째), 정확도) 입니다.
함수를 작성을 하면 H12에 들어있는 함수는
=VLOOKUP(G12, $A$7:$E$1236,2,0)
“G12″값인 “2022-01-30” 기준으로
“$A$7:$E$1236″값인 “A7에서 E1236 범위” 내에서
“2”번째 즉, A열 B열 C열 D열 E열에서
2번째인 B열의 값을 가져오라는 것입니다.
마지막, “0”값은 완전하게 일치하는 것을 가지고 오라는 것입니다.
(“0″값을 그냥 넣는다고 넣는다고 생각하면 됩니다.”)
*=VLOOKUP(G12, $A:$E,2,0) 와 같이 열 전체값을 설정을 해도 됩니다.
G12에 VLOOKUP 함수를 작성한 후에 복사(Ctrl + C)해서
나머지 노란색 음영에 붙여넣기(Ctrl + V)를 하면
위와 같이 H13은 정상 작동을 하지만
I12이후는 에러가 납니다.
범위를 지정할 때 “$” 값이 고정값인 것을 알고 있을겁니다.
즉, 현재 함수에서
H12에 들어있는 함수값인 =VLOOKUP(G12, $A$7:$E$1236,2,0) 을
I12에 복사하면
함수값이 =VLOOKUP(H12, $A$7:$E$1236,2,0) 으로
G12값은 H12값으로 변경이 되어서 에러가 나게 됩니다.
그래서 에러가 발생하지 않도록 정확하게 함수를 쓴다면
=VLOOKUP($G12, $A$7:$E$1236,2,0)
위와 같이 G열을 고정을 시키는 게 맞습니다.
하지만 위와 같이 작성을 하면
“국내발생(명)”, “해외유입(명)”, “사망(명)”의 값이 아닌
2번째 “계(명)” 값을 가지고 오게 됩니다.
#VLOOKUP함수사용팁
그래서 수작업으로
=VLOOKUP($G12, $A$7:$E$1236,3,0)
3, 4, 5 각각 이렇게 함수를 변경을 해줘도 괜찮지만
위와 같이 H10~K10열 위에 구분자를 넣고
함수를 아래와 같이 넣으면 보다 빠르게 할 수 있습니다.
=VLOOKUP($G12, $A$7:$E$1236,H$9,0)
(깔끔하게 보이기 위해 해당 숫자를 향후 글자색 조정이나 행 숨기기 등으로 안 보이도록 할 수 있습니다.)
#VLOOKUP함수사용주의사항
VLOOKUP 함수는 기본적으로 가져오는 데이터에 있어서
기준값이 가장 왼쪽에 있어야 합니다.
즉 만약 위와 같이 가져와야할 데이터의
기준값이 B열에 있다고 하면
그보다 왼쪽에 있는 A열의 데이터를 가지고 오지 못합니다.
위와 같은 경우라면 B열의 값을
왼쪽으로(A열) 옮기는 작업을 진행한 후에
VLOOKUP 함수를 사용하거나
대체함수 (INDEX , MATCH 함수)를 사용합니다.
두번째로 가져와야할 데이터에서 중복값이 있다면
첫번째 값을 가지고 오게 되어 있습니다.
위와 같이 만약 A17값이 오타로
“2020-01-30″이 아닌 “2022-01-30” 있다고 하면
위에서 첫번째로 있는 값을 가지고 오기 때문에
중복값이 있는 경우 주의를 해야 합니다.
VLOOKUP 함수의 한계점으로 가져오는 데이터에서
기준값이 가장 왼쪽에 위치를 하지 않는 경우에는
VLOOKUP를 사용하지 못한다고 설명을 드렸습니다.
→ 이럴 경우 기준값을 가장 왼쪽으로 이동 또는 복사를 한 후에 VLOOKUP 함수를 사용해야합니다.
만약에 자료를 이동 또는 복사를 하지 못할 경우
사용할 수 있는 방법에 대해 설명을 드리겠습니다.
#VLOOKUP대체함수INDEX
INDEX 함수는 지정한 범위의 데이터에서
“행”과 “열”의 범위를 지정을 하면 해당 데이터를 가져오는 함수입니다.
함수는 아래와 같이 지정을 합니다.
=INDEX(데이터범위, 행번호, 열번호) 입니다.
만약 아래와 같이 =index(A5:B10,3,2)의 함수를 넣으면
A5에서 B10까지의 데이터에서
행으로 3번째, 열로는 2번째 데이터를 가져오는 함수로
결국 “2020-01-20″의 값을 가지고 오게 됩니다.
그럼 이런 경우라고 하면
우측의 데이터에서 노란색의 값을 가져오는 방법은
=INDEX($A$5:$B$1236,행번호,1) 을
사용해서 만들수 있을 겁니다.
그럼 여기서 “행번호”를 가져오는 방법을
MATCH 함수를 사용해서 만들어보겠습니다.
#VLOOKUP대체함수MATCH
MATCH 함수는 기준값을 기준으로
해당 데이터가 몇번째에 있는지를 알 수 있는 함수입니다.
MATCH(찾을값, 찾을데이터의 범위, 타입)을 사용하며
=MATCH(G10,B5:B1236,0) 와 같이 작성을 하면
G10 값인 “2023-06-01″값이
“B5에서 B1236″기준에서 몇번째 행에 있는지 값을 알려줍니다.
* 마지막의 타입의 0값은 정확하게 일치하는 값을 가져오는 것을 의미하므로 무조건 해당 “0”을 넣는다고 생각하시면 될 것 같습니다.
그래서 앞서 설명을 드린
=INDEX($A$5:$B$1236,행번호,1)에서
“행번호” 대신에 위의 MATCH 함수를 넣어주면
해당 값을 완벽하게 가져올 수 있습니다.
아래칸에 해당 함수를 복사 – 붙여넣기를 하기 위해서는 고정값($) 설정을 잘 해줘야합니다.
위치의 변화에 따라 움직이지 않는 값은 $를 붙여서 잘 설정을 해줘야합니다.
위와 같이 함수를
=INDEX($A$5:$B$1236,MATCH($G10,$B$5:$B$1236,0),1)
넣으주면 복사 – 붙여넣기를 해도 문제가 없을 것으로 보여집니다.
#INDEX및MATCH함수사용시주의사항
INDEX 및 MATCH 함수를 사용하는 것은
VLOOKUP 함수 대비 기준값이 가장 왼쪽에 있어야하는 한계는 없지만
아무래도 2개의 함수가 결합이 되어야 하다보니
VLOOKUP 함수보다 직관성이 떨어집니다.
VLOOKUP 함수를 사용 가능할 때는 VLOOKUP 함수를
불가능하면 INDEX 및 MATCH 함수 사용을 추천을 드립니다.
추가적으로 VLOOKUP 함수와 동일하게 가져와야할 데이터에서 중복값이 있다면
첫번째 값을 가지고 오게 되어 있습니다.
위와 같이 만약 A17값이 오타로
“2020-01-30″이 아닌 “2022-01-30” 있다고 하면
위에서 첫번째로 있는 값을 가지고 오기 때문에 중복값이 있는 경우 주의를 해야 합니다.
* VLOOKUP 함수와 동일
지금까지 VLOOKUP 함수 대신 사용할 수 있는 INDEX 및 MATCH 함수에 대해 알아봤습니다.