엑셀 VLOOKUP으로 특정값 자동으로 가져오기

엑셀을 사용하더 보면, 특정값을 추가로 넣어야 하는 상황이 생기기 마련입니다.

셀 몇개라면 손으로 하나씩 확인하면서 넣어 주면 되겠지만, 그 항목이 몇십 몇백 개라면 이야가 달라지죠.

 

이처럼 많은 거래 자료에서 담당자의 소속팀을 넣어야 하는 상황이 발생했다면 어떻게 하시겠어요?

엑셀의 다양한 기능을 아는 사람이 아니라면 아마도 아래의 두가지 중 한가지 방식을 선택하게 되겠습니다.

 

1. 하나하나 찾아서 손으로 입력한다.

2. 필터를 적용시켜서 담당자 이름을 선택 한 뒤, 해당 소속을 입력한다.

 

이렇게 단순하고 반복적인 업무를 쉽게 처리 하기 위해서 굳이 AI나 RPA가 하지 않더라도

엑셀에는 다양한 기능이 있어요.

 

3. VLOOKUP을 이용한다.

 

우리는 3번의 기능인 VLOOKUP이라는 함수를 이용해서 

지정된 테이블에 입력된 자료 중에, 필요한 자료만 쏙쏙 찾아 오는 기능을 알아 보려고 합니다.

 

담당자의 소속을 넣어 주기 위해서는 담당자 소속이 들어가 있는 테이블이 필요합니다.

영업팀에 담당자들이 많이 있다고 가정하고 아래와 같은 테이블을 다른 시트에 만들어 주었습니다.

 

원래 있던 테이블로 돌아가서 VLOOKUP이라는 함수를 심어 줘 보도록 하겠습니다.

 

데이터를 넣을 셀을 클릭한 다음 윗쪽에서 fx라는 함수를 클릭합니다.

그러면 함수 마법사라는 팝업이 떠오릅니다.

이중에서 VLOOKUP 이라는 함수를 찾아야 하는

처음에는 '범주 선택'이라는 곳을 보면, '최근에 사용한 함수'라는 것이 나오는데 

최근에 VLOOKUP을 사용한 적이 없는 경우에는

'찾기/참조 영역'을 클릭해서 맨 아래로 스크롤을 내리면 V로 시작하는 부분에 

VLOOKUP이라는 함수를 찾을 수 있습니다.

 

그러자, 새로운 팝업창이 떠올랐어요.

=VLOOKUP() 이라는 함수가 들어갔고, 함수인수를 입력해 주는 창이 떠올랐습니다.

우리는 이곳에서 4가지 형태의 함수를 넣어 주게 되는데요.

 

첫번째는 Lookup_value 입니다.

이 값에서는 찾을 이름을 넣어 줍니다.

우리는 담당자 이름이 있는 셀인 I3을 입력하거나 클릭을 하면 자동으로 입력이 됩니다.

 

두번째는 Table_array 입니다.

아까 담당자와 소속팀 테이블을 만들어 두었던 시트로 가서 그곳에 테이블을 지정해 줍니다.

즉, 그 테이블에서 담당자 이름을 찾고, 관련 자료를 찾아 오라는 명령입니다.

담당자와 소속이 있는 테이블인 Sheet3으로 가서 

해당 테이블을 마우스로 쭉 드레그를 해 준 뒤

F4를 눌러서 절대값으로 지정을 해 주었습니다.

 

세번째는 Col_index_num 값 입니다.

즉, 두번째로 Table_array 로 지정해 놓았던 값중에서 몇번째 값을 가져 오는지를 지정해 줍니다.

테이블 상에서 담당자를 기준으로, 담당자는 1번째, 소속은 2번째 값이므로

소속을 표기 하기 위해서는 2를 입력해 줍니다.

 

이제 끝으로 4번째 값을 입력해 주는데, Range_lookup 입니다.

이 값은 정확하게 일치하는 것을 찾으려면 false를, 비슷하게 일치하는 것을 찾으려면 true 혹은 생략을 하라고 합니다.

우리는 정확하게 일치하는 값을 가져 오기 위해 false 혹은 숫자 0을 넣어 주면 됩니다.

 

위의 테이블을 보면 정확한 값을 가져 오기 위해 '0'을 입력했을 경우 아래 값이 "영업1"로 정확히 나온데 반해

비슷한 값을 가지고 오는 명령을 위해 빈칸으로 두었을 때는 정확하지 않은 값인 "영업3"을 가져 온 것을 알 수 있습니다.

 

'확인'을 눌러 주자

해당 셀인 '소속'에 '영업1'이라는 값이 추출 된 것을 알 수 있습니다.

값은 '영업1'이라는 값으로 표시가 되어 있지만, 상단의 함수를 보면 =VLOOKUP(I3,Sheet3!$A$1:$B$13,2,0) 이라는 값이 

설정 된 것을 알 수 있습니다.

 

즉, VLOOKUP을 이용해서, I3 셀의 경우, Sheet3!$A$1:$B$ 의 값들을 참조 해서 값을 찾는데

I3에 해당하는 두번째 값을 '0' 정확히 일치하는 값으로 가져 온다. 

라는 명령이라고 풀이를 해 볼 수 있을 것 입니다.

 

그렇다면 J3의 값은 완성이 되었지만, J4와 J5등 아래에 있는 값들은 어떻게 가져 올까요?

코드를 하나하나 복사해서 붙여 넣기를 해도 되겠지만,

함수가 적용된 셀을 클릭하고 오른쪽 아래 보면 두꺼운 네모가 생긴 곳에 마우스 포인터를 가져가면

그곳이 '+' 모양으로 바뀌게 됩니다.

그때 더블 클릭하면 동일한 함수가 테이블 아래 까지 쭉- 복사가 됩니다.

 

 

각각의 셀을 클릭해 보면 해당 되는 I의 숫자는 3부터 쭉 바뀌고 있지만, 

다른 서식은 바뀌지 않는 것을 볼 수 있습니다.

그로인해서 같은 함수가 위에서 부터 아래까지 동일하게 적용이 되었음을 알 수가 있어요.

 

자, 그럼 지난번 피벗 테이블을 이용해서 

소팀별 실적을 한번 조회해 보는 복습의 시간을 가져 보도록 하겠습니다.

 

피봇테이블에서 이번에 새롭게 추가 된 소속팀을 행에 위치하고

값을 납품가로 지정을 해 주었습니다.

그러자 소속팀별로 납품가 합계가 표시가 되었습니다.

 

영업1팀은 129만원, 영업2팀은 99만원, 영업3팀은 60만원, 영업4팀은 72만원을 남품하였습니다.

확인해 보니 내림차순으로 정렬이 되어 있지 않네요?

 

 

이렇게 다시한번 정렬을 해 주고나니, 영업1팀, 영업2팀, 그리고 영업4팀 마지막으로 영업3팀이 순차적으로 

테이터가 설정이 되었습니다.

영업3팀은 영업4팀에게 순위를 뺏기고 말았네요.

 

아무틈, 이와 같이 간단히 VLOOKUP을 통해서 

테이터를 찾아와서 적용하는 방법을 알아 보았습니다.

여러분의 반복되고 단순한 업무는 VLOOKUP에게 맡기고

그렇게 찾은 여유시간에는 워라벨을 즐기실 수 있기를 희망합니다.