Excel의 필수기능 VLOOKUP 초보강좌

엑셀을 사용하다 보면 VLOOKUP함수는 언젠가 한 번쯤 꼭 사용하게 되는 함수 일 것입니다.

엑셀의 활용도가 많아짐에 따라 엑셀로 데이터를 가공하는 작업이 많아졌기 때문인데, 

물론 SQL 같은 DB를 이용하면 수월한 작업이지만 SQL을 사용할 수 없는 일반 협업부서에서는

엑셀만으로도 충분하기도 합니다.

 

필자 또한 VLOOKUP함수를 사용하기 위해 여기저기 찾아보다가 설명이 제각각이라, 

이해하는데 어려움이 있어, 나름 터득한 방법으로 풀어내고자 하니,

어차피 동일한 방법이지만 한번 살펴보시기 바랍니다.

 

엑셀 활용에 필수라 볼 수 있는  VLOOKUP함수에 대해 살펴보도록 하겠습니다.

 

 

Sheet1에 위와 같은 테이블이 있다고 가정합니다.  주어진 것은 과일 코드만 있습니다. 

동일한 엑셀 파일에 Sheet2와 Sheet3에는 과일에 대해 정의된 자료들이 있습니다.

 

 

Sheet2 시트에는 과일 코드에 대한 과일명이 정의되어 있습니다. 

여기서 순서는 과일 코드와 과일명순으로 잡았습니다.

 

 

 

Sheet3에도 Sheet2와 동일하게 과일명과 과일 코드를 정의하는 데이터들을 넣었습니다.

앞의 Sheet2와 차이가 있다면 순서를 과일명, 과일코드 순으로 Sheet2와

반대의 순서로 배치하였습니다.

가져오려는 데이터가 놓인 순서에 따라 Sheet2와 Sheet3에서 VLOOKUP을 했을 때 차이점을 보기 위해서입니다.

 

 

 

 

Sheet1에서 과일 코드를 가지고 과일명을 Sheet2와 Sheet3에서 각각 가져올 것입니다.

B열에서는 Sheet2에서 가져오는 과일명을, C열에는 Sheet3에서 가져오는 과일명을 넣어 주려고 합니다.

 

과일명을 가져오기 위해서는 Sheet1에 있는 과일 코드를 가지고 찾아와야 합니다. 

그러기 때문에 VLOOUP함수에게 과일 코드가 어디 부분에 위치하고 있는지 먼저 알려줘야 합니다.

 

VLOOKUP(과일 코드 위치, 과일 코드와 과일명이 있는 데이터 위치, 과일명 위치, 완전 일치)

 

먼저 과일명을 Sheet2에서 가져올 것이기 때문에 B2열 빈란에다가=VLOOKUP(  을 입력합니다.

Sheet1에서 과일 코드는 A2:A10까지이므로 마우스로 A2~A10까지 드래그를 합니다.

그러면 =VLOOKUP(A2:A10으로 지정이 되며, 다음 과일명 정보를 가져오기 위해 , (쉼표)로 구분해줍니다.

 

 

Sheet2로 이동하여 과일명을 가져오기 위한 데이터 영역을 마우스로 드래그를 해줍니다. 

여기서 과일명을 가져올 것이라고 과일명만 드래그를 하게 되면 앞서 Sheet1에서는 과일 코드를 가지고 

과일명을 찾을 수 없기에, 과일 코드와 과일명을 모두 지정을 해주어야 과일 코드를 가지고 과일명을 찾아 올 수가 있습니다.

 

=VLOOKUP(A2:A10,Sheet2!A2:B10, 

 

으로 설정이 됩니다,  여기서 다시 Sheet1로 이동하지 마시고 Sheet2에서 다음 옵션을 마저 지정을 해줍니다.

우리가 필요한 것은 과일명이기 때문에 Sheet2에서 과일명이 어디에 위치하고 있는지

VLOOKUP함수에게 알려주어야 합니다.

1열 기준으로 1, 2열 기준으로 2 잡으면 되기에 숫자 2로 입력을 해줍니다.

여기서 2가 아닌 1을 지정하게 되면 코드를 가져오게 됩니다.

 

=VLOOKUP(A2:A10,Sheet1!A2:B10,2,

 

다음으로 주는 옵션은 부분 일치냐 완전 일치냐의 차이인데, 대부분은 완전 일치하는 값으로 해당되는 데이터를 가져오려고 VLOOKUP을 사용하는 것이기 때문에 기본적으로 0 (완전 일치)으로 입력한다고 생각하시면 됩니다.

 

=VLOOKUP(A2:A10,Sheet2!A2:B10,2,0)

 

이렇게 입력하고 엔터를 치고 난 후 Sheet1을 살펴봅니다.

 

과일명(Sheet2 기준)을 보시면 과일명을 제대로 가져온 것도 있고 #N/A로 표기가 된 것도 

있습니다. 

이것은 Sheet2에서 데이터를 지정할 때 고정값으로 지정하지 않았기 때문에,

VLOOUP으로 가져오는 코드에 따라 범위도 자동 위치가 늘어났기 때문입니다.

 

따라서 Sheet2에서 데이터를 지정할때 고정으로 지정을 해줍니다. 

Sheet2!$A$2:$B$10

 

 

고정으로 변경 후 다시 보면 #N/A라고 표기된 곳도 정상적으로 과일명이 나오는 것을 볼 수 있습니다.

 

 

다음으로 C열의 과일명(Sheet3 기준) 쪽도 동일하게 VLOOKUP을 해보겠습니다.

여기서 Sheet2와 Sheet3는 과일명과 과일 코드의 위치만 변경되었기 때문에,  과일명을 가져오는 위치는 아래와 같이

변경해주어야 합니다.

 

=VLOOKUP(A2:A10,Sheet3!$A$2:$B$10,1,0)

C열에 VLOOKUP을 적용해보면 이번에는 모두 #N/A가 나온 것을 볼 수 있습니다. 

$ 고정을 해주었음에도 #N/A가 나온 것은 코드값의 위치 때문에 그렇습니다. 

VLOOKUP을 사용할 때는 정보를 참조하는 곳에서는 항상 첫 번째가 찾아서 가져오려는 코드가

위치해 있어야 합니다. 

즉 과일 코드, 과일명 순으로 돼야지 과일 코드가 다른 정보 뒤에 있게 되면

위에 처럼 #N/A로 표기되어 정보를 찾을 수가 없게 됩니다. 

 

따라서 VLOOKUP 할 때 참조할 데이터는 매칭 하려는 값을 꼭 첫 번째에 두도록 하는 것을 기억해야 합니다.